Long processing time when inserting data in google sheets through google scripts

Question

I have just built my first google script for a dashboard I am building in google sheets. Essentially, this dashboard has cells which act as filters for calculations in other cells. I am trying to build two buttons, one which returns the filter-cells values to a default value and one which copies the values for the filter from another sheet in the same google spreadsheet.

Problem

The script is taking between 2 and 120s to finish, for an action that done manually takes about 20s, essentially making the button "useless" when it takes more than 30s. Some times the script is incredibly fast, but I would need it to be that fast consistently for it to be worth having. I have tried optimizing the code to the best of my ability (which is not much) based on other threads I have found, but I haven't been able to optimize it yet and I was hoping to find a way to improve my code. Any suggestions?

Code

Default button

function Filters() {
  var spreadsheet = SpreadsheetApp.getActiveSheet();
      range_apply_1 = spreadsheet.getRange('C2:C8'),
      range_apply_2 = spreadsheet.getRange('E4:E6');

  var values_1 = SpreadsheetApp.getActiveSheet().getRange('\'Aux AM\'!A11:A17').getValues()
      values_2 = SpreadsheetApp.getActiveSheet().getRange('\'Aux AM\'!A20:A22').getValues();


 range_apply_1.setValues(values_1),
 range_apply_2.setValues(values_2);
};

Copy values button

function test() {
  var spreadsheet = SpreadsheetApp.getActiveSheet();
      range_apply_1 = spreadsheet.getRange('C2:C8'),
      range_apply_2 = spreadsheet.getRange('E4:E6');

  var values_1 = SpreadsheetApp.getActiveSheet().getRange('\'AM Metrics - Orders\'!C2:C8').getValues()
      values_2 = SpreadsheetApp.getActiveSheet().getRange('\'AM Metrics - Orders\'!E4:E6').getValues();


 range_apply_1.setValues(values_1),
 range_apply_2.setValues(values_2);
};

Rubén Rubén · Accepted Answer · 2021-10-13T01:20:04

The basic to reduce the execution time in Google Apps Script is to to reduce the number of calls to Google Apps Script services. In your specific case you could reduce the calls by using a single getRangeList instead of using multiple getRange.

I think that the following modification should work:

function Filters() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var [range_apply_1, range_apply_2, range_1, range_2] = sheet.getRangeList([
     'C2:C8', 
     'E4:E6',
     'Aux AM!A11:A17',
     'Aux AM!A20:A22'
  ]).getRanges();

  var values_1 = range_1.getValues();
  var values_2 = range_2.getValues();


  range_apply_1.setValues(values_1);
  range_apply_2.setValues(values_2);
};

Regarding variation on the execution time that is normal. It's caused by things on your control and things out of it. One example of things that might be on your control is your PC load as the spreadsheet recalculation might be affected by the PC resources availability. An example of things out of your control is the Google servers responsiveness among other things.

Long processing time when inserting data in google sheets through google scripts

1 Answers