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