用于将多个工作表组合到主工作表的脚本 [英] Script to combine multiple worksheets to a master sheet

问题描述

目前的脚本代码有错误，请参阅视频和代码，预计结果

The present script code has errors, please see video and code and the results of it are expected

提前致谢

推荐答案

使用标题字符串作为键将其他表格中存在于Master中的特定列组合

此函数将读取掌握标题并在所有其他工作表上查找这些标题，并在主文件中的正确列下返回。如果找不到特定的标题，它将返回工作表名称，标题名称和未找到的字符串nf。

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.

它还具有该功能要排除您不希望在此过程中涉及的工作表，当然您的代码，它使用名为Master的工作表作为目标，因此不会读取数据的主工作表但它确实读取它以获取第一行中的列名以在其他工作表中搜索。

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.

代码：

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

图片名称在图片上。

这篇关于用于将多个工作表组合到主工作表的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！