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

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

Solved Solved
1 15 14.1K
1 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.
โ€‹

View solution in original post

15 REPLIES 15

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

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.

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

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 )

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.

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.
โ€‹

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

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

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

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) โ€ฆ

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

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.

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.

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?

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.

Top Labels in this Space