Try...catch not working as expected in Google Apps Script

Question

[Edited to include a minimal reproducible example as suggested by T.J. Crowder.]

I'm working on a simple function using Google Apps Script that is supposed to post either a date or an error string to a spreadsheet's column. The column already has data validation rules that reject any value that is not a valid date. This is all well and good.

My problem is this:

I tried using a try...catch block to gracefully handle the error, and to just log the error message when a value doesn't pass the data validation. The try...catch doesn't seem to be working at all. Instead, the script throws the error and just breaks, and the log comes up empty.

Here's an updated screenshot (sorry, Sourabh Choraria, for overriding your update) with the new code. Surprisingly, GAS is highlighting a line way above where the error should have occurred.

For a little bit of background, this script gets the IDs of various other spreadsheets stored in a column, gets the last updated timestamp for each spreadsheet, and posts the results in the result column.

Here's the code I used.

function trackDocUpdates() {
    //Set the global variables
    var ss = SpreadsheetApp.getActive();
    var residentSheet = ss.getSheetByName("Resident Documents");
    var activeRange = residentSheet.getDataRange();
    var numRows = activeRange.getNumRows();
    var lastRevision = "No value yet.";

    //Loop through the rows of data to get the last updated timestamp of each document
    //The first data row is the 5th row of the sheet currently, hence the for loop starts at 5
    for (i = 5; i <= numRows; i++) {
        //Get the document URL from the current row. Currently the second column has the document URLs
        var docURL = residentSheet.getRange(i, 2).getValue();
        //Extract the document's ID from the URL
            var docId = docURL.split("/")[5];
            //As long as there's a valid-looking document ID, get the last updated timestamp for the current document in the loop
            if (docId != undefined) {
                lastRevision = getLastRevision(docId);
                Logger.log(lastRevision);
            }

            else {
                lastRevision = "No document URL found";
                Logger.log(lastRevision);
            }
        //Post the last updated timestamp in the appropriate result cell
        postLastUpdatedTime(lastRevision, i, 9);
    }

    //Function to get the last updated timestamp for a given document
    function getLastRevision(docId) {
        //Try to get the last updated timestamp for the given document ID
        try {
            var revisions = Drive.Revisions.list(docId);
            if (revisions.items && revisions.items.length > 0) {
                var revision = revisions.items[revisions.items.length-1];
                var lastModified = new Date(revision.modifiedDate);
                //var modifiedDateString = Utilities.formatDate(lastModified, ss.getSpreadsheetTimeZone(), "MMM dd, yyyy hh:mm:ss a");
                return lastModified;
            }

            else {
                return 'No revisions found.';
            }
        }
        //If the file cannot be accessed for some reason (wrong docId, lack of permissions, etc.), return an appropriate message for posting in the result cell
        catch(err) {
            return "File cannot be accessed.";
        }

    }

    //Function to post the last updated timestamp for a given document in the given result cell
    function postLastUpdatedTime(message, rowIndex, colIndex) {
        //If there's no argument is passed to colIndex, set its value to be 11
        colIndex = colIndex || 11;
        var cellToPost = residentSheet.getRange(rowIndex, colIndex);
        try {
            cellToPost.setValue(message);
            cellToPost.setNumberFormat('MMM dd, yyyy hh:mm:ss AM/PM');
        }

        catch(err) {
            Logger.log(err);
            residentSheet.getRange(rowIndex, 12).setValue(err);
        }

    }

    //Update the last refreshed time of the script in the first row of the result column
    var scriptUpdatedTime = new Date();
    postLastUpdatedTime(scriptUpdatedTime, 1);
}

Could anyone help me understand where I went wrong?

PS: I don't have the liberty to remove the data validation that presented this problem in the first place, since I'm just adding a functionality to a client's existing spreadsheet.

Answer 1

I was able to reproduce your problem.

This exact issue has been reported to Google (issuetracker.google.com) and they filed an internal case [1] about try/catch statements in Apps Script not handling data validation errors.

[1] https://issuetracker.google.com/issues/36763134#comment7