1

I have a multi sheet workbook in Google Sheets. I need to export a compilation of tables from this workbook to CSV. I therefore have added a new sheet where I use IMPORTRANGE to combine values from different sheets to one table, which I then want to export. The table has five headers:

Header 1 Header 2 Header 3 Header 4 Header 5
IMPORTRANGE IMPORTRANGE

There are two uses of IMPORTRANGE

The result text file is exactly 11 bytes, and contains one line:

#REF!,#REF!

I guess the reason there are two #REF! (and not 5, one for each column) is because I use IMPORTRANGE twice.

Currently I have to copy-paste the whole table into another, temporary, sheet, as values, export this new sheet to CSV and finally delete the temporary sheet, a PITA.

How can I export a sheet where I use IMPORTRANGE to CSV in Google Sheet?

1
  • 1
    You don't need to use importrange in the same spreadsheet. If you want help with a Sheets formula you need to include sample data in the question as well as the desired result as well as relevant formulas if they are being discussed. What should be in the CSV? What is in the sheet prior to export?
    – Blindspots
    Dec 10, 2023 at 3:05

1 Answer 1

2

The issue is caused by the use of importrange() to copy data from one tab to another within a spreadsheet file, which is generally discouraged.

To copy data from one tab to another within the same spreadsheet, file, use { array expressions } instead of importrange(). If your current formula is

=importrange("...spreadsheet ID...", "Sheet1!A2:D")

use this array expression instead:

={ Sheet1!A2:D }
1
  • Thank you, interesting. Note that you have to add apostrophes around the sheet name if it contains spaces.
    – d-b
    Dec 10, 2023 at 14:13

Not the answer you're looking for? Browse other questions tagged or ask your own question.