Copy data from one file to another for rows with a same index value

1
votes

I tried to look for previous answers but none of the scripts I found can suit my needs.

I have two Google spreadsheets:

The source file file2 has 8 columns. The first column is offernumber: http://i.stack.imgur.com/BnV6S.png

The other spreadsheet, file1, has a corresponding ordernumber column. http://i.stack.imgur.com/QYqiE.png

Where there is a match on ordernumber to offernumber, I need an automatic way to copy the data in file 2 into the corresponding row in file 1.

I managed to copy values manually when I enter a query into the spreadsheet, such as:

=QUERY( ImportRange( "keyspreadsheet" ;"Sheet1!A2:H1000"),
        "select Col2,Col3,Col4,Col5,Col6,Col7,Col8 where Col1='6/2012' ";0)

But am not able to figure out how to script this. Any help would be appreciated!

1
google-apps-scriptgoogle-sheets

1 Answers

0
votes

You can do this entirely with spreadsheet functions, no need for a script. (Although I think a script would handle very large spreadsheets more efficiently.)

In file1, put this function in cell AG2, replace both instances of "keyspreadsheet" with the ID of file2, then copy to the rest of the rows in column AG:

=index(ImportRange( ʺkeyspreadsheetʺ ;ʺSheet1!$B$2:$Hʺ),match(AF2,ImportRange( ʺkeyspreadsheetʺ ;ʺSheet1!$A$2:$Aʺ),0))

This finds the row in File2 that has a value in column A matching the ordernumber in File1 Column AF, and copies the values from the entire row, starting with column B (thus skipping 'Offer N.'). The result looks like this:

File1 with imported matching rows