Google sheets API batchGet and batchUpdate

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.

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.

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

Please share the workflow

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        -660,
        -60
      ]
    },
    {
      "parameters": {
        "authentication": "oAuth2",
        "url": "=https://sheets.googleapis.com/v4/spreadsheets/{{$json[\"spreadsheet id\"]}}/values:batchGet?ranges={{$json[\"administrator\"]}}&ranges={{$json[\"assumptions\"]}}&ranges={{$json[\"bilanturi\"]}}&ranges={{$json[\"borrower\"]}}&ranges={{$json[\"coborrower\"]}}&ranges={{$json[\"companie\"]}}&ranges={{$json[\"companie\"]}}&ranges={{$json[\"contacts\"]}}&ranges={{$json[\"income\"]}}&ranges={{$json[\"liabilities\"]}}&ranges={{$json[\"mortgage\"]}}&ranges={{$json[\"product\"]}}&ranges={{$json[\"property\"]}}&ranges={{$json[\"result\"]}}",
        "options": {}
      },
      "name": "Get batch google sheet",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        0,
        -60
      ],
      "credentials": {
        "oAuth2Api": {
          "id": "16",
          "name": "Google sheets n8n"
        }
      }
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "result",
              "value": "result!A%3AAQ"
            },
            {
              "name": "product",
              "value": "product!A%3AAQ"
            },
            {
              "name": "contacts",
              "value": "contacts!A%3AE"
            },
            {
              "name": "assumptions",
              "value": "assumptions!A%3AF"
            },
            {
              "name": "property",
              "value": "property!A%3AAD"
            },
            {
              "name": "mortgage",
              "value": "mortgage!A%3AE"
            },
            {
              "name": "borrower",
              "value": "borrower!A%3AK"
            },
            {
              "name": "coborrower",
              "value": "coborrower!A%3AL"
            },
            {
              "name": "income",
              "value": "income!A%3AI"
            },
            {
              "name": "liabilities",
              "value": "liabilities!A%3AI"
            },
            {
              "name": "companie",
              "value": "companie!A%3AP"
            },
            {
              "name": "bilanturi",
              "value": "bilanturi!A%3AAC"
            },
            {
              "name": "administrator",
              "value": "administrator!A%3AI"
            },
            {
              "name": "NOTE",
              "value": "sheetname!{startColumn}%3A{endColumn}"
            }
          ]
        },
        "options": {}
      },
      "name": "Set sheet ranges",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        -440,
        20
      ]
    },
    {
      "parameters": {
        "values": {
          "string": [
            {
              "name": "spreadsheet id",
              "value": "1FFOV4M4fwmCaxUcYqtFCa0q0fSK1blUDpIgDXBxA4bw"
            }
          ]
        },
        "options": {}
      },
      "name": "Set spreadsheet id",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        -440,
        -140
      ]
    },
    {
      "parameters": {
        "mode": "mergeByIndex"
      },
      "name": "Merge",
      "type": "n8n-nodes-base.merge",
      "typeVersion": 1,
      "position": [
        -220,
        -60
      ]
    },
    {
      "parameters": {
        "functionCode": "// Code here will run only once, no matter how many input items there are.\n// More info and help: https://docs.n8n.io/nodes/n8n-nodes-base.function\n\n// Loop over inputs and add a new field called 'myNewField' to the JSON of each one\nlet simplifiedValueRanges = [];\n\nfor (item of items) {\n for (object of item.json.valueRanges) {\n let key = object.range.split(\"!\")[0];\n let valueRangeValuesObj = {};\n\n //\n\n let temporaryArray = [];\n let temporaryObject = {};\n\n for (var y = 1; y < object.values.length; y++) {\n temporaryObject = {};\n for (var i = 0; i < object.values[0].length; i++) {\n temporaryObject[object.values[0][i]] = object.values[y][i]\n }\n temporaryArray.push(temporaryObject);\n }\n\n //\n\n valueRangeValuesObj[object.range.split(\"!\")[0]] = temporaryArray;\n simplifiedValueRanges.push(valueRangeValuesObj);\n item.json.valueRanges = [];\n }\n item.json.valueRanges = simplifiedValueRanges\n}\n\n// You can write logs to the browser console\nconsole.log('Done!');\n\nreturn items;\n"
      },
      "name": "Convert data to array",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        220,
        -60
      ]
    },
    {
      "parameters": {
        "authentication": "oAuth2",
        "requestMethod": "POST",
        "url": "=https://sheets.googleapis.com/v4/spreadsheets/1FFOV4M4fwmCaxUcYqtFCa0q0fSK1blUDpIgDXBxA4bw/values:batchUpdate",
        "options": {},
        "bodyParametersUi": {
          "parameter": []
        },
        "headerParametersUi": {
          "parameter": []
        },
        "queryParametersUi": {
          "parameter": []
        }
      },
      "name": "Update batch google sheet1",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        880,
        -60
      ],
      "credentials": {
        "oAuth2Api": {
          "id": "16",
          "name": "Google sheets n8n"
        }
      }
    },
    {
      "parameters": {
        "functionCode": "// Code here will run only once, no matter how many input items there are.\n// More info and help: https://docs.n8n.io/nodes/n8n-nodes-base.function\n\n// Loop over inputs and add a new field called 'myNewField' to the JSON of each one\nlet simplifiedValueRanges = [];\n\nfor (item of items) {\n for (object of item.json.valueRanges) {\n let valueRangeValuesObj = {};\n valueRangeValuesObj.range = Object.keys(object)[0];\n\n if (object[Object.keys(object)[0]].length > 0) {\n let resultArray = [];\n let temporaryArray = [];\n if (resultArray.length < 1) {\n let mainKeys = Object.keys(object[Object.keys(object)[0]][0]);\n resultArray.push(mainKeys);\n }\n for (let j = 0; j < object[Object.keys(object)[0]].length; j++) {\n let parent = object[Object.keys(object)[0]][j];\n for (let p = 0; p < Object.keys(parent).length; p++) {\n temporaryArray.push(parent[resultArray[0][p]] ?? \"\");\n }\n resultArray.push(temporaryArray);\n temporaryArray = [];\n }\n\n //\n valueRangeValuesObj.values = resultArray;\n\n simplifiedValueRanges.push(valueRangeValuesObj);\n item.json.valueRanges = [];\n }\n }\n delete item.json.valueRanges;\n delete item.json.spreadsheetId;\n \n item.json.valueInputOption= \"USER_ENTERED\";\n item.json.data= simplifiedValueRanges;\n}\n\n// You can write logs to the browser console\nconsole.log('Done!');\n\nreturn items;\n"
      },
      "name": "Convert data from array",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        460,
        -60
      ]
    },
    {
      "parameters": {
        "options": {
          "dotNotation": false
        }
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        680,
        -60
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Set sheet ranges",
            "type": "main",
            "index": 0
          },
          {
            "node": "Set spreadsheet id",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get batch google sheet": {
      "main": [
        [
          {
            "node": "Convert data to array",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set sheet ranges": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Set spreadsheet id": {
      "main": [
        [
          {
            "node": "Merge",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge": {
      "main": [
        [
          {
            "node": "Get batch google sheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Convert data to array": {
      "main": [
        [
          {
            "node": "Convert data from array",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Convert data from array": {
      "main": [
        [
          {
            "node": "Set",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set": {
      "main": [
        [
          {
            "node": "Update batch google sheet1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

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]:

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.

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.