Apologies if this is obvious, I am new to Excel programming.
I have a table of Task where a user can add, edit an remove task (one task per row). On add (from within Glide) I need the row to contain certain formulas that refer to other cells on the same row. I am not clear how I go about doing this?
An example of a formula I need to input is =IF(O2=0, 0, POWER(2,O2-1))
which is attempting to calculate a exponential back off based on how many times O2 has been deferred or nah’d. I don’t believe that the nature of the formula is relevant here, I am just providing it as a example.
Thought 1:
There is something called an ARRAYFORMULA that you can use. You can also just drag the formula all the way down using the bottom right corner of a cell. I notice that glide then assigns each row a row-id as the row now has content. Unfortunately this means that glide will then assign new task to the next fully empty row. This makes sense, but of course the next empty row is the one that does not have the formula.
Thought 2:
I thought maybe I could simply input the formula from within glide. So I tried adding a text input component and then inputting a formula directly as a default value. I then set the display to only occur when row-id is equal to NOT_A_ROW_ID. This means it isn’t visible. However, after adding a task (row) using glide, it appears that glide quotes the equal sign to make it a literal value, so I don’t think that can work. I do notice that I can use the SPECIAL VALUES input component to input a Current Date, but I don’t see any SPECIAL_VALUES to input a formula?
Those are the solutions I have tried so far. I am curious if I am going about this the right way?
Arrayformula’s are best, but google will interpret a row that has a formula as a filled row and look for the next empty row to add data. That’s why dragging a formula never works. When using an arrayformula, it’s best to put in an IF statement to only use the formula when another column is populated, else return a blank value. Otherwise it will put something in the cell and glide will interpret it as a filled row. Also it’s best to delete all empty rows because the nature of arrayformulas fills every row in the sheet, regardless if you see anything in the cell or now. Otherwise google creates a new row and appends to the bottom of the sheet.
Here is a tutorial on using arrayformulas.
Glide does not interpret sheet formulas and will just treat it like text. However, keep in mind that depending on what you are trying to achieve, you can do a lot of functions within glide using math, rollup, if/then, etc. columns in the glide data editor.
I read through your article in the entirety. Everything was new to me! I think I may have used every section before 3.3 .
With the help of the article I was able to get the following to work in my header -> ={"NahOffset";ARRAYFORMULA(IF(O2:O=0, "", POWER(2,O2:O-1)))}
(Note: O2 is the column that contains the number of times a task has been “Nahed”, it is added to the current date to next ask you about this task at some increasingly exponential point in the future)
No that I care too much, but what is the efficiency of this code? I am wondering if excel is smart enough to know that I am only referring to things in this formula within the same row? Does that mean that this is only constant cost assuming I update a single row? Or is it doing what it literally says and “recomputing” the entire range every time?
Anyway, really thanks for your help, gave me plenty to read and think about.
Thank you. You are right based on my reading; I am trying to stay entirely within the excel sheet as much as possible. Partially this is due to the fact that excel is new to me and so I would like to understand it better.
I’m not saying that using the glide “virtual” columns is cheating, just that I want to know when I have to reach for them rather than reaching for them whenever I can.
BTW: I really appreciated your article linked above!
Hard to say on the efficiency. It may depend on the formula used. On one hand, I feel like google will only recalculate rows that have been “touched”. I only say this because I used to have a sheet with very heavy queries and calcs that would take several minutes to run. In some cases it was quick and sometimes it was not depending on what I was doing with it. This was also on a sheet that is dynamically built from a query, so depending on the change, the whole sheet could reload and recalc. On the other hand, some people do geocoding of addresses within their sheet and if they aren’t careful, they can exceed their daily google geocoding limit for the day really quick…even if the address has already been geocoded to lat/long coordinates. It seems like it still processes some formulas every time.
To piggy-back on your response to @ThinhDinh, there is a very large advantage to doing as many calculation within glide as possible, and that is speed. If you are purely relying on all formulas within the sheet, then you will also have to account for the seconds to minutes lag between when glide and google sheets sync up. Whenever you make a data change in the app, it will send it to the google sheet. If your calcs are quick enough, then usually glide will be able to grab them. If they take too long, then you are at the mercy of google sending the updated calcs to glide. Google will usually do the first update within seconds, but subsequent updates can be up to a few minutes before they are sent to glide and displayed in the app. Glide does have the option of a background refresh on pro apps that will query the google sheet every few minutes. This is usually beneficial if you have google formulas that use time based formulas, like Now() or Today(). Also it’s beneficial if you are using ImportRange or ImportXML for example. Otherwise, things like that won’t update automatically if your sheet is closed. So in the end, the more you can rely on Glide to perform calculations with glide specific columns, then better. I personally still have formulas that run in my sheet if I can’t reproduce them in glide. Other than that, I try to treat the google sheet strictly as a database when I can and let Glide do the heavy lifting.
Still doesn’t hurt though to get a feel for what you can do in google sheets.
