What syntax does IMPORTRANGE require to import ranges between sheets within the same spreadsheet?

Question

Scenario:

• I have a Google Sheets spreadsheet which contains two sheets: Sheet1, Sheet2
• Sheet1 has lots of data. Sheet2 is empty.

I want to:

• Import all of the data from Sheet1 into Sheet2.
• Since Data in Sheet 1 is updating continuously, I need to mirror it and reflect new rows or columns.
• NB: I could use cell-by-cell references (e.g. =Sheet1:A1, =Sheet1:A2, etc. plugged into each cell of Sheet2, populating them using auto-fill). However, row/column counts may increase, so I would need to fix a value much higher than I need (e.g. 2000 rows, 15 columns). Also, Google Sheets autofill works poorly on sheets with many rows - double-click doesn't work. <-- I hadn't realised there is a Fill Down shortcut (CTRL + D) for copying cell references to all cells within a column.

URL value

Using IMPORTRANGE, my syntax looks like:

IMPORTRANGE(URL, "Sheet1!A:Z")

What value do I need to include as the URL to reference the first sheet internally? I've attempted the following with no luck:

=IMPORTRANGE("Sheet1", "Sheet1!A:Z")
=IMPORTRANGE(Sheet1, "Sheet1!A:Z")
=IMPORTRANGE("", "Sheet1!A:Z")

I can include the full URL for the Google Sheet spreadsheet itself, or the ID slug of the document, and this works once I Allow Access via the "You need to connect these sheets" prompt.

=IMPORTRANGE("5OsdlkE-LdErSdfJSJlkLSKaklsHZ7tJWljQ9439023kELkEw", "Sheet1!A:Z")

// NB: fake slug, so this will not work if you insert it into your own sheet

[![Google Sheets Allow Access prompt for IMPORTRANGE][1]][1]

However, this seems like it's making additional unnecessary API calls and is quite slow.

Is there another value which I can include as the URL argument for IMPORTRANGE that will refer to the current sheet without requiring access and/or will instantly update? Or an alternative way to copy between sheets that would suit my needs?

Answer 1

To bulk copy values within a spreadsheet file, use an { array expression }, like this:

={ Sheet1!A1:Z }

Answer 2

The solution seems to be using the QUERY() function rather than IMPORTRANGE (which is for external data access:

=QUERY({Sheet1!A:F})

When placed in the top left cell of Sheet2, this formula imports all rows from Sheet1 (columns A:F inclusive only)