Deleting blank columns & performance improvement

[Swoopy]

Has anyone ever compared app’s performance between leaving the blank columns on spreadsheet/tables and without (I’ve seen this tip in an AppSheet clip once). I, myself, have not seen the different but this may be because my file is not big enough.

Regards,

[MultiTech]

There is a cell limit to Gsheets (it’s huge, but it’s there) - and it’s a CELL limit, not a data limit or something else - so deleting any empty columns will allow you to extend the capacity of your table to hold rows.

But once you get to the point where you’re reaching a cell-limit, you might want to think about migrating something to a SQL - or at least work on a way to clean up your data and shrink it down.

[Ovais_Shah]

MultiTech_Visions:

ching a cell-li

Hi there,
I am not a developer but i am using AppSheet. This cell limit in Gsheets is hampering my app. I want to know what is the best alternative for me to use. Please suggest an alternative wherein I can do calculations like that of googlesheets or excel. I would appreciate a quick response.

[MultiTech]

Hello @Ovais.Shah.

The ultimate solution is to migrate all the calculations into AppSheet itself; it’s a general guideline to limit the number of formulas running in a google sheet, as this will make your app run slow.

As a quick fix: try spreading things out into different sheets, this way you’re spreading the load around.

[Rafael_ANEIC-PY]

Hey @MultiTech_Visions i got a quick question, so it’s all around better to have all calculations in appsheet? even if it involves the use of heavy formulas like select(), lookup() and combinations of those two?

[MultiTech]

Hello @Rafael_ANEIC-PY

Yes, it is ALWAYS a better idea to put things inside of AppSheet - but care must be taken WHERE you’re putting these “heavy” formulas.

Virtual columns, for instance. If you’ve got a formula like this (prepare yourself):

I would NEVER put that inside a virtual column; there is way too much happening inside there - especially the LOOKUP() that’s inside the SELECT(), never do that in a virtual column.

But in reality, many times the data that a formula like this is calculating may actually be a “static” value - in the sense that it only changes when the row data changes. (Not this particular example, but I wanted something of a crazy formula to show. )

---

It’s all about variable handling and data subsets.

Sometimes it’s necessary to copy a piece of data from one “layer” of your data structure to another - just to make it easier on the system. And if you MUST have a heavy formula, you can reduce the load on the server by creating slices that hold sub-sets of data.

If I’ve got a crazy formula like the one above; I’d rather run that over a slice that contains 40% of the records that the original table.

What happens if I leave formulas on the sheet?

---

Then every time data is submitted to the sheet… AppSheet waits for the new row calculations BEFORE moving on to the next.

This is because there might be things that need to be triggered or updated between one data push and the next.
Think about it like this: if you didn’t wait for the formulas to update when YOU were working in the sheet, then after you entered one piece of data things might be off for when you’re entering the next piece of data - AppSheet works the same way, just a bit faster cause it’s all server-to-server.

So if I’ve got 15 updates queued up… it’s one at a time…

• first update is pushed to the sheet
  • then we wait for any row formulas to update
• then the 2nd can fly
  • and we wait again.

rinse and repeat.

[Rafael_ANEIC-PY]

Hey @MultiTech_Visions, thanks you for giving me such a complete answer, i love your videos btw, i learned a lot from the one about long forms.

Virtual columns, for instance. If you’ve got a formula like this (prepare yourself):

I was asking exactly because of that kind of monstrous solutions, like this one:

I use that one to make a “summary” table view for showing within a dashboard with filters

Then every time data is submitted to the sheet… AppSheet waits for the new row calculations BEFORE moving on to the next.

I see, so if i have 0 spreadsheet formulas appsheet can “skip” that waiting period in each sync? that seems like a huge time save, thank you again Matt.

[Kirk_Masden]

I’ve never tested this. When you say “blank columns,” do you mean columns in your sheet that are completely blank (no headers) and are not listed as columns in the table? If so, I don’t think it would affect the AppSheet side of the app directly. However, I know that Ben Collins has pointed out that excess blank columns and rows can contribute to slow Google sheets. I also know that slow Google sheets, in turn, can cause slow app syncs:

Sync slow? Your GSheet might be the problem! Tips & Tricks 💡

If your Google spreadsheet simply holds your data and you can do all of the calculations you need to do without difficult via AppSheet, then you can skip this tip. If, one the other hand, you use your app to collect fairly large amounts of data, then use extra Google sheets to do some back-end number crunching, keep reading. [loading_bar_2] This gif of Google sheets recalculating is taken from the following page by Ben Collins: I recently had a problem of this type and my solution what to …

If you mean blank columns (or blank cells) inside of AppSheet tables, I’m not sure.

By the way, at first I thought you were asking about blank rows, a question I had asked earlier:

Question about security filters and blank rows Questions

For all practical purposes, AppSheet ignores any row without a key column value (which includes blank rows).

I know you’re asking about columns, not rows, but I thought I’d add this as a little footnote.

[Swoopy]

I’ve deleted blank rows (about 800 rows+) in all sheets and I think I can feel that it syncs faster.

[Koichi_Tsuji · Accepted solution]

And you delete “column” they are not in use. It will run Appsheet faster as well, when we work on spreadsheet like Google sheet.

[Swoopy]

Hopefully this is not just placebo