App Script code is so slow [closed]

Question

I have created a script for my sheet to do the following when a certain cell is edited:

• Get the timestamp

• Get the user E-mail

• Add the text "New" in another spreadsheet.

    function WhenEdit(e){
  
    var currentDate = new Date();
    var triggeringSheets = new Array ("sheet1", "sheet2","sheet3");  
    var triggeredSheet =  e.source.getActiveSheet().getName();
    var col = e.range.getColumn(); // triggered col
    var user = e.user.getEmail(); // who triggered the funcation
    var triggeredRanage =  e.range.rowEnd - e.range.rowStart + 1;
    var eventStart = e.range.rowStart + 1;
    var row = eventStart; //value for row
    var user = e.user.getEmail(); // who triggered the funcation
  
    if (triggeringSheets.indexOf(triggeredSheet) != -1) {// check the triggered sheet nae
  
      if(col === 7 && row >= 2) { //check the triggered col & row
  
          var newArr =[];
          var dateArr =[];
          var userArr=[];
  
          for(var i=0; i<triggeredRanage; i++){
                newArr.push(["New"]);
                dateArr.push([currentDate]);
                userArr.push([user]);
          }
  
          e.source.getActiveSheet().getRange(eventStart,10,triggeredRanage,1).setValues(dateArr); // set time stamp
          e.source.getActiveSheet().getRange(eventStart,9,triggeredRanage,1).setValues(userArr); // set user email
  
          var anotherSheet = SpreadsheetApp.openById("Link");
          var secodSheet= anotherSheet.getSheetByName(triggeredSheet);
          secodSheet.getRange(eventStart,13, triggeredRanage, 1 ).setValues(newArr); // set new
  
          return;
  
  
      }
  

It's working but it takes forever to run! What can I change to make it run faster?

Answer 1

Your script doesn't look bad. First try to reduce the execution time by reducing the calls to the Google Apps Script methods, like SpreadsheetApp.Range.getColumn(), SpreadsheetApp.getActiveSheet(), SpreadsheetApp.Sheet.getRange.

If that is not enough you might try to use the Advanced Sheets Service or your Sheets API.

---

Instead of

var col = e.range.getColumn(); // triggered col

use

var col = e.range.columnStart; // triggered col

Instead of calling e.source.getActiveSheet() multiple times declare a variable, i.e.

const sheet = e.source.getActiveSheet();

Instead of

var newArr =[];
var dateArr =[];
var userArr=[];

for(var i=0; i<triggeredRanage; i++){
      newArr.push(["New"]);
      dateArr.push([currentDate]);
      userArr.push([user]);
} 
e.source.getActiveSheet().getRange(eventStart,10,triggeredRanage,1).setValues(dateArr); // set time stamp
e.source.getActiveSheet().getRange(eventStart,9,triggeredRanage,1).setValues(userArr); // set user email

use

var newArr =[];
var dateArr =[];
var userArr=[];
var values = [];
for(var i=0; i<triggeredRanage; i++){
      newArr.push(["New"]);
      dateArr.push([currentDate]);
      userArr.push([user]);
      values.push([user,currentDate]);
} 
const sheet = e.source.getActiveSheet(); 
sheet.getRange(eventStart,10,triggeredRanage,1).setValues(values); // set user email and time stamp