Google Sheets Rows Update

Questions
1

Describe the problem/error/question

I’m trying to iterate through a Google Sheet and get the address, do a geolocation lookup and update two columns in that row with lat and lon. I can’t seem to get this going, due to errors.

What is the error message (if any)?

{
  "errorMessage": "Bad request - please check your parameters",
  "errorDescription": "Invalid data[0]: Unable to parse range: Sheet1!W",
  "errorDetails": {
    "rawErrorMessage": [
      "400 - {\"error\":{\"code\":400,\"message\":\"Invalid data[0]: Unable to parse range: Sheet1!W\",\"status\":\"INVALID_ARGUMENT\"}}"
    ],
    "httpCode": "400"
  },
  "n8nDetails": {
    "nodeName": "Google Sheets1",
    "nodeType": "n8n-nodes-base.googleSheets",
    "nodeVersion": 4.5,
    "resource": "sheet",
    "operation": "update",
    "time": "3/6/2025, 11:12:23 AM",
    "n8nVersion": "1.81.4 (Self Hosted)",
    "binaryDataMode": "default",
    "stackTrace": [
      "NodeApiError: Bad request - please check your parameters",
      "    at ExecuteContext.apiRequest (/usr/lib/node_modules/n8n/node_modules/n8n-nodes-base/nodes/Google/Sheet/v2/transport/index.ts:78:9)",
      "    at processTicksAndRejections (node:internal/process/task_queues:95:5)",
      "    at GoogleSheet.batchUpdate (/usr/lib/node_modules/n8n/node_modules/n8n-nodes-base/nodes/Google/Sheet/v2/helpers/GoogleSheet.ts:198:20)",
      "    at ExecuteContext.execute (/usr/lib/node_modules/n8n/node_modules/n8n-nodes-base/nodes/Google/Sheet/v2/actions/sheet/update.operation.ts:417:3)",
      "    at ExecuteContext.router (/usr/lib/node_modules/n8n/node_modules/n8n-nodes-base/nodes/Google/Sheet/v2/actions/router.ts:64:14)",
      "    at ExecuteContext.execute (/usr/lib/node_modules/n8n/node_modules/n8n-nodes-base/nodes/Google/Sheet/v2/GoogleSheetsV2.node.ts:30:10)",
      "    at WorkflowExecute.runNode (/usr/lib/node_modules/n8n/node_modules/n8n-core/src/execution-engine/workflow-execute.ts:1123:8)",
      "    at /usr/lib/node_modules/n8n/node_modules/n8n-core/src/execution-engine/workflow-execute.ts:1470:27",
      "    at /usr/lib/node_modules/n8n/node_modules/n8n-core/src/execution-engine/workflow-execute.ts:2029:11"
    ]
  }
}

Please share your workflow

Share the output returned by the last node

No output. Just the error. Not sure why it’s thinking Sheet1W

Information on your n8n setup

  • n8n version:
    1.81.4

  • Database (default: SQLite):
    No database involved.

  • n8n EXECUTIONS_PROCESS setting (default: own, main):
    No clue what that means.

  • Running n8n via (Docker, npm, n8n cloud, desktop app):
    Self-hosted, npm, no docker.

  • Operating system:
    Almalinux 9

2

Putting the answer under Detail widget to avoid confusion among future readers. I am sorry for taking the wrong direction.

Wrong response

~I assume you are trying to update a row by its number. Unfortunately, unless the sheet has a column named “row_number”, this won’t work.~

~You need to use a column that contains unique values and let the Sheet API to identify the row by specific unique value. Looking at your sample data, column hfpxzc href could be a good candidate for this (assuming it contains unique links to the venues). place_id or osm_id could serve this purpose more reliably but I guess those come from OSM API and not available on the Sheet.~

~Hope this helps. If it does please mark this post as Solution and give it a like.~

3

Why won’t row_number work? That’s the logic inside the nodes, to be able to look them up.

Anyway I tried to use a field address1 but the Merge node gets confused and can’t merge the two inputs for some reason.

This is the output of the Sheets Update node. I can’t seem to get the two sources into the Merge node to come together. Any ideas?

[
  {
    "gpslat": "43.6669123",
    "gpslon": "-79.345361",
    "address1": ""
  },
  {
    "gpslat": "",
    "gpslon": "",
    "address1": "807 Gerrard St E"
  }
]
4

You are right. My apologies for attempting to divert you.

5

I’ve migrated that Merge to a Code node. I want to grab from the inputs (Loop node and HTTP API Request node) and put them together into a nice little json item that I can use downstream.

Problem is that the Code node won’t see the API request data, which is seen as valid. Both inputs are valid.

I’m not sure why I can’t see both inputs. I’ve tested for both inputs and secondary (api request) is empty.

Any ideas?

6

If you weren’t to have fields with empty values the Merge node Combine by Position should have worked just fine.

If you cannot avoid having fields with empty values, try the Code node. The code below removes fields with empty values and then combines objects into one. The last non-empty value for each field will prevail tho.

7

Seems we replied at the same time.

8

Screenshot 2025-03-06 at 1.53.13 PM
Screenshot 2025-03-06 at 1.53.13 PM1424×1121 173 KB

This is showing that the api request is getting to this node, but it’s not combined at all.

9

You need to Merge threads before Code. Check the template I shared above.

I do not see overlap across filed names from both inputs.

Shouldn’t Merge Combine by Position meet expectations?

image
image502×532 15.9 KB

10

Ya they won’t merge. That’s why I keep going to Code.

11

OK I tried the merge. Here is the result, which only shows the Loop output:

