1

I'm trying to merge the information stored in multiple tabs in one master sheet such that it'll look like "Final" in this sample sheet. I found a code online that does something similar but merge the information row-wise, which is not ideal in my case. The code looks like this:

function merge() {
  const ss = SpreadsheetApp.getActive();
  const arr = ss
    .getSheets()
    .filter(s => !s.getName().includes('Master'))//exclude Master sheet
    .flatMap(s => s.getDataRange().getValues());//map sheet to values and flatten it
  ss.getSheetByName('Master')
    .getRange(1, 1, arr.length, arr[0].length)
    .setValues(arr);
}

Please let me know if you have any suggestions. Thanks in advance.

Maria
  • 13
  • 3

1 Answers1

1

Issue:

The values of each sheet are getting appended as successive rows in the target sheet. That's because the outer array corresponds to rows.

You should add the values of each sheet row to each inner array. That would correspond to columns.

Solution:

Transpose the array to make the columns correspond to the outer array, and then transpose back.

Also, since the first column in all sheets is the same, you only want to copy it for the first one, so you should check the index when using flatMap, and not retrieve the full range via getDataRange in these cases (use getRange instead).

Code sample:

function merge() {
  const ss = SpreadsheetApp.getActive();
  const excludedSheetNames = ['Master', 'Final']; // Add excluded sheet names
  let arr = ss
    .getSheets()
    .filter(s => !excludedSheetNames.includes(s.getName()); //exclude several sheets
    .flatMap((s, i) => {
      let values;
      if (i === 0) values = s.getDataRange().getValues();
      else values = s.getRange(1,2,s.getLastRow(),s.getLastColumn()-1).getValues(); // Ignore first column for non-first sheet
      values = values[0].map((_, colIndex) => values.map(row => row[colIndex])); // Transpose 2D array
      return values;
    });
  arr = arr[0].map((_, colIndex) => arr.map(row => row[colIndex])); // Transpose back
  ss.getSheetByName('Master')
    .getRange(1, 1, arr.length, arr[0].length)
    .setValues(arr);
}
Iamblichus
  • 18,540
  • 2
  • 11
  • 27
  • Thanks for the detailed solution. Much appreciated! This code is not working when I'm using 'Master' on line 5 but is working when I'm using final. Error- Exception: The number of rows in the range must be at least 1. Also, I want to exclude a couple of sheets from the overall merge like excluding both the final and the master sheet. Any suggestions? Thanks in advance! – Maria Jul 22 '21 at 18:25
  • @MariaMasood That probably means the `Master` sheet is empty, so when trying to retrieve the range at `s.getRange(1,2,s.getLastRow(),s.getLastColumn()-1)` if fails, because `getLastRow()` returns 0. In any case, I've modified the script to that you can exclude multiple sheets (`Master` and `Final` in this case). First the array of excluded sheets is defined ` const excludedSheetNames = ['Master', 'Final'];`, and then these sheets are filtered out `.filter(s => !excludedSheetNames.includes(s.getName());`. I hope this is useful to you! – Iamblichus Jul 23 '21 at 07:32