Heya!
Is it possible to do the insert or update workflow with google sheet? It would need to either insert a new line or update an existing one if the ID of the line matches, I know it's feasible with DBs but I am struggling to replicate in GSheet.
Hello @mcrisb!
Could you provide a little more information on the events you want and their order?
You can connect both a retool app and a workflow to a google sheets resource to insert or update (using the 'bulk upsert' Action) to either match with an existing ID or create a new one.
If you are using a workflow what would you want the trigger to be?
Here are some docs on working with google sheets, you should be able to replicate all the action in a workflow!
Hi Jack! Thanks for the response, apologies for not being super clear.
Basically, the workflow should run every week and it should transfer data from a SQL call to a Google sheet, which is theoretically OK but I need the workflow to differentiate between lines that are already in the sheet versus lines that need to be appended, and there should be only one unique sheet per entry.
For example every line has a unique_id and stores inventory data, every week the workflow should update the lines where the unique_id matches the one in the SQL query with the new inventory values, which can be done through the bulk update call, the workflow should then move on and append new inventory lines if the unique_id is not found in the google sheet.
I know how this would work in a database but I am not sure how to replicate it with google sheet at the end point and unfortunately I have to store the data there for this purpose. I have set up the bulk update section of the workflow and that is performing all OK but I am unsure how to manage the "insert if not in the sheet already" section hopefully this sheds more light!
Hello, thank you for the detailed explanation!
To accomplish the functionality you want (update if ID exists or create new row) we have a built in option called 'Bulk Upsert' for other resources, unfortunately it seems to not yet be added to GSheet query options which is odd....
The work around to this would be involving two queries, first fetching all the current IDs from the GSheet spreadsheet, putting them into an array, to check against each SQL row's ID for inclusion, in a loop block.
Then split up the row data into two arrays, one to pass to the update block and one to pass to the insert block.
Something along the lines of
if (ArrayOfIds.inlcudes(item.id)) {
updateRows.push(item)
} else {
appendRows.push(item
}