Unsupported Formulas in Appsheet

[Jon_Melo]

Hello fellow Appsheet users - first time that I’m posting a question here but have been working in Appsheet regularly since Feb 2020. I hit a wall when trying to integrate MMULT() as a “Spreadsheet Formula” into one of my columns.

Has anyone found a workaround on how to apply Excel formulas that are unsupported by Appsheet?

The interesting thing is that the formula shows up in the “Spreadsheet Formula” field of my column but it won’t apply it when new rows are added. Rather, it causes the app to error out if I leave it there.

I’ve resorted to copying down (Ctrl-D) the formula in Excel manually for new rows but this isn’t ideal.

Any native or third party workaround suggestions are welcome. I already checked out integromat but their support team told me that copying down a formula isn’t something they support.

[GreenFlux]

What is it that you’re trying to accomplish with MMULT()?

There may be a different approach in AppSheet involving ‘perform an action on a set of rows’, or creating a parent table to store summary data.

Try rephrasing the question in terms of your table and column names.

What do you want to calculate? What tables/columns store the inputs to the calculation? What real-world transaction are you tracking?

---

I would avoid sheet formulas until you have exhausted all native AppSheet options.

If that doesn’t work out though, look into R1C1 notation.

https://help.appsheet.com/en/articles/3326638-mismatched-spreadsheet-formulas
https://bettersolutions.com/excel/formulas/cell-references-a1-r1c1-notation.htm

[LongingToProgra]

So when you are saying that the excel formula is unsupported by Appsheet, I’m assuming that you mean you have put it in Spreadsheet Formula section of the column and it fails when you do that? Because I have done a ton of spreadsheet formulas that show up here and I didn’t think Appsheet cared what the formula was.

[GreenFlux]

help.appsheet.com

Spreadsheet Formulas

How formulas you write in your spreadsheet impact your app creation process

help.appsheet.com

Mismatched Spreadsheet Formulas

Diagnosing Mismatched Formulas in a Column

https://bettersolutions.com/excel/formulas/cell-references-a1-r1c1-notation.htm

---

What formula are you trying to use?

Chances are there is an AppSheet equivalent. Sheet formulas should be avoided if possible.

They slow down sync time, and their values don’t update until data is written back to the sheet. But if you use a Virtual Column, the data updates instantly in the UI when formulas inputs change.

[Jon_Melo · Accepted solution]

Thank you all for the helpful suggestions! I came to the conclusion that Appsheet can’t support Array Formulas (MMULT(), SUMPRODUCT(), etc). I think this is by design. I did accomplish what I needed by breaking down the formula into its parts and adding a bunch of hidden columns for calculation purposes. Very tedious but it works.

Happy to help anyone who runs into a similar issue (jmelo@payhouse.co).

[dbaum]

I found my way here bc I had a spreadsheet formula that AppSheet wasn't propagating to new rows. The Google Sheets formula uses the DCOUNTA function, and AppSheet transformed that to an array formula in the AppSheet column's spreadsheet formula property. Good to know that anything AppSheet considers an array formula won't work.

I was able to get everything to work by fully using R1C1 notation directly within the Google Sheets formula. After I made that revision and regenerated columns in AppSheet, AppSheet no longer transformed it to an array formula and it now propagates to new rows added via my AppSheet app.