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

Answer 2

MCVE:

A proper minimal reproducible example:

function testingSetValueToACellWithDataValidationWithRejectInput() {
  var ss = SpreadsheetApp.getActive();
  var sheet1 = ss.getSheetByName('Sheet1');
  try {
    for (var i = 1; i < 5; ++i) {
      var rng = sheet1.getRange(i, 1); //cell with datavalidation set to "reject input"
      rng.setValue('invalidData'); //setting invalid;
    }
    //setValue call above is cached and ignored until getValue()(alternating read/writes)/flush() is called
    /*SpreadsheetApp.flush();//Will error out                 <<<<<<line10 */
    /*sheet1.getRange(4, 1).getValue();//Will error out too   <<<<<<line11 */
  } catch (e) {
    Logger.log('Error catched: ' + e); //happens if line 10/11 is not  commented
  }
  Logger.log('End'); //happens regardless
}

Issue:

• Error thrown on a different line instead of being thrown on the line that it is supposed to occur

• As written in the documentation, It is recommended to avoid alternating read-write calls and that apps script caches set* calls.

  • The cache is written to the spreadsheet only when the cache is flushed. This happens, when
    • Calls are alternated. After a series of "write" calls, a "read" call is made or vice versa.
    • SpreadsheetApp.flush() is called.
    • In your case, the setValue(cellToPost.setValue(message);) call is made only when you alternated with the read call(residentSheet.getRange(i, 2).getValue()). That is why the error is made on the read call and not on the write call.

Solution:

• Use Arrays and loops instead of getValue() in every line, which is really slow.

• If you must follow a inefficient and a slow method and use try...catch to catch the error, then you must flush the cache immediately:

    try {
          cellToPost.setValue(message);
          cellToPost.setNumberFormat('MMM dd, yyyy hh:mm:ss AM/PM');
          SpreadsheetApp.flush(); //Added;Flushes the cache and thus throws the error, which can be caught 
    } catch(err) {
          Logger.log(err);
          residentSheet.getRange(rowIndex, 12).setValue(err);
    }
  

References:

• Optimization
• Arrays