I am using the “Add single row” (add_single_row) step within Pipedream to create a row on Google Sheets. The step populates available fields because I have set header check to “Yes”
I have intentionally set certain fields to empty because the value for those fields/columns is computed by Google Sheets. However, sending data from Pipedream to the sheet results in that field being filled with the next field’s value. This feels like a bug.
Reading this page, I do see Provide a value for each cell of the row. Google Sheets accepts strings, numbers and boolean values for each cell. To set a cell to an empty value, pass an empty string but I am not sure how to pass an empty string:
Shouldn’t the lack of a field value be treated as empty value/string?
For some additional clarity, I am referring to the row #3 in the sheet above. Row #2 has a #REF error because it’s a formula: =arrayformula(A2:A/86400+date(1970,1,1)+time(5,30,0))
It broke after the value for B3 was entered by Pipedream.
Hey @arunwp, good catch and sorry about that! We actually have the fix in progress now, because we noticed the same thing. Feel free to follow along with this issue if you’d like to follow along: https://github.com/PipedreamHQ/pipedream/issues/2143.
Once we publish the update to that action, you should see an option to update the step in your workflow to use the latest version.
Unable to parse range: Collected data!#ERROR!:#ERROR!
at Gaxios._request (/tmp/ee/node_modules/gaxios/build/src/gaxios.js:129:23)
at process.processTicksAndRejections (internal/process/task_queues.js:95:5)
at OAuth2Client.requestAsync (/tmp/ee/node_modules/google-auth-library/build/src/auth/oauth2client.js:368:18)
at Object.updateSpreadsheet (file:///tmp/ee/c_0RflKln/user/google_sheets/google_sheets.app.mjs:311:15)
at Object.updateRow (file:///tmp/ee/c_0RflKln/user/google_sheets/google_sheets.app.mjs:469:14)
at Function.all (null:null:null)
at Object.run (file:///tmp/ee/c_0RflKln/user/google_sheets/actions/upsert-row/upsert-row.mjs:181:9)
at global.executeComponent (/var/task/launch_worker.js:152:22)
at MessagePort.messageHandler (/var/task/launch_worker.js:618:35)
Any thoughts on why this might be happening? The key column is properly set to A:
Hm if you don’t mind, I’d like to take a closer look. Can you open the open the workflow Settings and check the box to “share the workflow with support”? Then if you don’t mind sharing the URL of the workflow here, I’ll be able to take a look and investigate further.
@danny, could you let me know if you or someone else from the team had a chance to look? I’d really like to move forward before the weekend comes around. Neither add_row nor upsert_row is being useful to me at the moment.
Hi @arunwp, I apologize for the frustration in getting those 2 actions working. I haven’t been able to reproduce the error you’re running into with the Upsert Row action, so we’re still looking into that one. Have you been able to modify the data you’re passing in the Insert array, in case there is an issue with the input format?
Thanks for getting back to me! Just so I understand correctly, what are the requirements for the input on upsert_row? Two primary questions come to mind:
Should I enter a value for all fields/columns for a row? I have intentionally set some the empty because I am just testing at the moment.
Should I not use blank values for certain fields?
This is how the input is constructed at the moment but I have about 20 fields on the sheet. For now, I am testing with 4 values:
@danny That is odd. It’s true that the account is missing on the step at the moment (not sure what caused that) but from the log I shared, we can see that the account actually existed fine at the time of workflow:
@arunwp I get that error too when my Sheet Name has spaces, but not when I replace the spaces with underscores. E.g., “Copy of Sheet 1” to “Copy_of_Sheet_1”. Changing your Sheet Name to “Collected_data” might work as a workaround.
Spaces not being treated properly seems like a bug though, but I am not sure if it’s a Pipedream component bug or Google Sheets API bug. cc @danny@dylburger
FYI @arunwp, Jacob fixed this bug in the Upsert Row action, so you should get prompted to update that step the next time you open the workflow, and you can then throw sheets that have spaces in their names in the mix without running into any issues : )
@arunwp FYI we just pushed out the fix for the bug you initially raised regarding passing empty strings in the Google Sheets Add to Row action – please make sure to update the action to the latest version and give it a shot.
