CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Jacek Giemza
Jacek Giemza

Reputation: 11

Speeding up onEdit function in Google Script - unresponsive at times

I have build a script (onEdit) that do a lot of checks for changes to indicate when and by whom changes ware made as well as moving row to archive on specific change, but it is really slow and it crash sometimes on around 3MB workbook.

See below code and let me know if you can help :

function onEdit(event) { 
    var timezone = "GMT"; 
    var timestamp_format = "dd/MM/yyyy 'at' h:mm a"; // Timestamp Format.dd-MM-yyyy hh:mm:ss
    var updateColName = "JL Comments and Actions ";
    var timeStampColName = "Last Modified"; 
    var sheet = event.source.getSheetByName('LOG'); //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 dateCol = headers[0].indexOf(timeStampColName); 
    var updateCol = headers[0].indexOf(updateColName); 
    updateCol = updateCol+1; 

    if (dateCol > -1 && index > 1 && editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself! 
        var cell = sheet.getRange(index, dateCol + 1); 
        var date = Utilities.formatDate(new Date(), timezone, timestamp_format); 
        cell.setValue(date); 
    }

    var updateColName1 = "JL Comments and Actions "; 
    var Acdifference ="Next Action Required by";
    var sheetN = event.source.getSheetByName('LOG'); 
    var actRng2 = event.source.getActiveRange(); 
    var editColumn = actRng2.getColumn(); 
    var index = actRng2.getRowIndex(); 
    var headers = sheetN.getRange(1, 1, 1, sheetN.getLastColumn()).getValues(); 
    var diffCol = headers[0].indexOf(Acdifference); 
    var updateCol1 = headers[0].indexOf(updateColName1); updateCol1 = updateCol1+1;

    if (diffCol > -1 && index > 1 && editColumn == updateCol1) {
        var cell = sheetN.getRange(index, diffCol + 1); 
        cell.setValue ("if");
    }

    var timezone = "GMT"; 
    var timestamp_format = "dd/MM/yyyy 'at' h:mm a"; // Timestamp Format.dd-MM-yyyy hh:mm:ss
    var updateColName = "if Comments/Actions "; 
    var timeStampColName = "Last modified by if"; 
    var sheet = event.source.getSheetByName('LOG'); //Name of the sheet where you want to run this script. 
    var actRng3 = event.source.getActiveRange(); 
    var editColumn = actRng3.getColumn(); 
    var index = actRng3.getRowIndex(); 
    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues(); 
    var dateCol = headers[0].indexOf(timeStampColName); 
    var updateCol = headers[0].indexOf(updateColName); 
    updateCol = updateCol+1; 

    if (dateCol > -1 && index > 1 && editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself! 
        var cell = sheet.getRange(index, dateCol + 1); 
        var date = Utilities.formatDate(new Date(), timezone, timestamp_format); 
        cell.setValue(date); 
    }


    var updateColName1 = "if Comments/Actions "; 
    var Aidifference ="Next Action Required by";
    var sheetN = event.source.getSheetByName('LOG'); 
    var actRng4 = event.source.getActiveRange(); 
    var editColumn = actRng4.getColumn(); 
    var index = actRng4.getRowIndex(); 
    var headers = sheetN.getRange(1, 1, 1, sheetN.getLastColumn()).getValues(); 
    var diffCol = headers[0].indexOf(Aidifference); 
    var updateCol1 = headers[0].indexOf(updateColName1); updateCol1 = updateCol1+1;

    if (diffCol > -1 && index > 1 && editColumn == updateCol1) {
        var cell = sheetN.getRange(index, diffCol + 1); 
        cell.setValue ("JLP");
    }

    var timezone = "GMT"; 
    var timestamp_format = "dd/MM/yyyy 'at' h:mm a"; // Timestamp Format.dd-MM-yyyy hh:mm:ss
    var updateColName = "Lu Comments"; 
    var timeStampColName = "Last modified by Lu"; 
    var sheet = event.source.getSheetByName('LOG'); //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 dateCol = headers[0].indexOf(timeStampColName); 
    var updateCol = headers[0].indexOf(updateColName); 
    updateCol = updateCol+1; 

    if (dateCol > -1 && index > 1 && editColumn == updateCol) { // only timestamp if 'Last Updated' header exists, but not in the header row itself! 
        var cell = sheet.getRange(index, dateCol + 1); 
        var date = Utilities.formatDate(new Date(), timezone, timestamp_format); 
        cell.setValue(date); 
    } 

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = event.source.getActiveSheet();
    var r = event.source.getActiveRange();

    if(s.getName() == "LOG" && r.getColumn() == 21 && r.getValue() == "y") {
        var row = r.getRow();
        var numColumns = s.getLastColumn();
        var targetSheet = ss.getSheetByName("Archived");
        var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
        s.getRange(row, 1, 1, numColumns).moveTo(target);
        s.deleteRow(row);
    }
}

Upvotes: 1

Views: 1266

Answers (1)

ReyAnthonyRenacia
ReyAnthonyRenacia

Reputation: 17651

Array look-ups take its toll on the speed performance of a program every time you use them. Try to look for tips in optimizing coding in Google Sheets here. Also as mentioned here, to speed up a script, read all data into an array with one command, perform any operations on the data in the array, and write the data out with one command.

Upvotes: 1

Related Questions