javascript - Try...catch not working as expected in Google Apps Script - Stack Overflow

admin管理员组

文章数量:1289540

[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 es 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.

[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 es 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.

• javascript
• validation
• google-apps-script
• google-sheets
• try-catch

Share Improve this question Follow edited Oct 29, 2019 at 10:24 Sourabh Choraria 2,3312727 silver badges6565 bronze badges asked Oct 29, 2019 at 9:18 Karthik SivasubramaniamKarthik Sivasubramaniam 68399 silver badges1313 bronze badges 6

• "Sorry, I can't give here the full code that I was working on that originally generated this error, since it's a client's property." No, but you can provide a minimal reproducible example of it. – T.J. Crowder Commented Oct 29, 2019 at 9:19
• The try/catch shown above is correct. If you're still getting an actual JavaScript error, either it's not ing from the code within the try block above (likely), or GAS doesn't let you catch that error (which is not likely). For instance, is validation an asynchronous thing? Or is the error not thrown as a JavaScript error? – T.J. Crowder Commented Oct 29, 2019 at 9:20
• 1 If you want to just ignore the error, it sounds like you want setAllowInvalid. If you can't do that, and it's not a JavaScript error but instead something Google Sheets displays to the user, I don't think you can do much about it (since if you can't do that, you probably can't use setHelpText, either). – T.J. Crowder Commented Oct 29, 2019 at 9:27
• @T.J.Crowder Thanks for the advice. I've included a minimal reprex now. As for the possibility of the error not being a JS one, or of the validation being asynchronous, I'm not equipped to answer that technically, since I'm relatively a beginner to Javascript itself. But from the little that I know, the validation is not likely an asynchronous process (as gathered from here as well). I also can't use setAllowInvalid, apparently because that would mess with the existing functionality in the spreadsheet that's not in my control. – Karthik Sivasubramaniam Commented Oct 29, 2019 at 10:31
• 1 Yeah, I don't think a JavaScript error is getting raised here at all, which is why try/catch isn't working. You'll need to research how to intercept validation errors (if you can even do that). Good luck! – T.J. Crowder Commented Oct 29, 2019 at 10:40

 |  Show 1 more ment

2 Answers 2

Sorted by: Reset to default 6

I was able to reproduce your problem.

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

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

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  mented
  }
  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 remended 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

本文标签： javascriptTrycatch not working as expected in Google Apps ScriptStack Overflow