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')
}