- 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.
Using IMPORTRANGE, my syntax looks like:
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]]
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?