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

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

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;

}

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

Understand How This Script Works

Locations: