When trying to regenerate a particular table ...

references
(Tom Graham) #1

When trying to regenerate a particular table (after adding 1 new column), we keep getting this error:

Unable to read the spreadsheet for ‘NewApp-80261-18-12-12:1.000052:Shipments’ due to spreadsheet reader error: Name ‘Z_7B74E57F_9147_4535_AA3F_CCC1929170E7_.wvu.FilterData’ is defined in the worksheet more than once. First as ‘‘Import Orders’!$A$1:$W$976’ and second as ‘‘Import Orders’!$A$1:$R$976’.MORE INFO

Is there anyway to narrow down which columns it is referring to? We have multiple fields that reference the ‘Import Orders’ sheet, but none appear to have formulas containing the range “$A$1:$R$976”.

Happy to share Gsheet with someone to check on the formulas if that helps.

(Tom Graham) #2

add name: NewApp-80261-18-12-12

(Steven Coile) #3

Remove all filters in the spreadsheet.

(Philip Garrett) #4

Other customers are reporting a similar problem.

It appears that Google released a bug on Jan 11, 2019 in the way it exports Google Sheets in Excel .xlsx format.

When Google exports a Google Sheet in Excel format, it creates a “workbook.xml” file containing the entire Google workbook. One section in the “workbook.xml” file contains a list of all of the Defined Names specified in any of the worksheets of the workbook. Excel expects every Defined Name to have a unique name value. Unfortunately, it appears that Google is exporting the Defined Names incorrectly. As a result duplicate names are appearing the section of the exported “workbook.xml” file.

You can verify this as follows:

  1. Open the workbook in Google Sheet.

  2. On the Google Sheets “File” menu select “Download as” and then “Microsoft Excel (.xlsx)”. This will export the Google workbook as an Excel document.

  3. At the bottom left of the browser page you will see the exported .xlsx file.

  4. Click the ^ and open the file in Excel.

  5. Excel will report an error when it attempts to open the exported .xlsx file.

The .xlsx file is simply a compressed Zip file containing a folder made up of XML files that represent the workbook, each individual worksheets, and all other workbook related information.

You can examine the Excel file by: 1. Renaming the file extension from .xlsx to .zip 2. Right clicking the file and selecting Extract All. 3. Open the resulting folder and navigate to the “workbook.xml” file. 4. Open the “workbook.xml” file. 4. Look for the XML element and you will see duplicate values.

I am not certain what is causing these duplicate Defined Name values. I presume that the Google export problem depends on some feature being used in the failing workbooks, but I am not sure what the feature is. The only thing I can suggest is trying to make changes to your worksheet and see if that eliminates the problem.

If you figure out what is causing the duplicate name, please share it to help others.

You can test whether you have eliminated the problem by exporting the updated workbook and opening the resulting .xlsx file in Excel. Once Excel can open the exported file, AppSheet should be able to do so also.

The problem has been reported to Google. See

accounts.google.com - Sign in - Google Accounts

Sign in - Google Accounts accounts.google.com

(Philip Garrett) #5

The first two customers who reported this problem to us got around it by creating a new Google Sheet document and manually copying the data from the old to the new document. They then linked the new Google Sheet document to their existing app.

Admittedly this is painful.

(Tom Graham) #6

Thanks for the information. Yes, I get the error when trying to download the xlsx version.

I tried to open as XML file, but renaming the file the zip did not allow me to then extract any file… seems that I am not actually changing the extension, as I also cannot see the xlsx… windows thing I guess… but I would really like to try to investigate however possible.

Is it possible to narrower down what the 2 conflicting fields /formulas are if you look at the XML code?

Its a massive task to move this to a new spreadsheet, as we have many other scripts that use the sheet ID… we would have to write a script, to find all the place that are referencing that ID and update it to the new one…

We also have another app, where we have the same issue and cannot add a new field, or we trigger this issue. That one is even more intense and more connections, I simply gave up on ever adding columns to that sheet again.

So, hoping we can try and get to the bottom of this and find a way to resolve it.

(Philip Garrett) #7

@Tom_Graham

Please try it now. I just deployed a change on our side that attempts to circumvent the Google Sheets bug.

Please let me know as soon as possible whether it fixes the problem

(Mike Rundell) #8

I am getting a similar issue when I add a column in a sheet that is causing my app formulas to come up with an error when I regenerate even though they were initially correct

(Philip Garrett) #9

@Mike_Rundell1

The problem Tom and others were seeing always yielded a message in the form:

Name ‘Z_094FF27D_C6EC_4143_B33D_37910F2C746A_.wvu.FilterData’ is defined in the worksheet more than once.

The actual name varied, but the phrase “wvu.FilterData’ is defined in the worksheet more than once” was always present.

Are you seeing that message?

If not, you are probably seeing a different problem. Can you describe your problem more fully?

(Mike Rundell) #10

@Philip_Garrett_Appsh no I am not seeing that message

(Mike Rundell) #11

@Philip_Garrett_Appsh I had problem when I added a new column to a spreadsheet in a table and then regenerated the table. What happened was that I lost the original Number settings that were set to slider for some of the columns that also had formulas applied to them and it reverted back to my initial setting of an enum and instead of being number it defaulted to text which then rendered all of my formulas invalid. It looked as if when the column was added and the table regenerated it lost the original changes made to alter from enum to sliders – very strange. I deleted the formulas, changed all the enums back to sliders and then re-added the formulas and it now works – very strange indeed !