What is "COMPUTED_VALUE" in Spreadsheet formula and How does it Work?

[sorin_mihai]

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

[sorin_mihai]

Just to be more explicit, this is what I am referring to:

[Phil]

I can investigate if you provide:

1. Your account id
2. The app name
3. The table name
4. The column name
5. Your permission to do one or more “regenerates” to reproduce the problem.

[sorin_mihai]

That would be great.
Should I send them via Email? to support?

And one question, just to know how things work. When I share the app and table with support, you make a copy and work on it? I ask because, if I send it to you and then I continue working on it and change different things in the app and in the Table as well, does that influence you working on it as well?

Thank you

[sorin_mihai]

In other more direct words, If I crash the app or delete the spreadsheet while you work on it, does that affect you?
(just curious and not sure how careful I should be )

[Phil]

Hi Sorin,

Yes you can submit the problem via the Support link.
I prefer that . When you do that, the bug is tracked in our database.

I normally work directly on your app when debugging.
Copying your app and all of its data takes longer and introduces the possibility that the problem get lost by the copy.

While I am debugging your problem, I ask that you stop making changes to your app because that can affect the problem.

[Phil · Accepted solution]

I have replied to Sorin’s bug directly. For the sake of the community, here is what I learned when investigating this issue.

This is a Google Sheets to Excel export issue.

When you click the “Regenerate” button for a Google Sheets workbook, this is what happens:

1. We call Google Sheets and ask it to “export” the Google Sheet as an Excel .xlsx file.
2. Google Sheets converts your workbook to an Excel .xlsx file. This includes converting the Google Sheets formulas.
3. We read the exported .xlsx file in a third party library called EPPlus that can read .xlsx files.
4. We use EPPlus to extract the formatting, formulas, and data values from each of the worksheet cells in the workbook.
5. We convert the worksheet formulas from A1 to R1C1 format and store those AppSheet formulas in the “Spreadsheet formula” property.

When you add a new row through your AppSheet application, we convert the formula in the “Spreadsheet formula” property from R1C1 to A1 format and store that formula in the appropriate cell of the newly added row.

The problem is occurring because Google Sheets is exporting the formulas in columns J through N of your Google Sheet as:
“__xludf.DUMMYFUNCTION(”"“COMPUTED_VALUE”"")"

Typically, Google Sheets exports formulas that are supported in Google Sheet but not Excel by wrapping the formula in:
“__xludf.DUMMYFUNCTION( )”.

We extract the value contained inside the __xludf.DUMMYFUNCTION( ) and use that as the worksheet formula.
Unfortunately, in your case, Google Sheets is exporting your formulas in columns J through N as “COMPUTED_VALUE”.
As a result, that is the value we see and that we assign to the worksheet formula.

Fixing this problem would require that Google export your Google Sheets formulas in a better form.
You can report this problem to Google, but I am not sure how responsive they will be.

The other alternative is to change the formulas until you find a form of the worksheet formulas that Google Sheets will export to Excel correctly.
You can experiment with this as follows:

1. Open you worksheet in Google Sheets.
2. From the Google Sheets “File” menu select “Download” > “Microsoft Excel”.
3. Try to open the exported .xlsx file in Excel.
4. ​If you try this with your current Google worksheet formulas you will see that Excel will complain that the .xlsx files are invalid and it will ask you if it can fix them.
   When it does this, it will delete the worksheet formulas in the failing columns.

​With luck, you may be able to find some form of the worksheet formulas that Google Sheets will export correctly.
​

[sorin_mihai]

I’ll try and see how I can change the formula … but I did try a lot of things already in achieving something that works … so I don’t really have high hopes. I’ll see what advice I get from some Excel forum and write back here if something will work and solve the issue.

Thank you for the the comprehensive explanation Phil

[Ansu_Halan]

@Phil @Steve Now since appsheet is also a google company, can you take this up internally. This is causing a lot of problems

[Aleksi]

@sorin_mihai Have you tried to use the expression in row #1?

[sorin_mihai]

How do you mean?
You mean in the second row, meaning not in the title row? Because I can’t do that as I have a script rolling in the spreadsheet that archives the rows (copy - pastes them in a different table and then deletes the initial row and that would delete the formula) …

[Aleksi]

No, I mean the row #1. Is the script overwriting values in row #1 as well?

[sorin_mihai]

The formula is now in row #1, that’s why there is this part in the formula:

={“Name of My Column”;
ArrayFormula(
…

which gives the name of the column in row #1 and then places the formula in row#2, without it actually being there.
No, the script doesn’t delete row#1 which holds the titles of the columns.

[Phil]

I suspect the Google Sheets is exporting Sorin’s Google Worksheet formula that contains the “Query” function as “COMPUTED_VALUE” because there is no equivalent in Excel to the “Query” function. In the absence of an equivalent function Google Sheets is exporting it to Excel as a constant string.

You might try converting the Google Sheets formula to R1C1 format and entering that manually into the field’s “Spreadsheet formula” property.

[sorin_mihai]

Well, I’ll have to look into that because in this moment, R1C1 sounds as strange as R2D2 to me :)) but although that sounds plausible, do you think if I nail it, it will remain after a Regenerate Structure? Won’t it go through the same process with the Excel export and import?

[vaiari]

I don't really get the talk. appsheet is a google company and cannot use googlesheet's superior functions because it's converting googlesheet to excel and truncating all and causing headache for what? using googlesheet in appsheet again. Oh my!  Now is just the time I start to think  I shouldn't have started using appsheet at all.

[Jason_Leffler]

while I understand this topic is "solved" I feel like this needs a boost since this is still an issue.  I agree with previous comments that it seems kind of silly that we can't rely on Google Sheet functions here because they don't exist in excel.