Объедините все листы Excel в один новый лист

• Если лист с именем «Объединить» существует, вам может потребоваться очистить лист перед сбором данных.
• let values = dataRange.getValues() включает строку заголовка, тогда на листе консолидации будет несколько заголовков.
• getRangeByIndexes() и getRange() разные.
• Примечание. Предполагается, что на каждом листе есть строка заголовка.

function main(workbook: ExcelScript.Workbook) {
  // Get all the worksheets in the workbook
  let sheets = workbook.getWorksheets();
  const sumShtName = "Consolidate";
  // Create a new worksheet named "Consolidate"
  let consolidateSheet = workbook.getWorksheet(sumShtName) || workbook.addWorksheet(sumShtName);
  // Clear sheet
  consolidateSheet.getRange().clear(ExcelScript.ClearApplyTo.all);
  // Loop through each sheet in the workbook
  for (let sheet of sheets) {
    // Get the data range of the current sheet
    if (sheet.getName() != sumShtName) {
      let dataRange = sheet.getUsedRange();
      let colCount = dataRange.getColumnCount();
      let rowCount = dataRange.getRowCount();
      // Copy table header
      if (!consolidateSheet.getRange("A1").getText()) {
        consolidateSheet.getRange("A1").copyFrom(dataRange.getAbsoluteResizedRange(1, colCount))
      }
      // Get the values from the data range, skip headser
      let values = dataRange.getOffsetRange(1, 0).getResizedRange(-1, 0).getValues();
      // Find the last row with data in the Consolidate sheet
      let lastRow = consolidateSheet.getUsedRange().getRowCount();
      // Get the range where the data will be pasted in the Consolidate sheet
      let targetRange = consolidateSheet.getRangeByIndexes(lastRow, 0, values.length, colCount);
      // Paste the values into the Consolidate sheet
      targetRange.setValues(values);
      sheet.delete();
    }
  }
}

Обновлять:

function main(workbook: ExcelScript.Workbook) {
  // Get all the worksheets in the workbook
  let sheets = workbook.getWorksheets();
  const sumShtName = "Consolidate";
  const headerRows = 2;
  // Create a new worksheet named "Consolidate"
  let consolidateSheet = workbook.getWorksheet(sumShtName) || workbook.addWorksheet(sumShtName);
  // Clear sheet
  consolidateSheet.getRange().clear(ExcelScript.ClearApplyTo.all);
  // Loop through each sheet in the workbook
  for (let sheet of sheets) {
    // Get the data range of the current sheet
    if (sheet.getName() != sumShtName) {
      let dataRange = sheet.getUsedRange();
      let colCount = dataRange.getColumnCount();
      let rowCount = dataRange.getRowCount();
      // Copy table header
      if (!consolidateSheet.getRange("A1").getText()) {
        consolidateSheet.getRange("A1").copyFrom(dataRange.getOffsetRange(headerRows-1,0).getAbsoluteResizedRange(1, colCount))
      }
      // Get the values from the data range, skip headser
      let values = dataRange.getOffsetRange(headerRows, 0).getResizedRange(-1 * headerRows, 0).getValues();
      // Find the last row with data in the Consolidate sheet
      let lastRow = consolidateSheet.getUsedRange().getRowCount();
      // Get the range where the data will be pasted in the Consolidate sheet
      let targetRange = consolidateSheet.getRangeByIndexes(lastRow, 0, values.length, colCount);
      // Paste the values into the Consolidate sheet
      targetRange.setValues(values);
      sheet.delete();
    }
  }
}

• Использование метода concat для консолидации данных и одновременной записи данных в лист более эффективно.

function main(workbook: ExcelScript.Workbook) {
  // Get all the worksheets in the workbook
  let sheets = workbook.getWorksheets();
  const sumShtName = "Consolidate";
  const headerRows = 2;
  // Create a new worksheet named "Consolidate"
  let consolidateSheet = workbook.getWorksheet(sumShtName) || workbook.addWorksheet(sumShtName);
  // Clear sheet
  consolidateSheet.getRange().clear(ExcelScript.ClearApplyTo.all);
  let allValues: (string | number | boolean)[][] = [];
  let bFristSht: boolean = true;
  // Loop through each sheet in the workbook
  for (let sheet of sheets) {
    // Get the data range of the current sheet
    if (sheet.getName() != sumShtName) {
      let dataRange = sheet.getUsedRange();
      let values: (string | number | boolean)[][] = [];
      if (bFristSht) {
        // Get table with header from the 1st sheet
        values = dataRange.getOffsetRange(headerRows - 1, 0).getResizedRange(1 - headerRows, 0).getValues();
        bFristSht = !bFristSht;
      } else {
        // Get table w/o header
        values = dataRange.getOffsetRange(headerRows, 0).getResizedRange(-headerRows, 0).getValues();
      }
      allValues = allValues.concat(values);
      // sheet.delete
    }
  }
  // Get the range where the data will be pasted in the Consolidate sheet
  let targetRange = consolidateSheet.getRangeByIndexes(0, 0, allValues.length, allValues[0].length);
  // Paste the values into the Consolidate sheet
  targetRange.setValues(allValues);
  console.info(allValues);
}