getSheetByName takes too long to process during onFormSubmit trigger

Question

I'm creating a spreadsheet wherein all projects were entered thru Google Form linked to a Google Spreadsheet and then retrieving the information to a specific Sheet intended for each Teams thru onFormSubmit Trigger. I am able to get what I want but I came to a point where my script takes too long to complete. And I figure out which part of the script is the culprit.

I'm getting around 20s just to return

var sheet = e.source.getSheetByName(TeamSheetNo);

Here is the script for the onFormSubmit function.

function onFormSubmit(e) {

  Logger.log("onFormSubmit has been ran.");

  var itemResponses = e.values;

  var Rteam = itemResponses[7];
  var Rjobno = itemResponses[1];
  var Rprojectname = itemResponses[2];
  var Rclientname = itemResponses[3];
  var Rother = itemResponses[6];
  var Rjobmanager = itemResponses[4];
  var Rcomponent = itemResponses[5];

  Logger.log("Item Response Lists - " + [Rteam, Rjobno, Rprojectname, Rclientname, Rother, Rjobmanager, Rcomponent]);


  console.time('GetInsertJobIndex');
  var Teams = JSON.parse(userproperties.getProperty('IndexTeams'));

  Logger.log(Teams);
  var search = Rteam

  var TeamIndex = Teams.indexOf(search) + 1;

  Logger.log("Insert Job at Sheet No. " + TeamIndex);

  console.timeEnd('GetInsertJobIndex');

  console.time('Insert_Response');
  Logger.log("Start Insert Project");

  // Get Team Sheet Tab
  //var TeamSheetNo = userproperties.getProperty("TeamSheetNo");
  var TeamSheetNo = TeamIndex;
  Logger.log("Go to Sheet No. " + TeamSheetNo);
  console.time('ActiveSheet');
  var sheet = e.source.getSheetByName(TeamSheetNo);
  //var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(TeamSheetNo);  //this take too long to run
  console.timeEnd('ActiveSheet');
  //Logger.log("Get " + sheet.getRange("C1:C1").getValue());
  var ColumnToCheck = sheet.getRange("A:A").getValues();
  Logger.log("Get " + ColumnToCheck);
  var LastRow = getLastRowSpecial(ColumnToCheck);
  Logger.log("Get Last Row = " + LastRow);

  // Get the ID Number for the Submitted Project 
  // var formId = '1_il_J6Hm_3D-ASB8cKVXYiLZVtVJcBdB0hfPRMQazEA';
  var form = FormApp.openById(formId);
  var IDnumber = form.getResponses().length;
  Logger.log(IDnumber + " - ID Number for the Submitted Project")

  // Get Form Responses 1 Tab 
  var FormSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1');
  var FormColumntoCheck = FormSheet.getRange("A:A").getValues(); //Get all values from sheet
  var FormLastRow = getLastRowSpecial(FormColumntoCheck);

  // Check whether Submitted Project exists on the Existing Data
  let found = false;
  FormColumntoCheck.forEach(r => {
    if (~r.indexOf(IDnumber)) {
      Logger.log("Submitted Project Found on the Existing Data");
      found = true;
    };
  });

  if (found) {
    Logger.log("Exit Function");
    return;

  } else {
    Logger.log("Insert New Job then")

    //SpreadsheetApp.getActiveSpreadsheet().toast("Consolidating New Project Data...", "💡",3);  <----- Not working on OnFormSubmit Trigger

    // Insert New Project

    // Insert New Project to Form Responses 1 Tab
    FormSheet.getRange(FormLastRow + 1, 1).setValue(IDnumber);
    Logger.log("Insert New Project to Form Responses 1 Tab");

    // Insert New Project to Team Sheet Tab
    var value = [IDnumber]; // inserted to Team Sheet Tab
    for (var i = 1; i <= 4; i = i + 1) {
      var row_number = LastRow + i

      // Value has been inserted
      sheet.getRange(row_number, 1).setValues([value]);
    };
  };

  SpreadsheetApp.flush();
  Logger.log("Insert New Project to Task Tab");


  //UpdateFilterRange(sheet, row_number);
  //SpreadsheetApp.flush();

  console.timeEnd('Insert_Response');

  getEditResponseUrls(e);

  SpreadsheetApp.flush();
  //SpreadsheetApp.getActiveSpreadsheet().toast("Update Complete", "💡",3);       <----- Not working on OnFormSubmit Trigger




}

Here is the logs for

console.time('ActiveSheet');

The script is too long because I tried making it work on a single function first and wishing it will help. Unfortunately, it did not. Actually, these are made from multiple functions called under onFormSubmit function.

Are there any solution/s on my problems?

Answer 1

  console.time('ActiveSheet');
  var sheet = e.source.getSheetByName(TeamSheetNo);
  console.timeEnd('ActiveSheet');

(20060 ms)

The getSheetByName() call should not take that long unless the spreadsheet is on the heavy side. Chances are that you can improve the loading time by optimizing the spreadsheet. See these optimization tips.