Deleting Google Sheets Records

Dear Gliders,
I recently discovered in my client’s expense tracking app that there were a number of blank records in the Expenses sheet, with the exception of two columns containing ArrayFormula logic returning a value of “False”.

I first began by deleting the individual records 1 and 2 records at a time. I then discovered that Google Sheets had somehow inserted 999 blank rows into the sheet. I had read where deleting large numbers of rows can cause problems for Google Sheets with Glide.

I went back and reworked the ArrayFormulae causing the return of FALSE as follows:

Before: image

This formula returned a value of FALSE if the cell in column A was blank.

After: image

This eliminated the FALSE values from the two columns that were causing the problem.

My question is what is the safest way to get rid of the 999 blank rows?

This is a live app being used by a client. I don’t want to cause a problem.

Thanks for any insights.

If you try to delete those empty rows after your last row of real data, and then they reappear instantly, it means you have a formula working wrong in a column! I’ve had this before!

I copied all my columns that could be at fault I to another sheet.
Then I deleted the blank rows from my main sheet. If that was fine, I copied in the next column… Till I found the culprit. Then I redid that formula and all was fine again :slight_smile:

Thanks Mark. The thing is there is data after the 999 blank rows. I don’t know what caused the blank rows to be added. I will take your suggestion and copy the entire worksheet to another file, delete the two columns that were the original culprits and reintroduce them after deleting blank rows.

Having Data saved way down the bottom is a classic beginner’s mistake! I vaguely remember it was my first time using =arrayformula things. My glide app would write a new row in, but it would appear way way down.

Good luck!!

1 Like

It worked. I copied the two ArrayFormula columns to a separate sheet and deleted all blank rows. Then re-entered the ArrayFormulae in both columns and it’s all nice and tight with no blanks.

Thanks again!


Also, a good practice is to store arrayformulas in the header so you don’t accidentally delete them.

I covered that part in this post, hope it helps.

1 Like

I use the above script on most of my Google Sheets.


@ThinhDinh totally agree. I did not do this at first, putting my arrayformulas into Row 2… but eventually you run into problems. Now I know how to put the column heading into my formula, everything is running wonderfully well!

I have also got into =QUERY (thanks to you again!) which helps to merge data from different sheets (even separate GSheets) and manipulate the data.

And every once in a while there is a g-formula that does not work in a standard way with =arrayformula… then you need to get creative (or just do a better Google Search!). For instance I learned about RANDARRAY the other day as I wanted to sort a bunch of rows in random order but in Glide I was not able to use Sort as I had a Swipe inline list. The trick was to add a column with RANDARRAY and then things work like magic :slight_smile:

1 Like

Here is a scripting solution for functions that don’t work with ARRAYFORMULA…