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

AppSheet AppSheet Q&A
7

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.
​

2 Likes