Google Sheets - Auto Name Matching Error

Questions
1

Title: Google Sheets node (Auto-Map, typeVersion 3) writes values one column off after upgrade to 2.22.5

Describe the problem/error/question

After upgrading n8n from an older release (~10 versions back) to 2.22.5, Google Sheets nodes using “Auto-Map Input Data to Columns” started writing values into the wrong columns on Append and Update. No node was changed — they’re still typeVersion 3 (latest is 4.7). The only variable was the n8n version.

The misalignment is a consistent one-column left shift that begins partway across the row:

  • The first block of columns map correctly.

  • From a specific column onward, every value lands one cell to the left of its matching header, cascading to the end of the row.

Minimal illustration (incoming JSON key → where it should go vs. where it lands):

header: ... | colN  | colN+1 | colN+2 | colN+3
should:     | (empty)| fieldA | fieldB | fieldC
actual:     | fieldA | fieldB | fieldC | (empty)   ← everything shifted 1 left from colN

Key point: the read side is correct — it’s the write that misplaces the data. The Append node’s output JSON shows e.g. fieldA: "X" and reports success, but reading that same row back by key returns fieldA: "Y" (the neighbouring column’s value). The value was physically written one column over. Downstream, any in-sheet formula columns and the follow-up Update step then resolve against the wrong cells and return #REF!.

So Auto-Map’s name->column matching is partially failing: early columns match, a contiguous later block is shifted by one.

Sheet characteristics that appear to trigger it (a simple sheet does not reproduce it):

  • A wide sheet (~150 columns), of which the workflow only supplies ~30; the remainder are blank or in-sheet formula columns.

  • A duplicate header name (identical header text in two different columns).

  • A column whose value is a nested object / JSON blob rather than a scalar.

This pattern (Append → read back → Update with row/formula context) is used across many workflows, so it affects all of them.

What is the error message (if any)?

No node error — Append/Update report success. The only visible failure is #REF! in dependent sheet formulas, caused by the upstream column shift.

Questions for the team:

  1. Did the Auto-Map column-resolution logic for Google Sheets typeVersion 3 change between recent releases and 2.22.5? (Is v3 now routed through different code?)

  2. Are duplicate headers and/or nested-object cell values known to break Auto-Map column alignment in current versions?

  3. Recommended path — move these nodes to explicit “Map Each Column”, upgrade to node 4.x, or is this a v3 regression worth patching?

Workflow / output: Happy to share a sanitized minimal-repro workflow and a before/after row on request.

n8n setup

  • n8n version: 2.22.5 (upgraded from ~10 releases prior, exact prior tag unknown)

  • Database: PostgreSQL

  • EXECUTIONS_PROCESS: main

  • Running via: Docker, with workers

  • OS: Ubuntu

  • Google Sheets node: typeVersion 3 (unchanged across the upgrade)

2

Hi @Dwayne_Taylor

Your data is shifting because n8n’s “Auto-Map” feature is getting confused about which piece of information belongs in which column. Think of it like a conveyor belt: once the system hits a “bump”—such as a column name that appears twice or a complex piece of data—it loses its place. Because it doesn’t know where to put that specific item, it accidentally pushes everything following it one spot to the left, causing a domino effect of misplaced data for the rest of the row.

This is likely happening now because the recent update to n8n changed how the software calculates these positions behind the scenes. Even though you didn’t change your nodes, the “engine” running them was updated. In a wide sheet with duplicate headers, the system is struggling to distinguish between columns with the same name, which triggers the misalignment and leads to the broken formulas you’re seeing in your spreadsheet.

To fix this, the safest move is to stop relying on “Auto-Map” and switch to “Map Each Column” for your critical workflows. By manually telling n8n exactly which field goes into which column, you remove the guesswork and prevent the system from shifting your data. If you can, renaming your duplicate headers to be unique would also solve the root cause of the confusion.

Does that help?

3

On top of switching to “Map Each Column”, there’s one more option worth trying: upgrade the Google Sheets node to the latest typeVersion (4.7) (edit the JSON of this node). TypeVersion 4 rewrote how column matching works internally, so the Auto-Map shift issue tied to typeVersion 3 behavior in newer n8n builds likely won’t affect it. Worth trying before manually remapping all 150 columns.

4

Hey team - thx for the prompt follow up

Does this assume that the column names have a duplicate?
Given some of my worksheets did have duplicates in them some did not and were still throwing errors

Also @nguyenthieutoan do you have documentation on the node upgrade - I dont see the functionality you are referring to

5

Hi @Dwayne_Taylor

I’m sorry, you have to edit the node version manually by editing this node’s JSON.

Building on what was already shared above, there are a few sheet patterns that seem to make Auto‑Map in typeVersion 3 much easier to break after an n8n upgrade:

  • Very wide sheets (~150+ columns) where the workflow only writes to a small subset of columns and the rest are empty or formula columns.
  • Duplicate header names – Auto‑Map relies on header text, so identical names can confuse the matching logic.
  • Header names with special characters (for example, line breaks), which Google Sheets accepts but n8n doesn’t always recognise as the same name.
  • Columns that store nested objects / JSON blobs instead of simple scalar values.

When a few of these are combined, that’s exactly where we start to see the “one column to the left” shift you described, even though the node itself wasn’t changed. In those cases, moving critical flows to explicit “Map Each Column” on a newer Sheets node version is usually the most reliable long‑term fix.

6

@Dwayne_Taylor on the typeVersion bump — open the affected workflow, click the ... menu (top right of canvas) → Download. open the downloaded JSON file in a text editor and find ur Google Sheets node, then change typeVersion from 3 to 4.7 and add the matching mode field. before/after looks like:

{
  "before": {
    "parameters": {
      "operation": "append",
      "documentId": "...",
      "sheetName": "...",
      "dataMode": "autoMapInputData"
    },
    "type": "n8n-nodes-base.googleSheets",
    "typeVersion": 3
  },
  "after": {
    "parameters": {
      "operation": "append",
      "documentId": "...",
      "sheetName": "...",
      "columns": {
        "mappingMode": "defineBelow",
        "matchingColumns": ["row_id"],
        "value": {}
      }
    },
    "type": "n8n-nodes-base.googleSheets",
    "typeVersion": 4.7
  }
}

then Import the edited JSON back (top-right ... → Import). the v4 node uses defineBelow mapping where u explicitly bind each input field to a sheet column, so duplicate headers + 150-col-wide sheets stop being a footgun.

on ur first Q (does it apply without dupes) — yes, the same matching logic underlies all 4 of the patterns @nguyenthieutoan listed (wide sparse sheets, special chars in headers, nested objects). dupes are just the most obvious one. if some of ur affected sheets dont have dupes, ur probably hitting one of the others — most likely the nested-object case since u said v3 was reading correctly but writing wrong (the JSON blob serializer in v3 leaks into adjacent columns when fields are sparse).