Deleting a row in Google sheets if deleted in sharepoint list

[bebo987]

Hi community, 

I have tried to build a flow that deletes rows from google sheets after an item is deleted in sharepoint. Some details more. 

The rows in the gsheet file has the same columns and values of the sharepoint list. The item ID of sharepoint is under the ID column in google sheet: 

so if a row is deleted in sharepoint the correspondent row, with the same id should be deleted in ghseet. I have tried to set this up (the delete record in the "yes" situation is missing). 

This way is not really working, can't really understand what's missing. 

thank you for your help

[Rhiassuring]

Hi there, 

So as it turns out, Google Sheets doesn't care about your ID. It only cares about the ID it adds in  itself, under the title "__PowerAppsId__" - it automatically generates this.

We need to go through a few steps to do this.

⭐Step 1 - Trigger on item deletion - you've already got this! This gives us our ID. In my example, I'm using an "Input" called SPID. So that'll be the same for you as your ID in your Google Sheet.

⭐Step 2 - Use the Get Rows from Google Sheets action. You've already got this too.

⭐Step 3 - Use the "Filter Array" action to look at the "Get Rows" action and say, "Only show me when the Column called SPID contains a value that matches my SPID". (For  you, this is your Google Sheet ID and your SharePoint deleted item ID.)

⭐Step 4 - Save &run the flow, then copy the output from your Filter Array. Come back into your flow and add a Parse JSON action. Point it at your Filter Array, and then use the Generate from Schema button, to paste in your content. 

⭐Step 5 - Use the "Delete Row" for Google Sheets and put the "__PowerAppsId__" content output from your Parse JSON into that field. It will turn into an Apply to Each - that's okay, it'll only have one record anyway.

To make this cleaner, we could use a Compose and first() but ... it is very late for me and I am going to bed 🙂 I hope this helps anyway!

Rhia

[bebo987]

Hi Rhia,

about step 4, these are the otput that i get from the filter array after running:

input:

  {

    "@odata.etag": "",

    "ItemInternalId": "eb2ce8ad18a64ab3a9e7b88efc883c01",

    "ID": "113",

    "N_x0020_pratica": "test",

    "1o_x0020_Beneficiario": "test",

    "Cliente": "test",

    "Data_x0020_Conferma_x0020_Esp": "2022-10-10",

    "Descrizione": "test",

    "E_mail_x0020_OP": "test",

    "Inizio": "2022-11-10",

    "Operatore": "test",

    "Pax": "1",

    "Programma": "test",

    "Sotto": "test",

    "Stato_x0020_Pra": "test",

    "Totale_x0020_OP": "100",

    "Lingua": "",

    "Descriz_x0020_aggiuntiva": "test",

    "Escursione": "",

    "Località_x0020_di_x0020_pa": "",

    "Località_x0020_di_x0020_ar": "",

    "Accettazione_x0020_operatore": "True",

    "Note": "",

    "__PowerAppsId__": "eb2ce8ad18a64ab3a9e7b88efc883c01,2"

  }

]

output:

Body: 

[]

this is the process so far:

[Rhiassuring]

Oh, I see - I believe it is because your IDs aren't the same format (such as both numbers, or both strings.) 

I'm guessing that Google Sheets is text, and SHarePoint ID is number. To alleviate that issue, try this: 

Click in your Filter Array where you Google Sheets ID is. Backspace it. Click on the "Expression" tab that you will see to your right. Type in int() - click between the two brackets, and then find your Google Sheet "ID" column from the dynamic content tab. Click it. Then, click update. Save, re-run. Let me know how it goes!

R

[bebo987]

Hi @Rhiassuring 

so that helped a lot, and it worked; however the problem now it's here:

if I use the highlighted value:

it deletes ALL rows in google sheets (:D) which is not really intended. By replacing that with "ID" field coming from the JSON parse it fails. 

If we fix this, should be all. 

thank you for your help

Alberto

[bebo987]

Ok I solved that part,

the problem now is that we parsed a JSON with only 1 element in the sharepoint list, therefore it works with 1 record only. When the list gets fully populated the process fails. 

how can we fix that part?

[Rhiassuring]

There should only be one item in a SharePoint list with the ID specified, since it's unique ... are you using the ID in a different way?

[bebo987]

no not really, and I have the same problem by using

_PowerAppsId_ or ItemInterlID (from PARS JSON)

Take into consideration that you have an "ID" column populated after a new item is added in sharepoint and then it automatically creates a column "__PowerAppsId__"  as you probably know.

BTW i am working on a similar flow that updates the gsheet record after the same record is updated in sharepoint: same story, it randomly updates the whole dataset with the same criteria regardless of the row ID. 

[Rhiassuring]

I'm sorry, I'm not sure I'm following - so the PowerAppsId is the Google Row ID and is unique.

The "ID" that you've created in your GSheet, that holds the SharePoint ID, is what I'm referring to in the condition.

The other ID is from SharePoint itself, which is always, always unique in the SharePoint list.

I guess I'm not understanding where in this process you'd ever return more than a single record.

[bebo987]

HI @Rhiassuring 

basically it's this way: when the new record is created a new ID, under the column "ID" which is the same number in sharepoint  as shown:

and automatically creates:

So when we filter ID=ID we refer to the column but after we address the delete action to _PowerAppsId_ or ItemInterlID.

Hope it clarifies

[bebo987 · Accepted solution]

did it myself by using that field instead of ID