Google Sheets Apps Script – Combine Multiple Tabs to a Master Tab When Column Positions Don’t Match

/in , /by

Results of Apps Script tutorial to combine multiple worksheets to a master sheet. It will also work when column positions don’t match.

function combineData() {
  
  var masterSheet = "Master";
  
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(masterSheet);
  var lc = ss.getLastColumn();
  var lr = ss.getLastRow() > 1 ? ss.getLastRow() : 2;
  
  ss.getRange(2, 1, lr-1, lc).clearContent();
  
  var labels = ss.getRange(1, 1, 1, lc).getValues()[0];
  
  
  labels.forEach(function(label,i){
    var colValues = getCombinedColumnValues(label,masterSheet);
    ss.getRange(2, i+1, colValues.length, 1).setValues(colValues);
    
  })
  
}


function getCombinedColumnValues(label,masterSheetName) {
  
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  
  var colValues = [];
  
  for([i,sheet] in sheets){
    var sheetName = sheet.getSheetName();
    if(sheetName !== masterSheetName) {
      var tempValues = getColumnValues(label,sheetName);
      colValues = colValues.concat(tempValues);
    }
  }
  
  return colValues;
}


function getColumnValues(label,sheetName) {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  var colIndex = getColumnIndex(label,sheetName);
  var numRows = ss.getLastRow() - 1;
  var colValues = ss.getRange(2, colIndex, numRows, 1).getValues();
  return colValues;
}


function getColumnIndex(label,sheetName) {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  
  var lc = ss.getLastColumn();
  var lookupRangeValues = ss.getRange(1, 1, 1, lc).getValues()[0];
  
  var index = lookupRangeValues.indexOf(label) + 1;
  
  return index;
  
}

Understand How This Script Works