Combine data in sheets by header name different structures

Question

I have a Google sheet with the following details:

• Many sheets with different names, but some sheets have the naming convention as “EE_*” (sheet name starts with “EE_”). I will call these sheets as sheets EE_. Data of the first row of these sheets is the column headers.
• A master sheet named MasterEffort. Formulas are in cell range $I$2:$Q$2 https://docs.google.com/spreadsheets/d/1Njl4NS3LwzWYt7eloJX_7WVEiNW_gvOvbe_NMmEh9uY/edit#gid=1299605891

I want a Google Apps Script to:

• Clear content in columns A to H of sheet MasterEffort.
• The 1st row of each sheet EE_ is the column headers.
• Identify the sheet EE_ has the most number of columns. Use the column headers of this sheet as the template.
• Combine data of all sheets EE_ based on column headers. If the column does not exist, then leave it blank. If the column does not match the template, skip it.
• Write combined data to columns A to H of sheet MasterEffort.

I tried the following code but ran into an error. Please help.

Exception: The number of rows in the data does not match the number in the range. The data has 21, but the range has 20. consolidateData @ Code.gs:60

function consolidateData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var masterSheet = ss.getSheetByName("MasterEffort");
  var sheets = ss.getSheets();
  var data = [];
  var headers = [];
  var sheetName;
  var sheet;
  var range;
  var values;
  var row;
  var col;
  
  // Clear content in column A to H of sheet MasterEffort.
  masterSheet.getRange("A:H").clearContent();
  
  // Identify the sheet EE_ has the most number of columns. Use the column headers of this sheet as the template.
  for (var i = 0; i < sheets.length; i++) {
    sheetName = sheets[i].getName();
    if (sheetName.indexOf("EE_") === 0) {
      sheet = sheets[i];
      range = sheet.getDataRange();
      values = range.getValues();
      
      // Treat these data as column headers of sheet MasterEffort.
      if (data.length === 0 || values[0].length > data[0].length) {
        headers = values[0];
        data = values.slice(1);
      }
    }
  }
  
  // Combine data of all sheets EE_ based on column headers. If the column does not exist then leave it blank. If the column does not match the template, skip it.
  for (var i = 0; i < sheets.length; i++) {
    sheetName = sheets[i].getName();
    if (sheetName.indexOf("EE_") === 0) {
      sheet = sheets[i];
      range = sheet.getDataRange();
      values = range.getValues();
      
      for (row = 1; row < values.length; row++) {
        data.push([]);
        for (col = 0; col < headers.length; col++) {
          if (headers.indexOf(values[0][col]) !== -1) {
            data[data.length - 1].push(values[row][headers.indexOf(values[0][col])]);
          } else {
            data[data.length - 1].push("");
          }
        }
      }
    }
  }
  
  // Write combined data to column A to H of sheet MasterEffort.
  if (data.length > 0) {
    masterSheet.getRange(1,1,data.length,data[0].length).setValues([headers].concat(data));
  }
}

Answer 1

Although I'm not sure whether I could correctly understand your expected result, in your situation, how about the following sample script?

Sample script:

function myFunction() {
  const masterSheetName = "MasterEffort"; // This is from your question.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const sheetObj = sheets.reduce((ar, sheet) => {
    const sheetName = sheet.getSheetName();
    if (sheetName.indexOf("EE_") === 0) {
      const [header, ...values] = sheet.getDataRange().getValues();
      const obj = values.map(r => r.reduce((o, c, i) => (o[header[i]] = c, o), {}));
      ar.push({ header, obj });
    }
    return ar;
  }, []);
  const [{ header }] = sheetObj.slice().sort((a, b) => a.header.length < b.header.length ? 1 : -1);
  const values = [header, ...sheetObj.flatMap(({ obj }) => obj.map(r => header.map(h => r[h] || null)))];
  const masterSheet = ss.getSheetByName(masterSheetName).clearContents();
  masterSheet.getRange(1, 1, values.length, values[0].length).setValues(values);
}

• When this script is run, the values are retrieved from the sheets of EE_###. And, the values are merged by checking the header row. And, the values are put into "MasterEffort" sheet.

Note:

• This sample script is for your provided Spreadsheet. When you change the Spreadsheet, this script might not be able to be used. Please be careful about this.

References:

• reduce()
• map()