Downloading File with importrange function failing

问题:

I've been saving Google Sheets to Excel without any problems for a while. These sheets have always successfully saved and opened in Excel with the importrange function. However, recently it hasn't been successfully saving correctly.

It used to just have the static value (e.g, 40). There used to be an IFERROR in the first cell in the header row but now it exists in every single cell.

E.g, each cell would have something like this:

=IFERROR(__xludf.DUMMYFUNCTION(importrange(blahblah)),"40"). 

DUMMYFUNCTION throws an error and "40" is returned as a result. but "40" is a string, not an integer which messes up all my formulas.

I also know this isn't an Excel issue because OpenOffice is doing the same thing with the file.

I'm pretty sure this would be a bug because why would it be working for months and then suddenly stop working?

What should I do?

回答1:

I'm thinking it's a bug too.

Workarounds

On Excel

• Copy and paste as values only the ranges with IFERROR(__xludf.DUMMYFUNCTION(..., then use Excel's UI tools to convert numbers shown as text to numbers.
• Selectively remove quotes on the IFERROR second argument of the cells causing problems
• Remove =IFERROR(__xludf.DUMMYFUNCTION(),"value") except value (we could use Excel's built-in FIND & REPLACE for this)

On Google Sheets

• Use Copy > Paste as values only on the range areas having formulas with non-compatible functions like IMPORTRANGE, QUERY, FILTER, etc.
• If you only need the values, download it as CSV instead of XLSX

IMPORTANT

In order to help to prioritize this issue, send feedback to Google. To do this open a Google Sheets spreadsheet, click on Help > Report a problem, then fill the feedback form and submit it.

Related stuff

I posted 5 small articles about this in Spanish. You could find them listed on https://www.rubenrivera.mx/p/descargar-hcg-excel.html.

回答2:

Until this bug is fixed, a workaround is to put a data validation (Data > Data Validation) on the imported data (Any kind of data validation will do).