使用主控图纸创建图纸

Question

1. 我有一个名为form responses的表单，它从google表单中获取输入，然后我用C列（员工代码）中的名称创建了表单，并将表单响应中的所有行附加到新创建的表单中

我开发的代码如下所示：

 function switchSheet(){
    var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = activeSpreadsheet.getSheets();
    for (var i = 1; i < sheets.length; i++) {
       var p =  sheets[i].getSheetName();
       sheetCreateChannel(p);
      }
    }
 function sheetCreateChannel(ss){
   var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
   var sheet1 = activeSpreadsheet.getSheetByName(ss);
   var getNames = sheet1.getDataRange().getValues();
   getNames.shift();
   var sheets = activeSpreadsheet.getSheets();
   var sheetsObj = {};
      for (var i = 0; i < sheets.length; i++) {
       sheetsObj[sheets[i].getSheetName()] = sheets[i];
       for (var i = 0; i < getNames.length; i++) {
         var r = getNames[i];
         if (r[4].toString()== "ASR"&& !((ss+(r[4])) in sheetsObj)) {
                    var newSheet = activeSpreadsheet.insertSheet(ss + "ASR");
                    sheetsObj[ss+"ASR"] = newSheet;
                   }
         else if (r[4].toString()== "ACC"&&!((ss+(r[4])) in sheetsObj)) {
              var newSheet = activeSpreadsheet.insertSheet(ss + "ACC");
              sheetsObj[ss+"ACC"] = newSheet;
                   }
         else if (r[4].toString()== "RSR"&& !((ss+(r[4])) in sheetsObj)) {
              var newSheet = activeSpreadsheet.insertSheet(ss + "RSR");
              sheetsObj[ss+"RSR"] = newSheet;
              }
         else  {
              break;
              }
            }
           } 
         var deleteRows = 0;
           for (var i = 0; i < getNames.length; i++) {
            var r = getNames[i];
             if (r[4].toString() != "") {
              if ((ss+(r[4])) in sheetsObj) {
               var Q =ss+(r[4]);
                sheetsObj[Q].appendRow(r);
             } 
          deleteRows = i + 2;
          }    
         }
        sheet1.getRange(2, 1, deleteRows - 1, sheet1.getLastColumn()).clearContent();
       } 

Answer 1

此代码适用于我的上述问题

 function sheetCreate(){
   var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
   var sheet1 = activeSpreadsheet.getSheetByName("Form Responses 1");
   var getNames = sheet1.getDataRange().getValues();
   getNames.shift();
   var sheets = activeSpreadsheet.getSheets();
   var sheetsObj = {};
    for (var i = 0; i < sheets.length; i++) {
     sheetsObj[sheets[i].getSheetName()] = sheets[i];
     }
   var deleteRows = 0;
    for (var i = 0; i < getNames.length; i++) {
     var r = getNames[i];
     var N=r[2].toString()+r[4].toString(); 
        if (N != "") {
         if (N in sheetsObj) {
          sheetsObj[N].appendRow(r);
        } else {
          var newSheet = activeSpreadsheet.insertSheet((N));
          var cell1 = newSheet.getRange("A1").setValue("Timestamp");
          var cell2 = newSheet.getRange("B1").setValue("Score");
          var cell3 = newSheet.getRange("C1").setValue("Employee ID");
          var cell4 = newSheet.getRange("D1").setValue("Name of the ATCO");
          var cell5 = newSheet.getRange("E1").setValue("The channel on which duty is performed ");
          var cell6 = newSheet.getRange("F1").setValue("DATE on which duty  is performed");
          var cell7 = newSheet.getRange("G1").setValue("Total duration of duty done");
          newSheet.appendRow(r);
          sheetsObj[N] = newSheet;
          }
        deleteRows = i + 2;
        }    
       }
      sheet1.getRange(2, 1, deleteRows - 1, sheet1.getLastColumn()).clearContent();
     }