Problem with Google Sheets Node "Delete Rows"

Hello everyone,

I’m facing a problem that I couldn’t solve by myself.
I had a case where I was trying to delete specific rows in a Google Sheet.

You can copy the sheet I used here: https://docs.google.com/spreadsheets/d/1vFCu5RfKpAebUWDmtqCFTez8lqJ9ZzRxR_iZCqb9mZ8/copy

Screenshot:

I created a “delete” column and set the values “yes” or “no” for each row.
I wanted all rows with the value “yes” to be deleted.
Unfortunately, this was much more complicated than I expected and I couldn’t get it to work as I wanted.

The “Delete Rows or Columns” operation in the Google Sheet node does not provide many options.
I tried to set the “Row Number” that I got from the “Get Rows” node and add it to the “Start Row Number” field in the “Delete Rows” node.

The main problem here is that the “Delete Rows” node only looks for the row number.
When the delete process starts in the sheet, rows are deleted one by one and the rows below the deleted ones move up.
So their initial position/row number changes, but the deletion is still in process for the initial row number.
As a result, rows that shouldn’t be deleted are deleted.

The problem could be solved if we could set a “Column to Match On” like in the “Append or Update Row” operation.

If I haven’t missed anything, this may be a feature request, but if anyone has a solution for this, I’m grateful!

Thanks in advance.

This is the Workflow:

Information on your n8n setup

  • n8n version: 1.29.1
  • Database (default: SQLite): Postgres
  • n8n EXECUTIONS_PROCESS setting (default: own, main): default
  • Running n8n via (Docker, npm, n8n cloud, desktop app): docker
  • Operating system: Debian 11

Também estou travado nisso

hello @denobeno

There is a little trick. You need to reverse the order for the row_number :slight_smile:

2 Likes

Hi @barn4k,

thank you very much for the input, to be honest i didn’t about just reversing the order but it totally makes sense.

It’s a good solution for now, but it does not prevent false deletions in some cases.
If you think of someone working on a sheet while the automation is running and a row is created/deleted somewhere in between, the row number will still change for all rows below it.

That’s why it would be best to have a unique identifier in the row.

Still it looks your solution is solid as long as no one touches the sheet while the automation is running. :+1:

The point is that Google Sheets API performs the deletion of the rows only by the row number. n8n can’t do anything here :slight_smile:

The right way would be not to delete rows one by one, as it is a slow operation, but to replace the entire sheet with the rest rows.

@barn4k

I see, it’s good to know rows always delete by row number.
I get your point by replacing the whole sheet, still this might not always be necessary and could overcomplicate things.

Anyway it could make sense to add following features to the “Row Delete” Node:

  • Add a “Column to match on” function
  • Always sort the items before deletion in an descending order

This way users can set the value to look up the rows to be deleted within the node and there is no issue with the order of deletion. Also, previous filtering and further sorting would not always be necessary.

If i’m missing anything here, please let me know.

Google Sheets API does not provide such a method. n8n acts as a proxy, it does what the API allows it to do, with some exceptions to handle underlying n8n stuff :slight_smile:

Well, technically, it’s not the API issue of how you are sorting items. Ideally, you will need to split the rows for deletion into chunks (e.g. delete rows 4-10, then 1-3) but that stuff is completely up to you.

Maybe you need an ascending order for some reason. And that also not the case for Column Deletion, as it will be performed from left to right, so you should pass the items to it in right-aligned order, usually

The “Column to match on” function is already available in the Append & Update Node. I’m pretty sure the Node itself utilizes the API to search for values in a specific column and return the index. I don’t see any problem here to add this function to the Delete Rows Node as well.

And yes, I know the sorting is not the APIs problem, but it’s the users problem when using the node and he doesn’t understand what’s under the hood.

Still the function could be implemented in the node itself, so the data that is being sent to the API is preprocessed. Both, Rows and Columns could have an additional option to delete rows in descending order.