Deleting Google Sheets Records

[BobbyG77]

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:

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

After:

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.

[Mark_Turrell]

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

[BobbyG77]

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.

[Mark_Turrell]

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!!

[BobbyG77]

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!

[ThinhDinh]

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.

Tutorial: Arrayformula in Google Sheets, good practices & how to overcome Arrayformula restrictions with scripts

Hi, Today I will show you the script that can make copying formulas down work, when it does not work with arrayformula. But first, let’s have some introductions about arrayformula, for those who has not used this before in Google Sheets, some equivalents in Glide and some good practices while using it. I hope it can help you in building your Glide apps as well as applying it in your normal day job.

1/ Definition

Arrayformula is defined as “a range, mathematical expression using one cell range …

[Darren_Murphy]

Getting rid of excess rows and/or columns (the easy way)

Anyone that’s been using Glide for a while will know that it’s generally a good practice to remove excess rows from the bottom of your Google Sheets. But doing so manually can be a bit tedious. This pair of script functions will do the leg work for you. Just run them once in your Google Spreadsheet and they will clean everything up in one quick pass. The following configuration options are available: process.rows : [true|false], whether or not to prune excess rows (default true) process.colu…

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

[Mark_Turrell]

@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

[Darren_Murphy]

@Mark_Turrell

And every once in a while there is a g-formula that does not work in a standard way with =arrayformula

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

Scripts, scripts, scripts!

Here is another one that I use all the time as an alternative to ARRAYFORMULA. The neat thing about this is that it works with any formulas, whereas ARRAYFORMULA has some limitations. Again, I didn’t write this one myself, and I can’t recall where I got it from (probably Stackoverflow), so I can’t take any credit for it. But I’ve been using it for 3 or 4 years and it’s never failed me. What it does and how it works is described in the comments.

Summary

/** * Copy Formulas Down * This script…