Ignore multiple sheets besides "MasterSheet" with script "Combine Multiple Tabs to a-CodePudding

I've been using a script lately I've found at http://www.chicagocomputerclasses.com/google-sheets-apps-script-combine-multiple-tabs-to-a-master-tab-when-column-positions-dont-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 (let [i, sheet] of Object.entries(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;
  
}

It's a great script. Unfortunately the script only ignores "MasterSheet" when being executed.

Please can someone modify the script so there ist an option to ignore multiple sheets besides "MasterSheet"?

That would be awesome!

CodePudding user response：

function getCombinedColumnValues(label,shts) {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  
  var colValues = [];
  
  for (let [i, sheet] of Object.entries(sheets)) {
    var sheetName = sheet.getSheetName();
    if(!~shts.indexOf(sheetname)) {
      var tempValues = getColumnValues(label,sheetName);
      colValues = colValues.concat(tempValues);
    }
  }
  
  return colValues;
}

function combineData() {
  
  var shts = ["Master"];//add more sheet names as desired
  
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Master");
  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,shts);
    ss.getRange(2, i 1, colValues.length, 1).setValues(colValues);
    
  })
  
}

CodePudding user response：

As far as I can tell it can be done if you change three lines in the first function:

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