Please Help: Flow to add Google Sheet rows to Sharepoint List

[rmclass]

Hi everyone,

I'm trying to build a Flow that sends rows from a Google sheet to a Sharepoint list. Each time a new row is added in the Google Sheet, I'm looking to have that row added to the Sharepoint list as well.

I've attached an image of my Flow here.

The above Flow works but each time the Flow runs, it copies every row in the Google Sheet to the Sharepoint list again instead of just the new Google Sheet row that's been created.

I would like for just the new Google Sheet row to be added to Sharepoint. Could you please help me understand how I can fix this? 🙂

Thanks so much.

[Rhiassuring]

Hi there, 

This is because you are using "Get Rows" - so it is returning every row.  You have to use that action though since there is no trigger for "When a new row is added to Google Sheets", so I see your pain.

You will want to filter your returned "Get Rows" to only the new ones. So, we will filter the array for anything added in the last day - since the last time it ran. But, it doesn't have dates by default in the rows.

Do users add dates into the sheet? Would you be permitted to add a column so we can track the run times?  

[rmclass]

Hi @Rhiassuring

Oh okay, sure let's do that. 🙂

Yes, I can add a column for dates in the Google Sheet. I have set up a column "Date Today" which captures the date the row was created in mm/dd/yyyy format. Does this work for what you have in mind?

What would we need to do next? 🙂

Thanks a lot for your help by the way.

[Rhiassuring · Accepted solution]

Yes! Definitely. You could also just use "In SharePoint" Y/N, or even a "Link to SP Item", if you wanted to.

So you will Get Rows from your Google Sheet, same as before.

Then, use a Filter Array to return only those items where Date Today is Empty. 

Then, use a Parse JSON on the Output from the Filter Array so you can get the individual columns. (Run the flow once on its own up to the Filter Array, and use the Output to Generate Schema in the Parse JSON action.)

For each of those, you will iterate through and use the Create Item action in SharePoint. The difference now is that with each iteration, you'll also want to Update Row in Google Sheets to add the convertTimeZone(utcNow(),'UTC','Your Time Zone'),'M/d/yyyy') to the "Date Today" column in the current row you're using. 

That would, all in all, look like this:

Example Google Sheet before it runs:

The flow:

Proof that it worked...

Populated SharePoint list

Populated Google Sheet

[rmclass]

Thanks so much @Rhiassuring! 🙂

I've built that flow out using your flow as guidance but seem to be getting an error at the "Parse JSON" stage - 

Action 'Parse_JSON' failed

First, I created the flow up to "filter array", ran the flow and copied the "raw output".

Then I created "Parse JSON", entered "Body" in the Content field and for the Schema section, clicked "Generate from Sample". In here, I pasted the raw output I copied from "Filter Array" flow run. Am I making a mistake somewhere with this?

Also, it seems like 2 additional "apply to each" controls automatically popped up when I was entering data into "Create Item" and "Update row" - "Apply to each 2" and "Apply to each 3". Will this affect anything? If so, how would we remove them without affecting the rest of the flow? 🙂

Here's the full flow:

Thanks again! 🙂

[Rhiassuring]

What is the error you're getting on your Parse JSON? It looks like you're doing it right to me, but, hard to tell. 

For your Apply To Each - as soon as you have created your filter array, and then  your Parse JSON, you will only use your Parse JSON going forward. You won't reference the Google Sheet again, it all exists in  your Parse JSON ... once it works 🙂 

[rmclass]

Hey @Rhiassuring 

Here's the error:

It said "ValidationFailed. The schema validation failed."

For the "apply to each" bit, do you mean we select the purple Parse JSON operators on the bottom right that pertain to each field instead of the Google Sheet ones I've currently selected below? 🙂

It looks like those 2 extra "Apply to Each" controls come up automatically when I try to make selections in the fields, starting with the *Title field. 🙂

[Rhiassuring]

For the second part: Yes, only the Parse JSON outputs should be used in your creation - not the sheet.

For your Parse JSON issue, that usually happens when you've mis-pasted content or grabbed something that isn't typical of the output. Did you grab the whooooooole output? Should start with a [{

[rmclass]

Thanks sooooo much @Rhiassuring I just ran the flow again and that worked. It was mis-pasted content. 🙂

The flow runs perfectly, I really appreciate your help!! 🙂

[ina295]

hi i couldnt find filter array "date value" how to find it ? 

[DDB]

Hello Rmclass,

I am trying to create a flow to add Google Sheet rows to Sharepoint List. What app did you use to create the flow?

[Fcorrea]

I have the Filter Array Output generated Parse JSON schema, but when I run a test retreive this error:

"InvalidTemplate

Unable to process template language expressions in action 'Parse_JSON' inputs at line '0' and column '0': 'The template language expression 'outputs('Filter_array')['body/value']' cannot be evaluated because property 'body/value' doesn't exist, available properties are 'body'. Please see https://aka.ms/logicexpressions for usage details.'."

I have this before runing the test:

then it gives me the error. And after the error is like this: