Google sheet Formula Values are not updated

AppSheet AppSheet Q&A
, , ,
1

Hi,

I am using google Sheets as my database. I am using the Googlefinance function in my sheet to calculate the price, % change, and historical pricing of stocks the values in the table are dynamic and updated daily, but the result in the AppSheet is not updated properly.

The performance % column is not updated, but the price and % change is updated. All three columns uses GOOGLEFINANCE function in the sheet, the only difference is that the formula in the % performance column is more complicated than the other two.

rajtrader_0-1673944477309.png
rajtrader_0-1673944477309.png490Ă—780 17.7 KB

I found this issue with google sheet formula https://support.google.com/appsheet/answer/10104397?hl=en

rajtrader_1-1673945048612.png
rajtrader_1-1673945048612.png849Ă—414 17.4 KB

If GOOGLEFINANCE formula is not compatible then it should not work at all. But it seems it work fine in many cases.

2

Try revising the formula in the worksheet to use R1C1 notation–that’s interpreted by AppSheet more reliably than A1 notation.

3

Still not showing values in AppSheet. Though it makes the formula difficult to read, I would not mind if it works reliably. Is it because of using the INDIRECT function? I don’t know how to use R1C1 notation without INDIRECT function.

rajtrader_0-1673965781648.png
rajtrader_0-1673965781648.png1096Ă—188 28.8 KB

4

I don’t know why it would work in some scenarios but not others–especially since, as you noted, it’s on the list of unsupported functions.

If you haven’t already, compare the column’s Spreadsheet formula property populated by AppSheet to the source worksheet’s formula. That could suggest a next troubleshooting step.

5

It’s better to set up any spreadsheet formulas as ARRAYFORMULA()s that only live on the header row.

6

That’s an interesting tip!

7

Thanks, Marc.

This trick seems to be working for now. Though, I am not sure this answer will absolutely be a solution for everyone.