-1

I have a short timestamp script for my google spreadsheet. But it run quite slow and make a lots

function onEdit(e) {

  var TIMERECIEVE = [0,-29];
  var DATETRECIEVE = [0,-28];
  var DATEDONE = [0,-25]; 
  var DATECHECK = [0,-2];

var sheets =
  ["OS_PE",
  "OS_EPP1",
  "OS_VE",
  "OS_3D",
  "OS_CP",
  "OS_PA",
  "OS_Test"]
  var ss = SpreadsheetApp.getActiveSpreadsheet();
        var sheet = ss.getActiveSheet();1
  for (var s = 0; s < sheets.length; s++) {
        var sheet = ss.getSheetByName(sheets[s]);
    if( sheet.getSheetName() === sheets[s] ) 

    {var selectedCell = ss.getActiveRange();
     {   if( selectedCell.getColumn() == 31) {
       var check1 = selectedCell.offset(TIMERECIEVE[0],TIMERECIEVE[1]);
       var check2 = selectedCell.offset(DATETRECIEVE[0],DATETRECIEVE[1]);
      if(selectedCell.getValue() > 0) 
        {check1.setValue(new Date())
      check2.setValue(new Date());}}}

    {   if( selectedCell.getColumn() == 32){
        var check = selectedCell.offset(DATEDONE[0],DATEDONE[1]);
        if( selectedCell.getValue() >0) 
          check.setValue(new Date());}}

     {  if( selectedCell.getColumn() == 10){
      var check3=selectedCell.offset(DATECHECK[0],DATECHECK[1]);
      if( selectedCell.getValue() === 'App')
        check3.setValue(new Date());}}

     {  if( selectedCell.getColumn() == 11){
      var check4=selectedCell.offset(DATECHECK[0],DATECHECK[1])
      if( selectedCell.getValue() === 'Done')
        check4.setValue(new Date());}}



      }}
  }
0

SpreadsheetApp methods are slow so in order to speed-up our script we should minimize the use of them specially whenever it's possible avoid the use of these methods inside of loops.

One thing to try is to get/set all the values with a single calle rather than making multiple calls to the SpreadsheetApp methods by usins getValues() instead of getValue() and setValues() instead of setValue().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.