Websites built on Squarespace have the option of storing forms in Google Sheets. Each time a client fills in a form, a row is added to a Google Sheet with all of the form responses in different columns.
I need to find a way to copy that data from a Google Sheet to an Excel File in Sharepoint.
Squarespace forms trigger emails, so the trigger event for Flow is easy enough - it's the 'copy and paste' from the Google Sheet to the Excel Sheet that is the problem.
Do you want to copy data from Google Sheet to an Excel file in SharePoint?
Please check the following flow for a reference.
For testing, it is triggered by Button.
Then Get rows from Google sheets.
Add an Excel online action Add a row into a table to insert items from Google sheet to Excel. To use Excel files saved on SharePoint library, please make sure select Location to your SharePoint site.
Image for your reference.
I tried that yesterday and it didn't work. And then I tried it again today and it did!! - all of the fields in the Google Sheet became options to fill the Excel rows. (maybe it was a propagation thing that caused the delay?)
But...it copies the entire sheet, every time there is an email.
Do you know if there is a way to copy only the last row in the Google Sheet with data each time an email is received?
Thanks for updating.
To get the last row from Google sheet, please use the last function.
Under the action Get rows, add the SharePoint action Create item.
Enter code likes below for each field:
Please make sure you do use the correct column name from the Google sheet.
I really do appreciate your help with this! 🙂
So I tried to run that with just one of the columns from the Google Sheet - the first one - "Submitted On" is the title on the Google Sheet. I think I'm missing something in the syntax of these expressions. This is the error message I got back.
InvalidTemplate. Unable to process template language expressions in action 'Create_item' inputs at line '1' and column '2521': 'The template language expression 'last(body('Get_rows')?['value']?['Submitted_On'])' cannot be evaluated because property 'Submitted_On' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'.