Skip to content
Advertisement

Issue in creating script that copy the sheet and update it based on form response

I am trying to make a copy of Template in same folder when user submits the form and than update the fields in new copied sheet based on what user has submitted in the form response.

New sheet is creating successfully but response I am getting in the form is not updating in the new sheet. Need help in finding the issue in code

error showing :

TypeError: Cannot read property ‘0’ of undefined at CopySheet(Code:32:63) at getResponse(Code:3:16)

function getResponse(e){
 var response = e.response;
 var sheetID = CopySheet(response);
}



function CopySheet(response) {
  var ss = SpreadsheetApp.openById('1Q5XRV7E-m6IleX4vG2tqmJ104Osz6rb67tc8SA_1avk');
  var ssname = ss.getSheetByName('Template');
  
  var file = DriveApp.getFileById(ss.getId());
  var folders = file.getParents();
  while (folders.hasNext())
  {
    var folder = folders.next();
    var FID = folder.getId();
  }
  var protection = ssname.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  var editors = protection[0].getEditors();
  Logger.log(editors);
 
  var TargetFolder = DriveApp.getFolderById(FID);
  var CSheetID = DriveApp.getFileById(ss.getId()).makeCopy("CopiedTemplate", TargetFolder).getId();
  var CopiedSheet = SpreadsheetApp.openById(CSheetID);
  var ptc = CopiedSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var j = 0 ; j<ptc.length ; j++)
  {
   ptc[j].addEditors(editors);
  }
  
  CopiedSheet.getRange('C1').setValue(response['S.C.O Number'][0]);
  CopiedSheet.getRange('F1').setValue(response['Qty'][0]);
  CopiedSheet.getRange('E2').setValue(response['PCBA Code'][0]);
  
  DriveApp.getFileById(CSheetID).setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.EDIT);
  
  return CSheetID;
}

Edited Code :

function getResponse(e){
   var response2 = e.response;
   var IResponse = response2.getItemResponses();
   var SCO_Number = IResponse[0].getResponse();
   var BOM_Code = IResponse[1].getResponse();
   var qty = IResponse[2].getResponse();
   var SID = CopySheet(SCO_Number,BOM_Code,qty);
   
}

Advertisement

Answer

That’s because you are accessing the 0th index of a null variable.

output

Use e.values instead:

Code:

function getResponse(e){
 var response = e.values;
 Logger.log(response);
 // var sheetID = CopySheet(response);
}

and upon checking the value of the response, it should look like this.

response

  • Index 0 of response is the date the form was submitted.
  • Index 1 is the answer of the 1st question
  • Index 2 is for the 2nd question, and so on and so forth

Just use the order of the question then use it as index.

Sample:

If value of S.C.O Number is the answer of the first question, then getting the value of the answer should be response[1]

Code:

CopiedSheet.getRange('C1').setValue(response[1]); // answer to 1st question
CopiedSheet.getRange('F1').setValue(response[2]); // answer to 2nd question
CopiedSheet.getRange('E2').setValue(response[3]); // answer to 3rd question
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement