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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
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; } |