getSpreadsheetbyname() running very slow

[Luis Felipe Gusberti]

Hi guys,

I don't know why but the first SpreadsheetApp line of my code always take like 6-7 seconds to run. 

I write first line because I've tried to replace the first line and the delay stood in the first line.

When I get a sheet by name in a Global Variable it tooks  too much time to run and I'm out of ideas why this happen and how to turnaround. 

I've tried to code the Variables inside the function and got no results.

This code is for a menu-submenu for an accountability spreadsheet where I have a cashflow data with 4-10k lines and a group sheet with 120 groups/subgroups entries.

my code:

  /** @OnlyCurrentDoc */

  console.time('getFC')

  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FC")

  console.timeEnd('getFC')

  console.time('get Grupos');

  var wsGrupos = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Grupos");

  console.timeEnd('get Grupos');

  console.time('get values');

  var grupos = wsGrupos.getRange(2,1,119, 2).getValues();

  console.timeEnd('get values');

  function onEdit(e) {

    console.time('cellativa');

    var cellativa = SpreadsheetApp.getActive().getActiveSheet().getActiveCell();

    var row = cellativa.getRow();

    var col = cellativa.getColumn();

    var wsNome = cellativa.getSheet().getName();

    console.timeEnd('cellativa');

    if(wsNome == "FC" && col == 9) { 

    console.time('filter');

     var fltr = grupos.filter(function (o) {return o[0] === cellativa.getValue()});

    console.timeEnd('filter');

     console.time('map');

     var validinput = fltr.map(function(o){return o[1]});

console.timeEnd('map');

     console.time('datavalidation');

     var menu = ws.getRange(row, 10)

     if (col !=10){

     menu.setDataValidation(SpreadsheetApp.newDataValidation().requireValueInList(validinput, true).setHelpText('escolha uma categoria da lista').build())}

     console.timeEnd('datavalidation');

    }}

[Tanaike]

When I saw your script, it seems that "onEdit" is used. If you are executing the function as a simple trigger,
how about using the event object? Because when the event object is used, the process cost can be reduced. Ref: https://tanaikech.github.io/2018/04/14/benchmark-event-objects-for-google-apps-script/ If this was not useful for your actual situation, I apologize.

[Braja Patnaik]

Try to use the event object as Tanaike has suggested today. I think earlier also someone has suggested the same. This will reduce the time of your code execution by a little bit as you can avoid one read of the spreadsheet, inside the onEdit function.

Are there any other script files or triggers on this project?

[Luis Felipe Gusberti]

Hi Tanaike, Class5...

