Merge all excel sheets into a one new Sheet

Question

I need your help, I have an Excel file type .xlsx, which was sent automatically from a system to my email, this Excel comes with the information distributed in several tabs within the same file, what I want to do is an Office script that takes the information from each of the sheets and unifies it into a single sheet, once all the data has been copied, delete all the sheets and the file is left with a single tab, with all the data unified. Thanks for your help.

function main(workbook: ExcelScript.Workbook) {
  // Get all the worksheets in the workbook
  let sheets = workbook.getWorksheets();

  // Create a new worksheet named "Consolidate"
  let consolidateSheet = workbook.getWorksheet("Consolidate") || workbook.addWorksheet("Consolidate");

  // Loop through each sheet in the workbook
  for (let sheet of sheets) {
    // Get the data range of the current sheet
    let dataRange = sheet.getUsedRange();

    // Get the values from the data range
    let values = dataRange.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.getRange(lastRow + 1, 1, values.length, values[0].length);

    // Paste the values into the Consolidate sheet
    targetRange.setValues(values);
  }
}

Answer 1

• If the sheet named "Consolidate" is exist, you may need clear the sheet before collect data.
• let values = dataRange.getValues() includes the header row, then there are multiple header on Consolidate sheet.
• getRangeByIndexes() and getRange() are different.
• Note: Assuming there is a header row on each sheet.

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();
    }
  }
}

---

Update:

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();
    }
  }
}

---

• Using concat method to consolidate the data and writing data to sheet all at once are more efficient.

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.log(allValues);
}