Formula in Google Sheet or App

[Poovich]

I am trying to make an Inventory management app, which has in-bound and out-bound products features, so products’ cost and quantity value will change when there are new inventory coming in (plan to use an average for products’ cost)

However, I see some examples from appsheet website that directly put formulas into the google sheet.
I have read https://help.appsheet.com/en/articles/961393-spreadsheet-formulas, but still don’t get it about the differences between using google sheet formula and app formula (I am quite new to the appsheet)

What are the differences between app formulas and google sheet formula?

What are the pros and cons to put formulas in the google sheet vs. app formula?

If you have useful resources or examples, could you please share or give some suggestions?

[MultiTech · Accepted solution]

Hey @Poovich.

There are some big differences between sheet formulas and app formulas, I’ll try and highlight things below.

Google Sheet Formulas

• Computed by a server

  • so even really heavy formulas are processed relatively fast

• all g-sheet formulas available (including custom formulas you might create with apps script)

• Only calculated when new data hits the sheet

  • meaning you have to wait for the sheet to calculate the values, then send that data back to your app before you get the new values
    This is a big issue for a lot of apps, and can really cause a slow down during your sync times

App Formulas

• Computed by the end-user device

  • that means if someone’s using an old POS with no memory left, it will run slow and hard; if I’m running on a brand new Apple device, things will be fast and responsive; if I’m on a PC, things will work a little faster, but performance is still largely based on the device’s resources

• limited to only those supported by AppSheet

• IF INSIDE A PHYSICAL COLUMN:

  • they’re calculated anytime you modify a record;
  • For instance: if I have a timesheet table, with a duration formula to automatically calculate the total time of the timesheet, that formula only updates when I make a modification to the timesheet record. Meaning, to get the value to update I need to either open the form and save it, or somehow change some other value in the row (that will cause the duration formula to recal).

• IF INSIDE A VIRTUAL COLUMN:

  • they’re calculated anytime new data is made available - on the fly (if you will).

---

My general rule of thumb is this:
Treat your Google sheet like it’s a database table; it’s just a place to store data.

[Alan_Thorp]

As an alternative to one or the other I offer the idea of putting the formula/calculation into Apps Script and running a trigger to update the formula “on change.” The script should be written to produce a text result back into the sheet so that it will not need to be calculated on sync.

Most of the method can be seen in this post:
[How to Integrate Google Apps Script & Trigger with an AppSheet App]

Just as a note, you probably want to be looking for the row that is edited rather than the last row. I made this mistake, but quickly realised my error.