Combining Specific Columns that exist on Master from other Sheets using header strings as keys

This function will read the Master headers and look for those headers on all other sheets and return under the correct columns in the master. If it doesn’t find a specific header it will return the sheet name, the header name, and the string “nf” for not found.

It also has the ability to exclude sheets that you don’t wish to involve in the process and of course as your code did, it uses a sheet named “Master” as the destination and thus does not read the master sheet for data but it does read it to obtain column names in the first row to search for in other sheets.

I looked over your code and once I figured out what you were doing, I found it a lot easier just to do it myself.

The code:

function combineDataForAllSheets() {
  var ss=SpreadsheetApp.getActive();
  var excl=['Globals','Form Responses 1','Form Responses 2'];
  var masterSheet="Master";
  for(var i=0;i<excl.length;i++) {ss.getSheetByName(excl[i]).hideSheet()}
  var sh=ss.getSheetByName(masterSheet);
  var hrg=sh.getRange(1,1,1,sh.getLastColumn());
  var mhA=hrg.getValues()[0];
  sh.getRange(2,1,sh.getMaxRows()-1,sh.getLastColumn()).clearContent();
  var shts=ss.getSheets();
  for(var i=0;i<shts.length;i++) {
    if(!shts[i].isSheetHidden() && shts[i].getName()!=masterSheet) {
      var shx=shts[i];
      var name=shx.getName();
      var hAx=shx.getRange(1,1,1,shx.getLastColumn()).getValues()[0];
      var nr=sh.getLastRow()+1;
      for(var ix=0;ix<mhA.length;ix++) {
        vAix=[];
        var header=mhA[ix];
        var index=hAx.indexOf(mhA[ix]);
        if(hAx.indexOf(mhA[ix])>-1) {
          var vAix=shx.getRange(2,hAx.indexOf(mhA[ix])+1,shx.getLastRow()-1,1).getValues(); 
        }else{
          for(var kx=0;kx<shx.getLastRow()-1;kx++) {
            vAix.push([shx.getName()+ '-'+ mhA[ix] +'-nf']);
          }
        }
        sh.getRange(nr,ix+1,shx.getLastRow()-1,1).setValues(vAix);
      }                     
    }
  }
}

Some test code that can be used to generate headers of the same name in different column positions. I commented the things you can change to effect how it generates the test data.

function createTestData() { 
  var m=0;//0 for random column positions >0 for fixed position
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet();
  sh.clearContents();
  var cols=10;//number of columns
  var rows=20;//number of rows
  var hpref="hdr";
  var hA=[];
  var hdrA=[];
  var cA=[];
  for(var i=0;i<cols;i++) {hA.push(i+1);};
  for(var i=0;i<cols;i++) {
    if(m==0) {
      var index=Math.floor(Math.random() * hA.length);
    }else{
      var index=i;
    }  
    var hnum=hA[index];
    hdrA.push(hpref + hnum);
    cA.push(hnum);
    if(m==0) {
      hA.splice(index,1);
    }
  }
  sh.getRange(1,1,1,hdrA.length).setValues([hdrA]);    
  var rg=sh.getRange(2,1,rows,cols);  
  var vA=rg.getValues();
  for(var i=0;i<rows;i++) {
    for(var j=0;j<cols;j++) {
      vA[i][j]=Number(i+1) + ',' + cA[j];
    }
  }
  rg.setValues(vA);
}

Sheet Names are on the images.