Unanswered

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

(1) Share

Report

Posted on by 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.

Categories:

Building flows

I have the same question (0)

All responses (12)

Answers (1)

Rhiassuring 8,690 Moderator on at

Like (1)

Report

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?

Was this reply helpful? Yes No
rmclass 18 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

Rhiassuring 8,690 Moderator on at

Like (3)

Report

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

Was this reply helpful? Yes No
rmclass 18 on at

Like (0)

Report

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! 🙂

Was this reply helpful? Yes No
Rhiassuring 8,690 Moderator on at

Like (1)

Report

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 🙂

Was this reply helpful? Yes No
rmclass 18 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Rhiassuring 8,690 Moderator on at

Like (1)

Report

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 [{

Was this reply helpful? Yes No
rmclass 18 on at

Like (1)

Report

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!! 🙂

Was this reply helpful? Yes No
ina295 32 on at

Like (0)

Report

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

Was this reply helpful? Yes No
DDB 2 on at

Like (0)

Report

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?

Was this reply helpful? Yes No