Google sheets API batchGet and batchUpdate

Questions
1

Describe the issue/error/question

I have created a workflow for the following:

  1. Get all data from all sheets from a spreadsheet using HTTP Request connected to google API.
  2. Convert the data to an array in nice JSON format. (Google returns with keys as first object).
  3. Convert the data from an array back to Google format.
  4. Update the spreadsheet with the resulting data.

image
image1754×492 90.5 KB

What is the error message (if any)?

The data is received from batchGet is wrapped in an additional array. This is not the case when calling the same API from Google Developer test space.

image
image842×349 17.4 KB

With this here the batchUpdate does not work. (It works without it)

Please share the workflow

Share the output returned by the last node

Is this something that can be removed from the Convert data to array function right after we grab the data from the excel spreadsheet?

Information on your n8n setup

  • n8n version:
  • Database you’re using (default: SQLite):
  • Running n8n with the execution process [own(default), main]:
  • Running n8n via [Docker, npm, n8n.cloud, desktop app]:
2

Hey @markhanson,

Thanks for sharing the detailed information. Can you help me understand how are you sending the data from the last HTTP Request node? You’re making a POST request, but it seems like you’re not passing any data.

I created a sample workflow to verify how the data gets sent and below is the screenshot of the node. I’ve toggled the JSON/RAW Parameters option to true, and I am using the expression {{$json}} in the Body Parameters field. This sends only the object within the array.

Screenshot 2021-12-31 at 13.38.04
Screenshot 2021-12-31 at 13.38.041008×1462 100 KB

3

Hi @harshil1712

Thanks for the information about using the expression {{$json}}. This solved errors in not being able to update the spreadsheet. Note to add it is a function and not just paste it in the text box as that doesn’t work.

The Google sheets batchUpdate API only updates data. (Overwrites). It cannot be used to append data to the sheets. (Add new rows of data) The Append function only works on one Sheet within a Spreadsheet at a time.

To resolve this we made a function that gets the data from the sheet, then inserts the changes and new items, and then we call the batchUpdate API to overwrite the whole sheets.