0

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?

2
  • From reading [more about IMPORTRANGE][2], it seems solely designed for external data calls. Also, I hadn't realised that there are alternative ways to use auto-fill other than double-clicking / dragging. E.g. selecting a column with one cell reference and pressing CTRL + D (fill down) copies the formula to every cell. (Also copy + pasting cell reference formulas).
    – ric_cde
    Nov 17, 2022 at 12:44
  • Welcome to Web Applications Stack Exchange. There are many ways to bulk copy cells within a spreadsheet file, including { array expressions } and the arrayformula() function. The importrange() function should only be used when you are copying values from one spreadsheet file to another. Nov 17, 2022 at 14:02

2 Answers 2

1

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

={ Sheet1!A1:Z }
-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)

1
  • In the general case, query() is not suitable for bulk copying values. It will return empty values for any cells that are not of the same type as the majority of the values in their column, and in many cases will convert the first row or the first several rows to text when the third parameter is omitted. Nov 17, 2022 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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