cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bebo987
Helper I
Helper I

Deleting a row in Google sheets if deleted in sharepoint list

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: 

 

bebo987_0-1651096210309.png

 

bebo987_1-1651096230081.png

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). 

 

bebo987_2-1651096278410.png

 

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

 

thank you for your help

1 ACCEPTED SOLUTION

Accepted Solutions
bebo987
Helper I
Helper I

did it myself by using that field instead of ID

View solution in original post

10 REPLIES 10
Rhiassuring
Community Champion
Community Champion

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.

 

Rhiassuring_0-1651131854134.png

 

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

 

 

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:
 
bebo987_0-1651136010440.png

 

 
Rhiassuring
Community Champion
Community Champion

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
Helper I
Helper I

Hi @Rhiassuring 

 

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

 

if I use the highlighted value:

 

bebo987_0-1651224955385.png

 

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

 

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
Community Champion
Community Champion

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
Helper I
Helper I

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
Community Champion
Community Champion

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.


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:

bebo987_0-1651250154270.pngbebo987_1-1651250176523.png

and automatically creates:

 

bebo987_2-1651250224739.png

 

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
Helper I
Helper I

did it myself by using that field instead of ID

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (4,271)