ISSUE:
Thanks to the wonderful assistance of @Tanaike, I was able to modify the script below to copy all non-empty rows from one source sheet and put these rows into a target sheet. Now I want to be able to copy all non-empty rows from multiple source sheets (instead of just from one source sheet) and put these rows into a single sheet target.

To be more specific: I have 20 source sheets and 1 single target sheet that I call “Mastersheet”. I want to copy all non-empty rows from all the 20 sheets and put them into 1 single sheet (so, into the “Mastersheet”).

QUESTION:
How can I copy all non-empty rows from 20 source sheets and put them into one single sheet (so, into the "Mastersheet")?

Thank you so much in advance for your help :)

function copyAndAddNewEntries() {
  var sourceSheet = SpreadsheetApp.openById('SOURCE_SHEET_ID').getSheetByName('SOURCE_SHEET_NAME');
  var sourceSheetrange = sourceSheet.getDataRange();
  var [, ...sourceSheetData] = sourceSheetrange.getValues();
  sourceSheetData = sourceSheetData.filter(e => e.join("") != "");
  var targetSheet = SpreadsheetApp.openById('TARGET_SHEET_ID').getSheetByName('TARGET_SHEET_NAME');
  targetSheet.getRange(targetSheet.getLastRow()   1, 1, sourceSheetData.length, sourceSheetData[0].length).setValues(sourceSheetData);
}

CodePudding user response：

I believe your goal is as follows.

• You want to retrieve the values from 20 sheets in one Google Spreadsheet. In this case, you want to retrieve the values by removing the empty rows.
• You want to put the retrieved values to the target sheet which is another Spreadsheet.

In this case, how about the following modified script?

Modified script:

function copyAndAddNewEntries() {
  var sheetNames = ["Sheet1", "Sheet2",,,]; // Please set the sheet names you want to use.

  var sourceSs = SpreadsheetApp.openById('SOURCE_SHEET_ID');
  var {values, max} = sourceSs.getSheets().reduce((o, s) => {
    if (sheetNames.includes(s.getSheetName())) {
      var [, ...v] = s.getDataRange().getValues();
      var temp = v.filter(e => e.join("") != "");
      if (temp.length > 0) {
        o.values = o.values.concat(temp);
        var len = temp.length;
        o.max = o.max < len ? len : o.max;
      }
    }
    return o;
  }, {values: [], max: 0});
  values = values.map(r => {
    var len = r.length;
    return len < max ? r.concat(Array(max - len)) : r;
  });
  var targetSheet = SpreadsheetApp.openById('TARGET_SHEET_ID').getSheetByName('TARGET_SHEET_NAME');
  targetSheet.getRange(targetSheet.getLastRow()   1, 1, values.length, values[0].length).setValues(values);
}

• In this modified script, the values are retrieved from the multiple sheets in one Spreadsheet. And, the empty rows are removed. And, the array length of each element of the values is adjusted. And, the values are put to the target sheet.

References:

• getSheets()
• reduce()
• map()

Added:

From your following replying,

The script works perfectly fine, as long as all SOURCE SHEETS are in the same spreadsheet. But i want the script to also include SOURCE SHEETS that are in other spreadsheets. Example: Spreadsheet A has the sheets 1, 2, 3 and 4, and spreadsheet B has the sheets 4, 5, 6 and 7. The non-empty rows from all these sheets from spreadsheet A und B should be copied and pasted to the spreadsheet "Mastersheet". Is that possible? Thank you so much again for your help :)

How about the following sample script?

Modified script:

function copyAndAddNewEntries() {
  // Please set your Spreadsheet IDs and sheet names.
  var spreadsheets = [
    {ssId: 'SOURCE_SHEET_ID1', sheetNames: ["Sheet1", "Sheet2",,,]},
    {ssId: 'SOURCE_SHEET_ID2', sheetNames: ["Sheet1", "Sheet2",,,]},
    ,,
    ,
    
  ];
  
  var {values, max} = spreadsheets.reduce((o, {ssId, sheetNames}) => {
    SpreadsheetApp.openById(ssId).getSheets().forEach(s => {
      if (sheetNames.includes(s.getSheetName())) {
        var [, ...v] = s.getDataRange().getValues();
        var temp = v.filter(e => e.join("") != "");
        if (temp.length > 0) {
          o.values = o.values.concat(temp);
          var len = temp.length;
          o.max = o.max < len ? len : o.max;
        }
      }
    });
    return o;
  }, {values: [], max: 0});
  values = values.map(r => {
    var len = r.length;
    return len < max ? r.concat(Array(max - len)) : r;
  });
  var targetSheet = SpreadsheetApp.openById('TARGET_SHEET_ID').getSheetByName('TARGET_SHEET_NAME');
  targetSheet.getRange(targetSheet.getLastRow()   1, 1, values.length, values[0].length).setValues(values);
}