[Solved] Google Sheets operation delete all data after specific row though amount set to 1

Questions
1

I have a Google Sheet have duplicate data, so I want to remove some data
Example sheet is: https://docs.google.com/spreadsheets/d/1o_XYT31GIjyjN2wCaZxpd2_L17M8IM82UjsyTSh0u_4/edit#gid=0

I want delete data in highlight picture

image

If I use simple operation data like this:

{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        140,
        430
      ]
    },
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        140,
        430
      ]
    },
    {
      "parameters": {
        "sheetId": "1o_XYT31GIjyjN2wCaZxpd2_L17M8IM82UjsyTSh0u_4",
        "options": {
          "valueRenderMode": "FORMATTED_VALUE"
        }
      },
      "name": "GS Read Data",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 1,
      "position": [
        310,
        430
      ],
      "credentials": {
        "googleApi": "n8n API"
      }
    },
    {
      "parameters": {
        "operation": "delete",
        "sheetId": "1o_XYT31GIjyjN2wCaZxpd2_L17M8IM82UjsyTSh0u_4",
        "toDelete": {
          "rows": [
            {
              "sheetId": 0,
              "startIndex": 5
            }
          ]
        }
      },
      "name": "GS Read Data2",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 1,
      "position": [
        590,
        430
      ],
      "credentials": {
        "googleApi": "n8n API"
      }
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "GS Read Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "GS Read Data": {
      "main": [
        [
          {
            "node": "GS Read Data2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

It will delete the data that I want. But if I put in to more complex workflow like this:

{
  "name": "21. Google Sheet marks updated 1 delete all",
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        50,
        130
      ]
    },
    {
      "parameters": {
        "sheetId": "1o_XYT31GIjyjN2wCaZxpd2_L17M8IM82UjsyTSh0u_4",
        "options": {
          "valueRenderMode": "FORMATTED_VALUE"
        }
      },
      "name": "GS Read Data",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 1,
      "position": [
        210,
        130
      ],
      "credentials": {
        "googleApi": "n8n API"
      }
    },
    {
      "parameters": {
        "conditions": {
          "number": [],
          "string": [
            {
              "value1": "={{$node[\"GS Read Data\"].json[\"updated\"]}}",
              "operation": "notEqual",
              "value2": "1"
            }
          ]
        }
      },
      "name": "IF Updated not equal 1",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        390,
        130
      ]
    },
    {
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$node[\"NoOp\"].json[\"count\"]}}",
              "operation": "notEqual",
              "value2": "1"
            }
          ]
        }
      },
      "name": "IF",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [
        730,
        160
      ]
    },
    {
      "parameters": {
        "batchSize": 1,
        "options": {}
      },
      "name": "SplitInBatches",
      "type": "n8n-nodes-base.splitInBatches",
      "typeVersion": 1,
      "position": [
        940,
        140
      ]
    },
    {
      "parameters": {
        "operation": "delete",
        "sheetId": "1o_XYT31GIjyjN2wCaZxpd2_L17M8IM82UjsyTSh0u_4",
        "toDelete": {
          "rows": [
            {
              "sheetId": 0,
              "startIndex": "={{$node[\"SplitInBatches\"].json[\"index\"]}}"
            }
          ]
        }
      },
      "name": "GS Read Data2",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 1,
      "position": [
        1110,
        140
      ],
      "credentials": {
        "googleApi": "n8n API"
      }
    },
    {
      "parameters": {},
      "name": "NoOp",
      "type": "n8n-nodes-base.noOp",
      "typeVersion": 1,
      "position": [
        570,
        160
      ]
    }
  ],
  "connections": {
    "GS Read Data": {
      "main": [
        [
          {
            "node": "IF Updated not equal 1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF Updated not equal 1": {
      "main": [
        [],
        [
          {
            "node": "NoOp",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "IF": {
      "main": [
        [
          {
            "node": "SplitInBatches",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "SplitInBatches": {
      "main": [
        [
          {
            "node": "GS Read Data2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Start": {
      "main": [
        [
          {
            "node": "GS Read Data",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "NoOp": {
      "main": [
        [
          {
            "node": "IF",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  },
  "active": false,
  "settings": {},
  "id": "21"
}

It will delete the data that I want and ALL DATA after that data.

Please show me how to fix this. Thanks!

1 Like
2

Ah yes, that seemed definitely strange. It turned out that the “index” was a string and not a number. That means that in an n8n internal calculation where it calculated the end row to delete, instead of doing 5 + 1 which is 6 it did '5' + 1 which is 51.

Changed the code that it always converts it now to a number so that it is no longer a problem if it actually returns a string.

Till that version got released an easy fix would be to change the expression from currently:

{{$node["SplitInBatches"].json["index"]}}

to

{{parseInt($node["SplitInBatches"].json["index"])}}

It will then work fine.

2 Likes
3

It works like a charm, thanks. Will this fix will be added to next version?

1 Like
4

Yes like written above did the code already get changed so will be automatically released with the next version.

1 Like