Deleting or updating Blank Rows in Bulk Google Sheets

I had a source spreadsheet which has around 8500 records, of these about 600 rows are blank, mixed in amongst the data, between 1-4 rows blank at a time, I’m working with Google Sheets. After some initial processing I have only 2 columns with header.

I need to perform various operations on this sheet (which changes regularly - from my supplier), however the blank rows are causing me issues, as a standard “Order By” in Search Rows only seems to order the rows down to the first blank, and doesn’t sort the whole sheet properly.

I’m happy to either delete these blank rows or update them with a placeholder value like XXXXX but not having much luck with either. The Delete Rows module I can’t get to work at all. The “Update a Cell” works and I can make it update a cell by using A:[Row Number] from the output of a search with filter (and then an integrator), this works but consumes 600 processes and the Google API crashes.

I’ve tried using an aggregator to to create bundles then use the Bulk Update Rows (advanced), however from what I can tell this will only update rows in a single range, not several ranges within the sheet. I’ve successfully used that on another spreadsheet by first sorting the sheet as another step first, but in this case the blank rows are stopping that occurring.

I hope I’m explaining this properly. Any help would be much appreciated.
Happy to use either the Google Sheets or the CSV modules.

1 Like