There is a Tips & Trick inside the Google Sheets app documentation that is a pretty important component of using the Delete a Row module effectively.
Here is one example of someone falling into this trap.
When you search rows in Sheets, and then proceed to delete rows by row number, the wrong rows get deleted after the first deletion. The reason for this is as follows:
The Search Rows module returns a row number for each row to delete that fits the search criteria. By default the results come back in ASCENDING order of the row number.
So say you searched for rows and the search result output bundles come back with row number 2, 5, 10, 15. The following Delete Rows module will start to delete with row number 2. Then proceed to delete row 5, 10 and 15 in that order (the search rows output bundle order).
BUT, after row 2 was deleted, all the rows under row 2 MOVE UP automatically one row, and row number 5 now becomes row number 4, row 10 becomes row 9 and row 15 becomes row 14.
So when Delete Rows module attempts to delete row 5 while processing the next output bundle, the actual row being deleted was the original row 6 (which is now row 5 after row 2 was deleted previously). All the row numbers SHIFT up one row after each deletion so your original set of row numbers after the first row number no longer point to the correct rows AT ALL.
There is a subtle solution to this, and that is to return the search rows output bundles in DESCENDING ORDER OF THE ROW NUMBERS. That way the rows are deleted from the bottom going up rather from the top down. When you delete the highest search results row number, only the rows below that row number shift up and all the rows above it remain the same row number. You can safely then delete the correct rows!
Here’s how you configure Search Rows module to properly Sort by row number in Descending order
And here is a small scenario for you to experiment with, copy and paste into a new scenario
{
"subflows": [
{
"flow": [
{
"id": 2,
"module": "google-sheets:filterRows",
"version": 2,
"parameters": {
"__IMTCONN__": 648527
},
"mapper": {
"from": "drive",
"valueRenderOption": "FORMATTED_VALUE",
"dateTimeRenderOption": "FORMATTED_STRING",
"spreadsheetId": "1YXdwUImWG4Lcp3m6RnbMCDs215D-5ebF7PyJgimnR5s",
"sheetId": "Sheet1",
"includesHeaders": true,
"tableFirstRow": "A1:Z1",
"filter": [
[
{
"a": "B",
"o": "text:equal",
"b": "a"
}
]
],
"sortOrder": "desc",
"orderBy": "__ROW_NUMBER__",
"fieldType": "number"
},
"metadata": {
"designer": {
"x": 0,
"y": 0
},
"restore": {
"parameters": {
"__IMTCONN__": {
"label": "NewPath Google connection (alexs@newpathconsulting.com)",
"data": {
"scoped": "true",
"connection": "google"
}
}
},
"expect": {
"from": {
"label": "Select from My Drive"
},
"valueRenderOption": {
"mode": "chose",
"label": "Formatted value"
},
"dateTimeRenderOption": {
"mode": "chose",
"label": "Formatted string"
},
"spreadsheetId": {
"mode": "chose",
"label": "Testing"
},
"sheetId": {
"mode": "chose",
"label": "Sheet1"
},
"includesHeaders": {
"mode": "chose",
"label": "Yes"
},
"tableFirstRow": {
"label": "A-Z"
},
"sortOrder": {
"mode": "chose",
"label": "Descending"
},
"orderBy": {
"mode": "chose",
"label": "Row number"
},
"fieldType": {
"mode": "chose",
"label": "Number"
}
}
},
"parameters": [
{
"name": "__IMTCONN__",
"type": "account:google",
"label": "Connection",
"required": true
}
],
"expect": [
{
"name": "from",
"type": "select",
"label": "Enter a Spreadsheet ID and Sheet Name",
"required": true,
"validate": {
"enum": [
"drive",
"share"
]
}
},
{
"name": "valueRenderOption",
"type": "select",
"label": "Value render option",
"validate": {
"enum": [
"FORMATTED_VALUE",
"UNFORMATTED_VALUE",
"FORMULA"
]
}
},
{
"name": "dateTimeRenderOption",
"type": "select",
"label": "Date and time render option",
"validate": {
"enum": [
"SERIAL_NUMBER",
"FORMATTED_STRING"
]
}
},
{
"name": "limit",
"type": "number",
"label": "Maximum number of returned rows"
},
{
"name": "spreadsheetId",
"type": "select",
"label": "Spreadsheet",
"required": true
},
{
"name": "sheetId",
"type": "select",
"label": "Sheet Name",
"required": true
},
{
"name": "includesHeaders",
"type": "select",
"label": "Table contains headers",
"required": true,
"validate": {
"enum": [
true,
false
]
}
},
{
"name": "tableFirstRow",
"type": "select",
"label": "Column range",
"required": true,
"validate": {
"enum": [
"A1:Z1",
"A1:BZ1",
"A1:CZ1",
"A1:DZ1",
"A1:MZ1",
"A1:ZZ1",
"A1:AZZ1",
"A1:BZZ1",
"A1:CZZ1",
"A1:DZZ1",
"A1:MZZ1",
"A1:ZZZ1"
]
}
},
{
"name": "filter",
"type": "filter",
"label": "Filter",
"options": "rpc://google-sheets/2/rpcGetFilterKeys?includesHeaders=true"
},
{
"name": "sortOrder",
"type": "select",
"label": "Sort order",
"validate": {
"enum": [
"asc",
"desc"
]
}
},
{
"name": "orderBy",
"type": "select",
"label": "Order by"
},
{
"name": "fieldType",
"type": "select",
"label": "Field Type",
"validate": {
"enum": [
"string",
"number",
"date"
]
}
}
],
"interface": [
{
"name": "__ROW_NUMBER__",
"label": "Row number",
"type": "number"
},
{
"name": "__SPREADSHEET_ID__",
"label": "Spreadsheet ID",
"type": "text"
},
{
"name": "__SHEET__",
"label": "Sheet",
"type": "text"
},
{
"name": "0",
"label": "ID (A)",
"type": "text"
},
{
"name": "1",
"label": "Data1 (B)",
"type": "text"
},
{
"name": "2",
"label": "Data2 (C)",
"type": "text"
},
{
"name": "3",
"label": "(D)",
"type": "text"
},
{
"name": "4",
"label": "(E)",
"type": "text"
},
{
"name": "5",
"label": "(F)",
"type": "text"
},
{
"name": "6",
"label": "(G)",
"type": "text"
},
{
"name": "7",
"label": "(H)",
"type": "text"
},
{
"name": "8",
"label": "(I)",
"type": "text"
},
{
"name": "9",
"label": "(J)",
"type": "text"
},
{
"name": "10",
"label": "(K)",
"type": "text"
},
{
"name": "11",
"label": "(L)",
"type": "text"
},
{
"name": "12",
"label": "(M)",
"type": "text"
},
{
"name": "13",
"label": "(N)",
"type": "text"
},
{
"name": "14",
"label": "(O)",
"type": "text"
},
{
"name": "15",
"label": "(P)",
"type": "text"
},
{
"name": "16",
"label": "(Q)",
"type": "text"
},
{
"name": "17",
"label": "(R)",
"type": "text"
},
{
"name": "18",
"label": "(S)",
"type": "text"
},
{
"name": "19",
"label": "(T)",
"type": "text"
},
{
"name": "20",
"label": "(U)",
"type": "text"
},
{
"name": "21",
"label": "(V)",
"type": "text"
},
{
"name": "22",
"label": "(W)",
"type": "text"
},
{
"name": "23",
"label": "(X)",
"type": "text"
},
{
"name": "24",
"label": "(Y)",
"type": "text"
},
{
"name": "25",
"label": "(Z)",
"type": "text"
}
]
}
},
{
"id": 1,
"module": "google-sheets:deleteRow",
"version": 2,
"parameters": {
"__IMTCONN__": 648527
},
"mapper": {
"from": "drive",
"select": "list",
"sheetId": 0,
"rowNumber": "{{2.`__ROW_NUMBER__`}}",
"spreadsheetId": "/1YXdwUImWG4Lcp3m6RnbMCDs215D-5ebF7PyJgimnR5s"
},
"metadata": {
"designer": {
"x": 300,
"y": 0
},
"restore": {
"expect": {
"from": {
"label": "My Drive"
},
"select": {
"label": "Select from the list"
},
"sheetId": {
"label": "Sheet1"
},
"spreadsheetId": {
"path": [
"Testing"
]
}
},
"parameters": {
"__IMTCONN__": {
"data": {
"scoped": "true",
"connection": "google"
},
"label": "NewPath Google connection (alexs@newpathconsulting.com)"
}
}
},
"parameters": [
{
"name": "__IMTCONN__",
"type": "account:google",
"label": "Connection",
"required": true
}
],
"expect": [
{
"name": "select",
"type": "select",
"label": "Enter a Spreadsheet and Sheet ID",
"required": true,
"validate": {
"enum": [
"map",
"fromAll",
"list"
]
}
},
{
"name": "rowNumber",
"type": "uinteger",
"label": "Row number",
"required": true
},
{
"name": "from",
"type": "select",
"label": "Choose a Drive",
"required": true,
"validate": {
"enum": [
"drive",
"share",
"team"
]
}
},
{
"name": "spreadsheetId",
"type": "file",
"label": "Spreadsheet ID",
"required": true
},
{
"name": "sheetId",
"type": "select",
"label": "Sheet ID",
"required": true
}
]
}
}
]
}
],
"metadata": {
"version": 1
}
}