The Wayback Machine - https://web.archive.org/web/20220722184947/https://github.com/PipedreamHQ/pipedream/discussions/972
Skip to content

Update Row in Google Sheets #972

Answered by dylburger
osseonews asked this question in Q&A
Update Row in Google Sheets #972
@osseonews osseonews
Feb 24, 2021 · 3 answers · 3 replies
Answered by dylburger Return to top

osseonews
Feb 24, 2021

Hi there,
We are trying to use Google Sheets to update a row in a specific sheet, based on the value we get in another step. Specifically, we are storing SKU and QTY in a Google Sheet to track inventory. A previous step in Pipedream gets us the SKU and the QTY values. We then want to:

  1. Search the Google Sheet for the row that equals SKU value. For example, search the Google sheet SKU column for the row where the SKU value equals ABC
  2. Update the QTY column in the SKU value row to the QTY value returned by the previous step.

So for example, if our step says, SKU: ABC now has QTY: 10, we want to search the Google Sheet for SKU with ABC and then update the QTY column in that row to 10.

I thought this would be easy, but we can't figure it out. Any help would be appreciated.

THanks.
Answered by dylburger Feb 24, 2021

@osseonews I created an example spreadsheet and example workflow that shows you how this works. All of the data / params should be public, and I added comments in the code of the workflow that should help you follow.

  • The Find Rows action accepts a spreadsheet / sheet name, as well as a column + value pair to look for. In my case, column A contains the SKU, and I'm looking for the row with the value "ABC". This action returns an array of row numbers where we found the value you were looking for in your target column. If you had multiple records with the value "ABC" in Column A, the action would return an array of all of those. In your case I assumed each SKU was unique, so you should see …
View full answer

Replies

3 suggested answers
·
3 replies
Oldest Newest Top

dylburger
Feb 24, 2021
Maintainer

@osseonews I created an example spreadsheet and example workflow that shows you how this works. All of the data / params should be public, and I added comments in the code of the workflow that should help you follow.

  • The Find Rows action accepts a spreadsheet / sheet name, as well as a column + value pair to look for. In my case, column A contains the SKU, and I'm looking for the row with the value "ABC". This action returns an array of row numbers where we found the value you were looking for in your target column. If you had multiple records with the value "ABC" in Column A, the action would return an array of all of those. In your case I assumed each SKU was unique, so you should see an array of 1 row number returned by this step.
  • The cell_update_data does two things. 1) it formats the cell number you'd like to update in Google's A1 notation. In my example spreadsheet, I want to update the QTY cell corresponding to the ABC SKU: cell B2. The Find Rows step returns 0-based row numbers, Google Sheets uses 1-based row numbers, so I did the conversion here (see the cellToUpdate step export). 2) It formats the data Google expects for updating cells. Since you can update multiple rows and columns of data with this action, Google expects an array of arrays of update data. In our case we just want to update one cell, so the update data look like this: [ [ data_to_update ] ]. You'll need to fill in the variable that corresponds to your QTY here.
  • I pass these values to the Update Spreadsheet action, and cell B2 updates with my sample QTY:

2021-02-24 13 26 54

I think there are a few ways we can optimize this specific use case, but let me know if that makes sense / helps.
0 replies
Answer selected by dylburger

osseonews
Feb 24, 2021
Author

Thank you! Will test later and let you know. Certainly was more complicated than I thought. Quick question, why do you add a +1 in ${steps.google_sheets_find_rows.$return_value[0] + 1 in the cell_update_data - I'm not clear what that does even reading your explanation. Thanks.
3 replies
@dylburger

dylburger Feb 24, 2021
Maintainer

Are you generally familiar with 0-based indexing? In most programming languages, when you're dealing with lists, the first element is technically at index 0, the second element is at index 1, and so on.

So when we return rows from the "Find Rows" action, we return 0-based indexes. In my example spreadsheet, we return a value of "1" for the "ABC" row, but that actually corresponds to row 2 in the Google Spreadsheet, since Google starts their row numbers at 1 (in other words, they use 1-based indexing). So we have to add 1 to the value returned from the Find Rows action to convert the 0-based index to a 1-based index.
@osseonews

osseonews Feb 24, 2021
Author

Yeah, sure, OK I got it now. I didn't understand that google is starting at 1. Anyway, we tried your workflow and it works fine. Just out of curiosity, in the steps.cell_update_data.updateData, what is the structured mode for value? If we turn it on, we get an error, have to turn it off for the workflow to work, but, the default is on. Can you clarify what that is exactly? Thanks!
@dylburger

dylburger Feb 24, 2021
Maintainer

Take a look at the docs on structured params and let me know if that helps.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Category
Q&A
Labels
None yet
2 participants
@osseonews @dylburger