Unanswered

Add new row in Excel from Google Sheets when doesn't exist already

(0) Share

Report

Posted on by DL-23070615-0

I'm trying to achieve something like this, sorry I'm still a newbie here...

1) My Google Sheet stores responses from a form.
2) Every hour my flow "gets rows" from that Google Sheet
3) Then, I list rows present in an Excel table, and add rows for each cell etc.

The problem is, I don't know how to filter rows from Google Sheets before they are copied into Excel.
I only want to copy new rows that do not exist in the Excel file already.

I'm trying to use the filter function, something like: If "email" or "ID" in Google sheets is equal to email/ID in Excel, then skip it. If not equal, then add new row in Excel.
But I always end up with all rows being copied multiple times. into the excel table.
Any suggestions?

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (0)

v-LilyW-msft on at

Like (1)

Report

Hi @Dan_176

I set up a flow, you can refer to below screenshots to see if it helps.

This is my test Google sheet

This is my excel:

After flow runs, the new row will be added to excel like below:

Hope the content above may help you.

Best Regards

If my reply helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
DL-23070615-0 on at

Like (0)

Report

Thanks for your response. I've been trying to test your solution, but I keep getting an error related to Google API, so I'm stuck at step 1.
The error reads like:
---
Flow save failed with code 'DynamicOperationRequestClientFailure' and message 'The dynamic operation request to API 'googlesheet' operation 'GetTable' failed with status code 'BadRequest'. This may indicate invalid input parameters. Error response: { "status": 400, "message": "Range (responses!P2:P) exceeds grid limits. Max rows: 16, max columns: 15\r\nclientRequestId: xxxxxx", "error": { "message": "Range (responses!P2:P) exceeds grid limits. Max rows: 16, max columns: 15" }, "source": "googledrive-we.azconn-we-01.p.azurewebsites.net" }'.
---
I thought the original file could be too large, so I've reduced it to 10 rows, 10 columns, still same error. So, though I'm pretty sure your solution works, there's no way I can test on my account

Was this reply helpful? Yes No
lizbiz520 26 on at

Like (0)

Report

This solution really helped however, I want to also update existing rows in the copied spreadsheet if any changes were made in the data source spreadsheet - I want to ensure any changes made to the original data is also reflected in the second spreadsheet but cannot figure out how to set the above flow up and also account for changes and update accordingly. Any help is appreciated. I've also posted my own post but am still awaiting any replies....

Was this reply helpful? Yes No
gbeard 5 on at

Like (0)

Report

@v-LilyW-msft,
Thank you! This helped me build something very similar with ESRI's Get Data from Feature Layer to populate a Google sheet. For some reason it is filtering some of my data in the loop, but I think that is unrelated.

Was this reply helpful? Yes No
gbeard 5 on at

Like (0)

Report

I had a format on my date time field. Some of my dates were NULL so those records were being removed.

New issue in: my flow says it is complete even though it didn't add all the records (made sure to leave off filters this time). I can rerun the flow and it will add new records, even when the data hasn't changed. I tested with Excel in SharePoint and it worked as intended.

Was this reply helpful? Yes No
EmillyMota 2 on at

Like (0)

Report

Você conseguiu? Estou com esso erro também

Was this reply helpful? Yes No