I've made this change and as you shown in your article I've got considerable reduction on processing cost. Thanks! But still got like 4-6 sec to run the trigger (supposing It's the trigger). As I'm new at programming maybe I didn't made all the necessary changes, I'll post the new code above, maybe we'll see something...

Answering Class5 question, this Trigger and script is the only one in this project...

The new code:

  /** @OnlyCurrentDoc */

  console.time('get Grupos');

  var wsGrupos = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Grupos");

  console.timeEnd('get Grupos');

  console.time('get values');

  var grupos = wsGrupos.getRange(2,1,119, 2).getValues();

  console.timeEnd('get values');

  function onEdit(e) {

    console.time('cellativa');

    var row = e.range.getRow();

    var col = e.range.getColumn();

    var ws = e.range.getSheet();

    var wsNome = ws.getName();

    console.timeEnd('cellativa');

    if(wsNome == "FC" && col == 9) { 

    console.time('filter');

     var fltr = grupos.filter(function (o) {return o[0] === e.range.getValue()});

    console.timeEnd('filter');

     console.time('map');

     var validinput = fltr.map(function(o){return o[1]});

console.timeEnd('map');

     console.time('datavalidation');

     var menu = ws.getRange(row, 10)

     if (col !=10){

     menu.setDataValidation(SpreadsheetApp.newDataValidation().requireValueInList(validinput, true).setHelpText('escolha uma categoria da lista').build())}

     console.timeEnd('datavalidation');

    }}

And the running time:

[Braja Patnaik]

Last time you were reading the FC sheet first when it took long time. Now, it is the Gropos sheet which is taking long time.

Seems the first call to open the spreadsheet is taking longer.

1. Can you break up the first statement into 2 different statements with timelog in them as below.

 console.time('get ActiveSs');

  var wg = SpreadsheetApp.getActiveSpreadsheet();

  console.timeEnd('get ActiveSs');

console.time('get Grupos;);

var wsGrupos = wg.getSheetByName("Grupos");

  console.timeEnd('get  Grupos'); 

2. Try opening the sheet with SpreadsheetApp.openById('ID HERE').getSheetByName("Grupos");

I hope you know how to get the ID of the spreadsheet.

Not sure if this will work. But you can try it out.

[Braja Patnaik]

3. You can also remove the   if (col !=10) as you are checking for col == 9 in the preceeding if statement.

The col !=10 is now superfluous.

[Luis Felipe Gusberti]

Nice tips Braja, I'll try it all!

I've made a test running the exactly same code on a new empty sheet, just only adding 10k new empty lines. The process time is WAY faster, 1 sec!!! 

Maybe that is what you guys are trying to tell me, and if so, thank you for your patiance, but I suspect that the onEdit event Trigger has to read all data in all spreadsheet to know what is being edited, right?

If so, is there a method to Event Triggers that only runs the trigger if there is edition in a certain range?

[Yusuf Mirza]

Hello All

I have a simple appscript that fetches the first row from a data sheet and populate it into another sheet in the same worksheet. It is usually executed within 1-3 secs. 

From last few days I am observing that the script take a long time at getSheetByName() for the first sheet. Once the first sheet is executed the next sheet does not take time. The below logs shows it took more than 90 secs just to execute getSheetByName()  for the first sheet (Calling Dashboard). The second sheet is executed almost instantaneously with the rest of the script. This is happening randomly after several executions and it is affecting our work.

I have tried SpreadsheetApp.flush(); but that does not help when this happens.

I am wondering if there a better way of handling this or I have missed anything? I have gone through several online resources but could not find any guidance on this kind of issue.

I am attaching my script and any help will be very much appreciated!!

function fetchNextCallBack() {

Logger.log("Start Function")
const myGooglSheet = SpreadsheetApp.getActiveSpreadsheet();
Logger.log("Active Spreadsheet initiated")

//SpreadsheetApp.flush();
const shUserForm = myGooglSheet.getSheetByName("Calling Dashboard");
Logger.log("Calling Dashboard Initiated")

const datasheet = myGooglSheet.getSheetByName("Call Backs");
Logger.log("Call Backs Initiated")

shUserForm.getRange("C8:C22").clearContent();
shUserForm.getRange("F10:F18").clearContent();
shUserForm.getRange("M4:M6").clearContent();

Logger.log("Dashboard cleared")

const values = datasheet.getRange("A3:N3").getValues();
Logger.log("Call back Data fetched")

shUserForm.getRange("C8").setValue(values[0][5]); // vehicle no
shUserForm.getRange("C10").setValue(values[0][3]); // mobile no
shUserForm.getRange("C12").setValue(values[0][2]); // customer name
shUserForm.getRange("F12").setValue(values[0][4]); // model
shUserForm.getRange("C14").setValue(values[0][1]); // call type
shUserForm.getRange("F14").setValue(values[0][6]); // service type
shUserForm.getRange("F20").setValue(values[0][13]); // cre
shUserForm.getRange("C18").setValue(values[0][11]); // appt date
shUserForm.getRange("F18").setValue(values[0][12]); // appt slot
shUserForm.getRange("F10").setValue("REMINDER CALL");

Logger.log("Call back Data populated in dashboard")

}