What do you want to know about Google Sheets?

Hey Community! :wave:

I’m doing a collaboration with a Google Sheets expert in the next month or so.

  1. What questions would you ask a Google Sheets expert?

  2. What do you want to know how to do in Google Sheets?

  3. What is something you do all the time in Google Sheets that other people need to know?

Thanks :handshake::tada::raised_hands:


UPDATE: This video is live! :point_down:

5 Likes

When printing …why do we have to mark every time…selected cells …and it is not possible to set it as default ???

Why print feature is not available in scripts ??

Thanks !!

1 Like

I use array formulas all the time. When using glide and rows are being added, array formulas are essential to keep data streamlined and reduce user inputs/ actions.

@COVA Can you share an example? I’m not following…

Creating a pdf via script works, I even print different ranges and have one or two pages depending on the amount of rows to maintain a nice layout

If you want to start a script via onchange trigger after a set column action in Glide, this trigger does not always happen. Especially if you have other set column actions in your combined action before that. Obviously if Google is busy updating some other tables it misses the onchange which should trigger the script.

Of course.

Glide has a good example here: https://docs.glideapps.com/all/topics/spreadsheets/functions/arrayformula

However, I go beyond this for certain cases. For example, if my app is for a technician who creates estimates and invoices, there needs to be a way to increment the estimate and invoice number so each is unique. I use an array formula for this.

1 Like

Thanks for the documentation. Didn’t know that kind of stuff was in there.

Last question: :wink:
Is the benefit of using ARRAYFORMULA over the template column when you need the “template” in the sheet for some kind of external automation like in Apps Script of Zapier?

Obviously your invoice number example above is another good use case.

P.S. I was told early in my Gliding career that formulas in sheets slow down the apps. So I’ve avoided them like the plague! :smile:

Arrayformulas were vital in the early days of glide, before we had any computed columns or even a data editor. Back then, absolutely everything had to be done in the google sheet (including establishing relations with a special column header naming scheme). I’m sure there are still some one-off cases where it’s needed, but I think now we have almost everything we need with glide computed columns. The only real exceptions are maybe some functions that are exclusive to google sheets.

Google sheet formulas don’t necessarily slow down the app performance-wise. The app really has no knowledge that a google sheet even exists. But, it’s a difference between having calculations happen instantly vs waiting for data to sync from app → to glide → to google → run sheet formulas → then sync to glide → back to the app. So, it might appear slow, but you are just waiting for updated data to sync between 3 copies of the database and calculate on an external database outside of the app. It’s a long unnecessary sync process that can take a few seconds to several minutes. Computed columns, on the other hand, compute directly on the user’s device in real time, so there is zero delay in waiting bfoe result. It might still take a few seconds for that data to migrate to the google sheet, but that is transparent to the end user. They already see their results before the data even gets to the google sheet. That makes an app feel much faster.

With the invoice number example, I think it could be done exclusively in glide, but there is a slight flaw if two users create a new invoice at the same time depending on how the process of assigning invoice numbers is set up. Since each device has it’s own copy of the database, two users creating a new invoice at the same time may lead to duplicate invoice numbers, because each user is working with their own copy of the database before data is synced to glide and back out to everyone else. Works fine if there is no risk of two submissions at the same time, but something to consider if multiple users are creating invoices at the same time. There are various ways to overcome it though. Some have used Make to queue up the process and assign numbers in the order they are received. You could use the common trick in the forums to assign a sequencial number to each row, but be careful if you delete a row, which would mess up any rows afterwards. I’m sure even some sort of API could be created that assigns sequencial numbers whenever it’s called.

6 Likes

Thanks for the info Jeff. When I build my apps, I use app scripts exclusively to avoid Make, Zapier, and other services.

In the same example, I keep all invoices on one tab of a google sheet. I also have separate tabs for work orders, and estimates. As each invoice is submitted, the row gets populated. Regardless of who or when an invoice is submitted, when a row gets populated, a specific number gets assigned. In my apps, estimates, invoices, and work order rows don’t get deleted. In my contracts with customers I specify that I can scrub data (rows) when the customer is nearing the limit. However, to your point, if a row were deleted, the formula continues and increments correctly.

Here is an example formula for a tab containing estimates. My customer wanted the estimates to start at 15000.
=Arrayformula(“EST-”&row(A:A)+15000)

If this can be done in Glide, can you show me an example?

And lastly, I still use ARRAYFORMULAS to specify multiple row owners in Google Sheets. Can this be done in Glide exclusively?

Hola!

Your request looks like a case where we’d need a kind of filter or a SUMIFS() formula and it can be done in Glide surely (some ideas/solutions are smarter than others).

Here an example: SUMIFS and advanced formulas

Greetings!

Hola @darren

Taking advantage you have opened this Pandora’s box, I’d like to confirm with your friend:
What method is faster to find and/or sort a bulk of data: the Filter() or Query() function?
I suspect that the Filter() is faster (although it’s not more powerful and flexible) but I’d like to have another opinion about it. Thanks a lot.

Saludos!

1 Like

Hello @gvalero! Glad to be a part of this community and honored to be an ambassador!

I like your approach! Thx for sharing!

1 Like

@christoph Hello!

I rarely use onchange triggers. I use time based triggers. I also use single cells which have rollup values to show if a script should execute. If a script does execute, instead of running through each row in the sheet, I take all data and add it to a 2D array so the script is streamlined.

The next logical question is how many customers can I have with simultaneous time based triggers before my account starts throttling and nearing its limit? For my business, I deploy each customer’s code into their account and the triggers fire under their account. Spreading out the triggers to each customer frees up resources on my account.

2 Likes

For intensive Google sheets with many rows, importrange, scripts etc…what is more important…RAM or CPU??

Is s Google Chromebook worst than a normal PC with similar characteristics???

THANKS

I have used ArrayFormula several times in the past month. Very useful!

Check out this tutorial where Adam shows how to use ImportRange, ArrayFormula, and Query to dynamically combine 4 spreadsheets into 1 spreadsheet that can be used to build an app. :point_down:

2 Likes

Yes, absolutely.

I recently went through an exercise of applying Row Owners to about a dozen tables in a Google Sheets based App. Each of the affected tables required 6 owner columns. One containing the email address of the user that the record refers to, plus 5 additional owner columns that are assigned to various user roles.

I deliberately tried to avoid any sheet formulas, and found that it was actually quite easy. All I had to do was modify the Glide actions that added new rows such that all owner columns were set at that time.

I did use VLOOKUPS to initially populate the new owner columns for existing records, but I got rid of those afterwards

Did nobody notice he imported North twice instead of importing East? :nerd_face:

One thing: it seems to me that a better approach to limiting the number of rows forming your import range is to composite your ARRAYFORMULA function within an ARRAY_CONSTRAIN. Doing so ensures you only bring in actually populated rows, and probably saves cycles. There are some limitations (for example: there should be no empty rows in your data), but for most uses I think it works nicely.

scott simple

1 Like

. . . actually I’m rethinking this now, because I see that when I delete rows in Glide they are cleared, rather than deleted, in Google Sheets. That makes it tricky to use ARRAY_CONSTRAIN. There’s probably a workaround, but. . . Darn it.

You might not feel like it because you’ve developed a strong expertise in Google Sheets, but if you don’t absolutely need to build formulae in GS then you might consider computing data with computed columns in Glide Tables:

  • Wide range of computed columns to choose from.
  • You’ll save on the syncing limits of your plan.
  • Possibly improved performance by computing on the user’s device (Glide computed column) rather than on 3rd party servers (Google Sheets).
2 Likes