Creating sheets with from master sheet

Question

I have a sheet named form responses which gets input from google forms,Then I created sheets with names from Column C which is employee code and append all the respective rows from form responses to the newly created sheets.
Now I have sheets with name as employee code ( which is in column c of the sheet). Then I want to create secondary sheets from this master sheet(sheets with name as employee code) whose name is using the column E value as attached to the Employee code.for eg : if Emp code is 10003825 and value in column E is ASR, the sheet name should be 10001515ASR.
In Column E there are only 4 values ADC,ASR ACC,RSR. let the master sheet(sheets with name as employee code) retain ADC rows always while when other rows containing ASR,ACC,RSR comes append the respective row to the newly created sheet(ie if ASR the row goes to 10001515ASR).
I have shown my present sheet and expected output

The code I developed is shown below:

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

I have to apologize for my poor English skill. Can I ask you about the current issue of your script? — Tanaike
@Tanaike I have created a sheet from my master sheet using column C value. Now I want to create a sheet using the sheets created using column C as master sheet. I have attached a screenshot of my sheet — Kumara Guru
Thank you for replying. Unfortunately, from your replying and updating question, I cannot still understand about your current issue and goal. I apologize for this. Can you give me a time to understand about them? — Tanaike
My English is pretty good, but I don't understand the request either. You state you want a sheet name to be the EmployeeID + column E. Okay... what's the problem? You'll probably run into some duplicate sheet names. If you were able to execute your code correctly, what would it look like? Perhaps share a sample file? — pgSystemTester
@Tanaike The issue is I have to create sheets from secondary sheet for eg here 10001515 sheet to create sheets 10001515ASR,10001515ACC,10001515RSR but not 10001515ADC. then append all the rows relevent to ASR in 10001515 to 10001515ASR and so on, but for all rows relevent to ADC let those rows remain in 10001515.whenever the master sheet is updated this sheet also get updated when the code is ran and it should not create 10001515ASRASR like as I am getting error like this everytime updation happens — Kumara Guru

Kumara Guru Kumara Guru · Accepted Answer · 2020-08-27T06:01:45

This code works fine for my above issue

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

Creating sheets with from master sheet

1 Answers