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?
arrayformula()
function. Theimportrange()
function should only be used when you are copying values from one spreadsheet file to another.