onEdit trigger 'failed' or 'timed out'-CodePudding

I have created an attendance system using the basic script to copy data from the attendance form(Mark attendance and paste it to the response sheet(Form Responses 1). There are two ways to submit the response. 1) By using the 'Submit' button which runs the submitData function, which only works on a pc/laptop and doesn't work on google sheets mobile app. 2. For google sheet mobile app, have created a drop down with an onEdit trigger. When the user the selects the option in the dropdown in cell E1, onEdit runs the same script(or the same submitData function as the 'Submit' button in option 1. Now the success rate of submitData function using the'submit' button is 100% but the onEdit trigger sometime stops in between. (you can see an incomplete response in Row 85 in the 'Form Responses 1' tab in the sheet. Error that comes when I check the executions - 'Failed' and 'Timed out'

Any idea why this is happening and how to correct it?

Link to the spreadsheet -https://docs.google.com/spreadsheets/d/1YoAE4lT50oLxZLpa92h1zuSQIu2N2WtBr5Y1MuHmdzM/edit#gid=1611147373

FYI - I am pulling the student name and other details from a different sheet.

Script for copy pasting the data

 function submitData(){
  //declare variable and set the reference at active google sheet

  var myGoogleSheet = SpreadsheetApp.getActiveSpreadsheet();
  var shMarkAttendance = myGoogleSheet.getSheetByName("Mark_attendance");
  var dataSheet = myGoogleSheet.getSheetByName("Form responses 1");
  var blankRow =  dataSheet.getLastRow()  1 ; //Next blank row  


  //to create the instance of user interface environment to use the alert features

  var ui = SpreadsheetApp.getUi();

  if(shMarkAttendance.getRange("A8:D9").getValue() != "**Please press SUBMIT to mark the attendance**") {
    return; // to exit
  }
   
 //code to update data in Form responses 1
 
  if(shMarkAttendance.getRange("A8:D9").getValue() == "**Please press SUBMIT to mark the attendance**") {

    dataSheet.showSheet()
    dataSheet.getRange(blankRow, 1). setValue(shMarkAttendance.getRange("D1").getValue()); //timestamp
    dataSheet.getRange(blankRow, 2). setValue(shMarkAttendance.getRange("C2:D2").getValue()) // center
    dataSheet.getRange(blankRow, 3). setValue(shMarkAttendance.getRange("C3:D3").getValue()); // Batch
    dataSheet.getRange(blankRow, 4). setValue(shMarkAttendance.getRange("C4:D4").getValue()); // Session date
    dataSheet.getRange(blankRow, 5). setValue(shMarkAttendance.getRange("I5").getValue()); // Session start time
    dataSheet.getRange(blankRow, 6). setValue(shMarkAttendance.getRange("F2:G2").getValue()); // Other coaches present
    dataSheet.getRange(blankRow, 7). setValue(shMarkAttendance.getRange("B6:D6").getValue()); // Your Name
    dataSheet.getRange(blankRow, 8). setValue(shMarkAttendance.getRange("J5").getValue()); //Duration
    dataSheet.getRange(blankRow, 9). setValue(shMarkAttendance.getRange("J9").getValue()); // Present
    dataSheet.getRange(blankRow, 10). setValue(shMarkAttendance.getRange("K9").getValue()); // Absent
    dataSheet.getRange(blankRow, 11). setValue(shMarkAttendance.getRange("L9").getValue()); // Late
    dataSheet.getRange(blankRow, 12). setValue(shMarkAttendance.getRange("F4:G4").getValue()); // Type of session 
    dataSheet.getRange(blankRow, 13). setValue(shMarkAttendance.getRange("G3").getValue()); // without Hi5 jerseys
    dataSheet.getRange(blankRow, 14). setValue(shMarkAttendance.getRange("B7:D7").getValue()); //Any notes or observations
    dataSheet.getRange(blankRow, 15). setValue(shMarkAttendance.getRange("F6:G6").getValue()); //Session plan followed 
    dataSheet.getRange(blankRow, 16). setValue(shMarkAttendance.getRange("G7").getValue()); //Weekly plan

    //to clear the mark attendance form
    shMarkAttendance.getRange("C3:D3").clearContent(); //Batch
    shMarkAttendance.getRange("C4:D4").clearContent(); // session date
    shMarkAttendance.getRange("B5:D5").clearContent(); //Session start time
    shMarkAttendance.getRange("F2:G2").clearContent(); //Other coaches present
    shMarkAttendance.getRange("B6:D6").clearContent(); //Your name
    shMarkAttendance.getRange("F4:G4").clearContent(); // Type of session 
    shMarkAttendance.getRange("F5:G5").clearContent(); // Duration
    shMarkAttendance.getRange("G3").clearContent(); // Without hi5 jersey
    shMarkAttendance.getRange("B7:D7").clearContent(); //Notes/observations
    shMarkAttendance.getRange("F6:G6").clearContent(); // session plan followed
    shMarkAttendance.getRange("G7").clearContent(); // Weekly plan
    shMarkAttendance.getRange("E1").clearContent(); // Submit button
    shMarkAttendance.getRange('E10:G60').activate();
    shMarkAttendance.getActiveRangeList().check();
    shMarkAttendance.getActiveRangeList().uncheck();

    dataSheet.hideSheet();
  }



}