Screenshot 2025-03-06 at 2.04.05 PM
Screenshot 2025-03-06 at 2.04.05 PM1407×1045 162 KB

12

Can you repost your workflow with output data in 1st Google Sheets and HTTP Request nodes pinned?

13

Same problem.

Sheets lookup → filter → loop → http api request → merge
…loop …-------------------> merge

The row data and the api request have always been fine. Both merge and code nodes can’t seem to deal with this.

14

Not sure if this means anything. I get this in the CLI where n8n is started:

. Aborting.
The expression evaluated to a falsy value:

  a.ok(nodeExists)
 (execution 3000)
The expression evaluated to a falsy value:

  a.ok(nodeExists)

Problem with execution 3002: The expression evaluated to a falsy value:

  a.ok(nodeExists)
. Aborting.
The expression evaluated to a falsy value:

  a.ok(nodeExists)
 (execution 3002)
The expression evaluated to a falsy value:

  a.ok(nodeExists)

Problem with execution 3003: The expression evaluated to a falsy value:

  a.ok(nodeExists)
. Aborting.
The expression evaluated to a falsy value:

  a.ok(nodeExists)
 (execution 3003)
The expression evaluated to a falsy value:

Rebooted server, started n8n up again.

15

OK, tried various ways of extracting those values, different javascript, and the http request, although it seems it’s working fine and outputting proper json, isn’t undefined in the workflow.

Am I missing something? I simply cannot this parsed out.


[
{
    "apilat": "43.6669123",
    "apilon": "-79.345361"
},
{
    "rownum": 5,
    "address1": "807 Gerrard St E"
}
]

Screenshot 2025-03-07 at 6.38.52 AM
Screenshot 2025-03-07 at 6.38.52 AM884×653 40.3 KB

However in that workflow, the merge has two objects in a single json object. The Google Sheets node won’t recognize rownum for some reason. The match field isn’t recognizing that incoming json element. It’s right there. Why won’t it see it?

Screenshot 2025-03-07 at 6.42.59 AM
Screenshot 2025-03-07 at 6.42.59 AM1403×892 82.2 KB

{
  "errorMessage": "Bad request - please check your parameters",
  "errorDescription": "Invalid data[0]: Unable to parse range: Sheet1!W",
  "errorDetails": {
    "rawErrorMessage": [
      "400 - {\"error\":{\"code\":400,\"message\":\"Invalid data[0]: Unable to parse range: Sheet1!W\",\"status\":\"INVALID_ARGUMENT\"}}"
    ],
    "httpCode": "400"
  },
  "n8nDetails": {
    "nodeName": "Google Sheets1",
    "nodeType": "n8n-nodes-base.googleSheets",
    "nodeVersion": 4.5,
    "resource": "sheet",
    "operation": "update",
    "time": "3/7/2025, 6:42:54 AM",
    "n8nVersion": "1.81.4 (Self Hosted)",
    "binaryDataMode": "default",
    "stackTrace": [
      "NodeApiError: Bad request - please check your parameters",
      "    at ExecuteContext.apiRequest (/usr/lib/node_modules/n8n/node_modules/n8n-nodes-base/nodes/Google/Sheet/v2/transport/index.ts:78:9)",
      "    at processTicksAndRejections (node:internal/process/task_queues:95:5)",
      "    at GoogleSheet.batchUpdate (/usr/lib/node_modules/n8n/node_modules/n8n-nodes-base/nodes/Google/Sheet/v2/helpers/GoogleSheet.ts:198:20)",
      "    at ExecuteContext.execute (/usr/lib/node_modules/n8n/node_modules/n8n-nodes-base/nodes/Google/Sheet/v2/actions/sheet/update.operation.ts:417:3)",
      "    at ExecuteContext.router (/usr/lib/node_modules/n8n/node_modules/n8n-nodes-base/nodes/Google/Sheet/v2/actions/router.ts:64:14)",
      "    at ExecuteContext.execute (/usr/lib/node_modules/n8n/node_modules/n8n-nodes-base/nodes/Google/Sheet/v2/GoogleSheetsV2.node.ts:30:10)",
      "    at WorkflowExecute.runNode (/usr/lib/node_modules/n8n/node_modules/n8n-core/src/execution-engine/workflow-execute.ts:1123:8)",
      "    at /usr/lib/node_modules/n8n/node_modules/n8n-core/src/execution-engine/workflow-execute.ts:1470:27",
      "    at /usr/lib/node_modules/n8n/node_modules/n8n-core/src/execution-engine/workflow-execute.ts:2029:11"
    ]
  }
}
16

I think Google Sheet node receives a list of objects and works them one by one. First object in the list doesn’t have rownum property hence undefined.

In the Merge node you need to change merge type to Combine from (I assume) Append.

Hope this works for you.

P.S. It would be easier to play around with your workflow if you posted it. Just in case, I am not a part of n8n team. Loitering around to learn from others’ here :slightly_smiling_face:

17

OK, the node can actually decipher row_numbers. It’s the last item on the match field because it’s part of the lookup algo for the node.

If I take a completely different column called address1, then it’s a valid column. In fact that column is the one that’s used for the api call. I can’t use that either. Both of these approaches won’t match up this relationship.

Screenshot 2025-03-07 at 7.44.30 AM
Screenshot 2025-03-07 at 7.44.30 AM594×239 13.6 KB

As for the workflow, I’m just getting json from two upstream nodes. I’m trying to identify a row in a google sheet and updating two fields after I locate the row. Both ways I’m trying to find the row, don’t work for some reason. And I don’t know why.

18

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.