Update Duplicate Data in Sheets

Hi, I am having a spreadsheet like this
duplicate

I have tried updating the values, but only the first one of the duplicates work, then the execution will just be looping over that one and not update the other rows.

Here is an example of a workflow:

{
  "nodes": [
    {
      "parameters": {
        "authentication": "oAuth2",
        "sheetId": "1vNI19PdlsvadpXfGBjTQX2VfFq3CmdasANo-b-DbgKw",
        "range": "Sheet2!A:F",
        "options": {}
      },
      "name": "Google Sheets",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 1,
      "position": [
        350,
        300
      ],
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "2",
          "name": "Google Sheets account"
        }
      }
    },
    {
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$node[\"Google Sheets\"].json[\"info\"]}}",
              "operation": "isEmpty"
            }
          ]
        }
      },
      "name": "IF",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        580,
        300
      ]
    },
    {
      "parameters": {
        "url": "=https://api.zippopotam.us/us/{{$node[\"SplitInBatches\"].json[\"zip\"]}}",
        "options": {}
      },
      "name": "HTTP Request",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 1,
      "position": [
        950,
        280
      ],
      "alwaysOutputData": true
    },
    {
      "parameters": {
        "authentication": "oAuth2",
        "operation": "update",
        "sheetId": "1vNI19PdlsvadpXfGBjTQX2VfFq3CmdasANo-b-DbgKw",
        "range": "Sheet2!A:F",
        "key": "zip",
        "options": {
          "valueRenderMode": "UNFORMATTED_VALUE"
        }
      },
      "name": "Google Sheets1",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 1,
      "position": [
        1360,
        280
      ],
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "2",
          "name": "Google Sheets account"
        }
      }
    },
    {
      "parameters": {
        "keepOnlySet": true,
        "values": {
          "string": [
            {
              "name": "info",
              "value": "={{$node[\"HTTP Request\"].json[\"country abbreviation\"]}}"
            },
            {
              "name": "zip",
              "value": "={{$node[\"SplitInBatches\"].json[\"zip\"]}}"
            }
          ]
        },
        "options": {}
      },
      "name": "Set",
      "type": "n8n-nodes-base.set",
      "typeVersion": 1,
      "position": [
        1160,
        280
      ]
    },
    {
      "parameters": {
        "batchSize": 1,
        "options": {}
      },
      "name": "SplitInBatches",
      "type": "n8n-nodes-base.splitInBatches",
      "typeVersion": 1,
      "position": [
        750,
        280
      ]
    },
    {
      "parameters": {
        "triggerTimes": {
          "item": [
            {
              "mode": "everyMinute"
            }
          ]
        }
      },
      "name": "Cron",
      "type": "n8n-nodes-base.cron",
      "typeVersion": 1,
      "position": [
        140,
        300
      ]
    }
  ],
  "connections": {
    "Google Sheets": {
      "main": [
        [
          {
            "node": "IF",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF": {
      "main": [
        [
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "HTTP Request": {
      "main": [
        [
          {
            "node": "Set",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set": {
      "main": [
        [
          {
            "node": "Google Sheets1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "SplitInBatches": {
      "main": [
        [
          {
            "node": "HTTP Request",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Cron": {
      "main": [
        [
          {
            "node": "Google Sheets",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

Thanks

Because the key in the Google Sheets node needs to be unique, and since all zips are 10080 it updates the first that it finds. Also, the split batches node in your workflow is not needed.

1 Like

If I remove the Split into Batches it still doesn’t work. Is there a way to bypass this?

No, as I mentioned, the key in the Google Sheets node has to be unique, else the node will always update the first record. This is how the Google Sheets API works. You can add another column with a unique value, like a number, and every time you add a new record, you increment that number.

Regarding removing the split batches node, that was only a suggestion, as you do not need it since n8n automatically iterates over all input items.

1 Like

Thanks. I have been researching for a way to auto increment the number in Google sheets on a new column, because the data come from a WP Forms integrated, but unfortunately I didn’t find a way to do that automatically. Do you know of a way out? Thanks.

Is not WP Forms adding more data to the sheet? Nothing that you can use as a primary key? Maybe IP? email? Phone?

1 Like

All the data is same(that is when someone fills the form twice) , but lemme try figuring out what changes I could make to get a unique key. Thanks

creates a new column and puts the =row() this formula no google spreadsheets will return the current row number.