onEdit trigger to run the submitData function

    function onEdit(e) {
  if (e.range.getA1Notation() == 'E1') {
    if (/^\w $/.test(e.value)) {        
      eval(e.value)();
      e.range.clear();
    }
  }
}

CodePudding user response：

Try this (from //code to update data in Form responses 1), it will speed up and prevent any failure

You need to enable the service Google Sheets API (see on the left hand side of the script editor

  //code to update data in Form responses 1
  if (shMarkAttendance.getRange("A8:D9").getValue() == "**Please press SUBMIT to mark the attendance**") {

    //dataSheet.showSheet()
    const ssId = SpreadsheetApp.getActiveSpreadsheet().getId();
    const source = shMarkAttendance.getSheetName();
    const rng = ["D1", "C2", "C3", "C4", "I5", "F2", "B6", "J5", "J9", "K9", "L9", "F4", "G3", "B7", "F6", "G7"].map(e => `'${source}'!${e}`);
    const values = Sheets.Spreadsheets.Values.batchGet(ssId, { ranges: rng })
    var data = []
    values.valueRanges.forEach(e => data.push(e.values ? e.values.flat().toString() : ""))
    dataSheet.appendRow(data)

    //to clear the mark attendance form
    shMarkAttendance.getRangeList(["C3", "C4", "B5", "F2", "B6", "F4", "F5", "G3", "B7", "F6", "G7", "E1"]).clearContent()
    shMarkAttendance.getRange('E10:G60').check();
    shMarkAttendance.getRange('E10:G60').uncheck();

    //dataSheet.hideSheet();
  }

but, onEdit is not compatible with batchGet

change the script as follows

function onEdit(e) {
  if (e.range.getA1Notation() == 'E1') {
    if (/^\w $/.test(e.value)) {
      eval(e.value)();
      e.range.clear();
    }
  }
}

function submitData() {

  //declare variable and set the reference at active google sheet
  var myGoogleSheet = SpreadsheetApp.getActiveSpreadsheet();
  var shMarkAttendance = myGoogleSheet.getSheetByName("Mark_attendance");
  var dataSheet = myGoogleSheet.getSheetByName("Form responses 1");

  //code to update data in Form responses 1
  if (shMarkAttendance.getRange("A8:D9").getValue() == "**Please press SUBMIT to mark the attendance**") {

    //dataSheet.showSheet()
    const cells = ["D1", "C2", "C3", "C4", "I5", "F2", "B6", "J5", "J9", "K9", "L9", "F4", "G3", "B7", "F6", "G7"]
    var data = [];
    for (var cell of cells) data.push(shMarkAttendance.getRange(cell).getValue());
    dataSheet.appendRow(data)

    //to clear the mark attendance form
    shMarkAttendance.getRangeList(["C3", "C4", "B5", "F2", "B6", "F4", "F5", "G3", "B7", "F6", "G7", "E1"]).clearContent()
    shMarkAttendance.getRange('E10:G60').check();
    shMarkAttendance.getRange('E10:G60').uncheck();

    //dataSheet.hideSheet();
  }

}