How to combine arrays of data into 1 sheet

In this post, we’re going to look at how we can easily combine arrays of data on separate sheets in a Google Sheet and add them on one single sheet. We’re going to use the relatively new spread operator (…) to do this.

Situation

In this example, we have a set of data on 3 separate sheets, Sheet1, Sheet2, and Sheet3 and we need to combine them all on the sheet called “Target Sheet”.

Combine arrays of data code

Get the spreadsheet, sheets and set up a blank array

1. function combineArrays() {
2. 
3.   const ss = SpreadsheetApp.getActiveSpreadsheet();
4.   const arrayData = [];
5.   const sheets = ss.getSheets();

L1: Open the function.

L3: Get the active spreadsheet and store it in the variable ss.

L4: Set up an empty array called arrayData, which is where we’re going to store the 3 sets of data.

L5: Get all the sheets in the Google Sheet and store them in the variable sheets.

Loop through the sheet, get the data, push it into an array

7.   for (i = 0; i < 3; i++) {
8.     const sh = sheets[i];
9.     const sourceValues = sh.getDataRange().getValues();
10.    arrayData.push(...sourceValues);
11.    Logger.log(arrayData);
12.  }

L7: We only want to get the data in the first 3 sheets. So we can set up a for loop to get the data on those 3 sheets.

L8: In each iteration of the loop, we get the current sheet from the sheets array. So for example, in the first iteration, we’ll get the first sheet, which is sheets[0], then sheets[1] and so on.

L9: On the current sheet, sh, we get all the values on that sheet and store them in the variable sourceValues.

L10: We then use the spread operator (…) to get the rows but without them being wrapped up in another array. Then we push it into the arrayData array.

Without the spread operator, the data in arrayData would look like this:

So, each set of 3 rows would be in its own array, which would mean we couldn’t easily add all the data to the Target Sheet in one go.

When we add the spread operator, we have 9 arrays to match the 9 rows of data we have, wrapped up in one single array.

L11: This is just a log to see what’s happening in the arrayData array in each iteration.

L12: Close the for loop.

Add all the rows to Target Sheet

13.  ss.getSheetByName("Target Sheet").getRange(1, 1, arrayData.length, arrayData[0].length).setValues(arrayData);
14. }

L13: We then add all the data stored in arrayData to the “Target Sheet” in one go, rather than adding the data sheet by sheet which is slower.

L14: Close the function.

Running the script

Now run the script and you will see that on the Target Sheet it has added the 3 sets of data from the individual sheets. Note, this script doesn’t copy the colours, they are there just to make the 3 sets of data clear.

It’s a nice example of one of the uses of the spread operator.

You can make a copy of the above example in this file.

For more tutorials on Apps Script go to the Apps Script page.

Thanks to Alessandro, one of my book readers, for the question which inspired this post. 🙂

a