Combining Master Columns from other Sheets

This function will read the Master headers and look for those headers on all other sheets and return them 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 it skips over the "Master" as the destination and thus does not read the master sheet for data.

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

}

}

}

}

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

}