0
votes

I am getting the message exceed maximum execution time and have no idea to fix it.

If I leave out the part where it checks if the cell is empty and if it's the modified date column than it worked.

Any idea how to fix this?

function autoUpdateFields(triggerField, valueField, updateValue, event) {
  var timezone = "GMT+1";
  var timestamp_format = "dd-MM-yyyy HH:mm:ss"; // Timestamp Format. 
  var sheet = event.source.getSheetByName('data'); //Name of the sheet where you want to run this script.


  var sheet = event.source.getSheetByName('data');

  var actRng = event.source.getActiveRange();
  var editColumn = actRng.getColumn();
  var index = actRng.getRowIndex();
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();

  var valueCol = headers[0].indexOf(valueField);
  var triggerCol = headers[0].indexOf(triggerField); triggerCol = triggerCol+1;

  if (valueCol > -1 && index > 1 && editColumn == triggerCol) { 
    var cell = sheet.getRange(index, valueCol + 1);
    updateValue = updateValue.replace(/%/g,index);
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);

    // do not overwrite CreatedDt

    var checkIfModifiedDt = headers[0][headers[0].indexOf(valueField)] == 'ModifiedDt';
    var checkIfCellEmpty = cell.getValue() == '';

    Logger.log(checkIfModifiedDt);
    Logger.log(checkIfCellEmpty);
    Logger.log(updateValue);

    // only update when cell is empty
    if (updateValue == 'timestamp' && checkIfCellEmpty) {
      cell.setValue(date)
    }

    if (updateValue != 'timestamp' && checkIfCellEmpty){
      cell.setFormula(updateValue);
    };

    if (checkIfModifiedDt && !checkIfCellEmpty) {
      cell.setValue(date)
    }

  }
}

function onEdit(event)
{ 
  autoUpdateFields('Task', 'DueDt', '=TODAY()', event)
  autoUpdateFields('Task', 'Dleft', '=A%-TODAY()', event)
  autoUpdateFields('Task', 'Priority', '=(if(isblank(F%);5;if(F%=0;1;F22))+if(isblank(E%);5;if(E%=0;1;E%)))*if(B%=0;1;B%+1)', event)
  autoUpdateFields('Task', 'CreatedDt', 'timestamp', event)
  autoUpdateFields('Task', 'ModifiedDt', 'timestamp', event)
  autoUpdateFields('Status', 'CompletedDt', 'timestamp', event)


}

I got it working using following code and guidelines from Anton. Read and process is isolated. I did not isolate the write proces since it only sets these values once with the if function.

If extra modifications can be helpfull please let me know.

function autoUpdateFields(params, triggerField, valueField, updateValue) {

  var valueCol = params.headers[0].indexOf(valueField);
  var triggerCol = params.headers[0].indexOf(triggerField); triggerCol = triggerCol+1;

  if (valueCol > -1 && params.index > 1 && params.editColumn == triggerCol) { 
    var cell = params.sheet.getRange(params.index, valueCol + 1);
    updateValue = updateValue.replace(/%/g,params.index);

    // do not overwrite CreatedDt

    var checkIfModifiedDt = params.headers[0][params.headers[0].indexOf(valueField)] == 'ModifiedDt';
    var checkIfCellEmpty = cell.getValue() == '';


    // only update when field is empty unless ModfiedDt
    if (checkIfCellEmpty && updateValue == 'timestamp') {
      cell.setValue(params.date)
    }

    if (checkIfCellEmpty && updateValue != 'timestamp') {
      cell.setFormula(updateValue);
    };

    if (!checkIfCellEmpty && checkIfModifiedDt) {
      cell.setValue(params.date)
    }

  }
}

function onEdit(event)
{ 

  var timezone = "GMT+1";
  var timestamp_format = "dd-MM-yyyy HH:mm:ss"; // Timestamp Format. 
  var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
  var sheet = event.source.getSheetByName('data'); //Name of the sheet where you want to run this script.
  var actRng = event.source.getActiveRange();
  var editColumn = actRng.getColumn();
  var index = actRng.getRowIndex();
  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues();

  var params = { 'timezone': timezone, 'timestamp_format': timestamp_format, 'date': date, 'sheet': sheet, 'actRng': actRng, 'editColumn': editColumn, 'index': index, 'headers': headers };

  autoUpdateFields(params, 'Task', 'DueDt', '=TODAY()')
  autoUpdateFields(params, 'Task', 'Dleft', '=A%-TODAY()')
  autoUpdateFields(params, 'Task', 'Priority', '=(if(isblank(F%);5;if(F%=0;1;F22))+if(isblank(E%);5;if(E%=0;1;E%)))*if(B%=0;1;B%+1)')
  autoUpdateFields(params, 'Task', 'CreatedDt', 'timestamp')
  autoUpdateFields(params, 'Task', 'ModifiedDt', 'timestamp')
  autoUpdateFields(params, 'Status', 'CompletedDt', 'timestamp')


}
1

1 Answers

0
votes

The issue stems from calling too many services concurrently. Each call to autoUpdateFields() adds one more block on top of the execution stack. Some actions, such as getting the variables for Sheet and Range objects should've been performed only once.

Squeezing these actions inside a function makes your code repetitive, inefficient and VERY slow.

Finally, don't alternate between read and write operations. Instead, follow the read-process-write routine.

1) Move all service calls to onEdit() function. That's where you should create your sheet, range, headers variables, etc. Don't pass the event object as parameter to another function. Get the data you need from it and use to it to determine your target range.

2) Modify your autoUpdateFields() function so that it returns a 2d array of values ready to be consumed by the range.setValue(). Remove any service calls from it.

3) Use if() or switch() operators to figure out what parameters must be passed to the function. If you do, calling it once will be enough.