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');

Logs

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?

Please explain what you are trying to do and give some examples before and after. Provide us with an image of the data. I don't wish to read your code any further and I don't understand what you are attempting to do. — Cooper, Nov 30 '22 at 17:16
Welcome to [so]. Google Apps Script services are slow, so if you want to improve the script performance you have to reduce the number of calls to these services. One way is to use batch operations, i.e. instead of writing one cell value at a time, write all of them at once. Also is opening a sheet takes too much time, it might be possible that there are a lot of formulas. — Rubén, Dec 06 '22 at 02:39

score 1 · Accepted Answer · answered Nov 30 '22 at 14:30

  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.

getSheetByName takes too long to process during onFormSubmit trigger

1 Answers1