Hello,
This question is mostly for those who use Google Spreadsheet for their data, I think.
For reasons of transferring computing time from appsheet to the google spreadsheet, Iโve come to write some formulas in the spreadsheet that go on these lines:
- the formulas are written on the very first row of the spreadsheet and look like this:
={โName of my Columnโ;ArrayFormula(my formula)}
Most work just fine and spreadsheet recognizes these formulas and the Column Name just fine.
However, there are some that donโt seem to be recognized by appsheet, but in a very strange โฆ half way โฆ way.
So the only thing that I find special about them is that they contain a query() as far as I can tell.
Appsheet recognizes the Column Name just fine.
However, in the โAuto Computeโ field of the column, on the โSpreadsheet formulaโ line it generates this " โCOMPUTED_VALUEโ " text. And when I add data through the App, in the spreadsheet it actually fills in the cell with the actual words โCOMPUTED_VALUEโโฆand obviously, thatโs not good as it interrupts the ArrayFormula.
Now what is even stranger, is that if I delete that value from the โSpreadsheet formulaโ line in Appsheet and save, it doesnโt fill it back in and so when I add data through the App now, the ArrayFormula works fine.
BUT, thatโs not a long term solution because if I Regenerate the Structure of the Table, then Appsheet again fills in that field with โCOMPUTED_VALUEโ and I would have to remember to always go back there and delete it before adding any new data to that table through the App.
So, any Ideas about what to do here?
Or whatโs the logic of it?
- how come it doesnโt recognize the formula?
Just in case, here is the formula itself:
={โName of My Columnโ;
ArrayFormula(
if(A2:A="","",
iferror(
vlookup(A2:A,
query({Times!A2:A,Times!C2:C,Times!J2:J,Times!Q2:Q}, "select Col2, sum(Col3) where Col1 <> โโ AND Col4 = โCNCโ group by Col2 ", 0),2,false),
โ0โ)))
}
- how come it forces this text โCOMPUTED_VALUEโ into the spreadsheet?
Thank you,
Sorin
LinkedIn
Twitter
