Google Sheet: How to use arrayformula to copy data from one sheet to another?

user_2302661 0
Viewed 1291

In a Google spreadsheet, I want to sync A2:G500 in sheet1 to sheet2, I've been aware of the following two methods:

  1. use IMPORTRANGE: put the following formula in A1 of sheet2:

    =IMPORTRANGE("spreadsheet_url",sheet1!A2:G500)

It works but it feels like I am overdoing it, besides there seem to be a performance issue

  1. In A2 of sheet2, put formula =sheet1!A2, then drag the formula to G500 in sheet2. This one is intuitive and simple to do. However, it doesn't work if sheet1 is a form response sheet - when new response is added, sheet2 won't automatically get it.

For learning purpose, I'm wondering if there is a way to do this using Arrayformula. Besides, I want to find a way to make this sync more care-free, meaning if there are indefinite rows of data I won't have to go back to this sheet every now and then and change the formula or manually drag the formula. Is this possible? And is Arrayformula the right way to go for this purpose?

google-sheets-formula google-sheets
4 Answers

I would recommend an { array expression }, like this:

={ Sheet1!A2:G }

This is more or less the same as

=arrayformula(Sheet1!A2:G)

...but I prefer the {} syntax because it allows you to specify non-adjacent columns. For example, you can skip columns D and F like this:

={ Sheet1!A2:C, Sheet1!E2:E, Sheet1!G2:G }

In spreadsheets where the locale uses the comma as decimal mark instead of the period, use a backslash \ instead of comma as horizontal separator.

To skip rows, use the semicolon ; as vertical separator. For example, you can skip rows 2:9 like this:

={ Sheet1!A1:G1; Sheet1!A10:G }

The open-ended range reference A10:G means "columns A to G starting in row 2 and extending all the way to the bottom of the sheet."

You can also leave out the row number to get an open-ended range reference like A:G which means "columns A to G from the very top to the bottom of the sheet." This reference will behave the same as A1:G in almost all situations. I have made it a habit to always include the start row in the reference because that way the formula will automatically adjust in the event a row is inserted above row 1.

When the source sheet is a form responses sheet, another tactic is needed. Form responses are always inserted in newly created rows that cannot be referenced directly in advance.

To avoid the range reference from adjusting when you dynamically copy form responses to another sheet, start the copy from row 1, like this:

={ 'Form Responses 1'!A1:A }

Alternatively, use an array formula, like this:

=arrayformula( 
  if( 
    row('Form Responses 1'!A1:A) = 1, 
    "Enter column header here", 
    'Form Responses 1'!A1:A 
  ) 
)

An even better way to deal with form responses is to aggregate the data directly to whatever reports you need with the query() function.

user_130451930

It's either:

  1. ArrayFormula(Sheet1!A2:G500) for the 499 lines, or
  2. ArrayFormula(Sheet!A2:G) if you wanto sync everything from line 2 down
user_132292110 
=ARRAYFORMULA(Sheet1!A:G)

Does this not work?

user_138243340

try in row 1:

={""; INDEX(sheet1!A2:A)}

this will solve your form issues when you use it in 1st row. if you already have something in your row 1 you can add it into double quotes like this:

={"header"; INDEX(sheet1!A2:A)}

in case of multiple columns its like this:

={"","","","","","",""; INDEX(sheet1!A2:G)}
user_56326290
Related