I have a workflow that is added data to a specific sheet in a google sheets document. It’s working perfectly. It fills in the first 5 columns based on data from previous node executions.
In the final step, I’d like to compare data from another sheet in the same document and add it to the first sheet. Basically, in Sheet1, there is an account_id column. There is also an account_id column in Sheet2.
For the final step, I’d like to lookup data in Sheet2 and add it to newly added row in Sheet1 using the account_id as the match. So, in order to populate (for example) columns 6-9 in row 1 in Sheet1, get the data from Sheet2 where account_id in row1 matches one of the rows in Sheet2 and fill in the matching data. For example, column 6 in Sheet1 might be email, but I need to get this from Sheet2 and not my previous nodes.
Working with and updating Google Sheets can be a bit tricky. But having an identifier column is the first step towards success
So let’s unpack this here. Seeing you haven’t shared your workflows or example data I will use the below example document for such a job and make some assumptions. You would of course need to adjust this to match your own document structure and replace any IDs as needed
You can already add data like so and I reckon you are doing something along these lines based on your description:
The workflow would produce the below result:
Updated logic
Now to lookup data from the “Additional data” sheet, all we need is two more Google Sheets nodes: One to perform the lookup (using the Read Rows operation and the Filters option), and one to update the “Main” sheet in this example. Here is how it could look in a workflow:
This workflow will now look up the additional details from the second sheet (an email in this example) and update the first one:
Thanks…this makes basic sense, but I’m not sure which sheet is which in your flow. In the one screenshot, you have 3 sheets nodes. The first one is the output of my flow and has several columns already filled and some that need to be filled by the next steps.
Which sheet comes next? Is it my sheet with the info that needs to be filled on the first sheet? If so, what function and “filter” am I using?
For the last step, I’m assuming I’m updating my first sheet, but I’m not sure what function and filters. What I’ve tried so far keeps adding rows to my sheet versus updating existing rows.
Actually, now it’s not working anymore. I changed around some columns in my sheet and thought I hooked it all together again correctly, but something isn’t right. Can you clarify what’s happening in each step in your first explanation?
I think I see the issue here…if I have more than one of the same value for the matching column, then it only updates the first instance. In those cases, it’s not updating the latter instances of the same value. For example, on my sheet that is your “Main”, the same account_id can be in multiple rows. On my “additional data” sheet, I have one line per account_id and values like email in difference columns.
When I run my workflow, when it appears happening is that the workflow updates the first row where it sees that account_id. So when it’s already populated, it looks like nothing happened. I tested this by deleting just some of the extra values for the first instance of an account_id and then my workflow adds another row with that account_id, but what the final step does is fill back in the data I deleted.
So, is there a way for this to selectively update the row that was previously added in the workflow OR update all instances of account_id in the sheet if there is no value for some of the other columns?
So, is there a way for this to selectively update the row that was previously added in the workflow
I am not sure I understand this requirement. The example workflow I have shared would only perform the lookup for newly added rows, so other rows should not be affected.
OR update all instances of account_id in the sheet if there is no value for some of the other columns?
Again, I am not sure I fully understand what you’re building, but it sounds like you can just add an IF node in your workflow after the “Find matching additional data” node. This node can then check whether the expected columns are empty, so your workflow can continue on either the true or false branch depending on the result.
On a more general note, if you’re not dead set on Google Sheets you could also consider using a proper SQL database if this is a technology you are familiar with. This will usually make working with complex datasets considerably easier as it allows you to query data across multiple tables if needed
