Google Sheets Script, Custom function runtime error (30seconds). How to make function run faster?

Question

I'm trying to create a function that loops through column "M" and multiplies each cell with a constant number (ex, 10.5). I finally managed to create a function that does this, but it takes time to execute and hits a runtime error before it finishes because it takes longer than 30 seconds to execute fully. Do you guys think it's possible to speed up my code somehow? Would appreciate if anyone could point me in the right direction. Below is my code.

function onEdit(e) {
  var ss = e.source;
  var cell = e.range;

  if(cell.getRow() == 1 && cell.getColumn() == 13 && e.value != null){

    if (cell.getDisplayValue() == 'Currency EURO') {
      var valuta = 10.5;
    }
    if (cell.getDisplayValue() == 'Currency Pund') {
      var valuta = 12.31;
    }


    var range = ss.getRange("M:M");
    var count = range.getDisplayValues();

    for(var i=2;i<count.length;i++){


      ss.getRange("M"+ i).setValue(Number(ss.getRange("M"+ i).getDisplayValue())*valuta);

      }


  }
}

Answer 1

I think the issue is that you're using the spreadsheet functions too much - you're basically interacting with the spreadsheet in every iteration of that for loop. This has a huge overhead.

I propose two ideas:

1. Code fix: I suggest using the spreadsheet functions as little as possible. They are very expensive. This means retrieving the range only once, and updating the range only once.

This is my suggestion:

function onEdit(e) {
  var ss = e.source;
  var cell = e.range;

  if(cell.getRow() == 1 && cell.getColumn() == 13 && e.value != null){

    if (cell.getDisplayValue() == 'Currency EURO') {
      var valuta = 10.5;
    }
    if (cell.getDisplayValue() == 'Currency Pund') {
      var valuta = 12.31;
    }

    // This is a reference to the range
    var range = ss.getRange("M:M");
    // This actually reads the values - do it only once
    var count = range.getDisplayValues();

    // This updates the count variable - does not interact with the spreadsheet yet
    for(var i=2;i<count.length;i++){
       count[i][0] *= valuta;
    }

    // This updates the range - done only once.
    range.setValues(count);
  }
}

The code has not been tested, so let me know if there are any errors to fix.

2. Optional: take a look at this answer from Tanaike - especially the pattern #3. You could make it faster if you ignore the blank values when getting the range.

Answer 2

  function onEdit(e) {

    const range = e.range;

    if (range.getRow() === 1 && range.getColumn() === 13 && e.value) {

      let valuta;
      if (e.value === 'Currency EURO') { valuta = 10.5 }
      if (e.value === 'Currency Pund') { valuta = 12.31 }

      const sheet = e.source.getActiveSheet()
      const values = sheet.getRange(`M2:M`)
                          .getDisplayValues()
                          .filter(String)
                          .map(i => [Number(i) * valuta])

      sheet.getRange(2, 13, values.length, values[0].length).setValues(values)

    }

}

Here is an optimized version of your function.

• The primary optimization is that all data changes are handled 'at one time'.
• The secondary optimization is that there is only one call for values.
• This will also handle any empty cells, and avoid the first row cell.

Answer 3

Try it this way:

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == "Sheet Name" && e.range.rowStart == 1 && e.range.columnStart == 13 && e.value != null) {
    if (e.range.getDisplayValue() == 'Currency EURO') {
      var valuta = 10.5;
    }
    if (e.range.getDisplayValue() == 'Currency Pund') {
      var valuta = 12.31;
    }
    let vs = sh.getRange("M2:M" + sh.getLastRow()).getDisplayValues().flat().map((e,i) => [e * valuta] )
    sh.getRange("M2:M"+sh.getLastRow()).setValues(vs));
  }
}