How to debug Google Apps Script (aka where does Lo

Question

In Google Sheets, you can add some scripting functionality. I'm adding something for the onEdit event, but I can't tell if it's working. As far as I can tell, you can't debug a live event from Google Sheets, so you have to do it from the debugger, which is pointless since the event argument passed to my onEdit() function will always be undefined if I run it from the Script Editor.

So, I was trying to use the Logger.log method to log some data whenever the onEdit function gets called, but this too seems like it only works when run from the Script Editor. When I run it from the Script Editor, I can view the logs by going to View->Logs...

I was hoping I'd be able to see the logs from when the event actually gets executed, but I can't figure it out.

How do I debug this stuff?

Answer 1

Currently you are confined to the container bound nature of using scripts within docs. If you create a new script inside outside of docs then you will be able to export information to a google spreadsheet and use it like a logging tool.

For example in your first code block

function setCheckboxes() {

    // Add your spreadsheet data
    var errorSheet = SpreadsheetApp.openById('EnterSpreadSheetIDHere').getSheetByName('EnterSheetNameHere');
    var cell = errorSheet.getRange('A1').offset(errorSheet.getLastRow(),0);

    // existing code
    var checklist = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("checklist");
    var checklist_data_range = checklist.getDataRange();
    var checklist_num_rows = checklist_data_range.getNumRows();

    // existing logger
    Logger.log("checklist num rows: " + checklist_num_rows);

   //We can pass the information to the sheet using cell.setValue()
    cell.setValue(new Date() + "Checklist num rows: " + checklist_num_rows);

When I'm working with GAS I have two monitors ( you can use two windows ) set up with one containing the GAS environment and the other containing the SS so I can write information to and log.

Answer 2

A little hacky, but I created an array called "console", and anytime I wanted to output to console I pushed to the array. Then whenever I wanted to see the actual output, I just returned console instead of whatever I was returning before.

    //return 'console' //uncomment to output console
    return "actual output";
}

Answer 3

I am having the same problem, I found the below on the web somewhere....

Event handlers in Docs are a little tricky though. Because docs can handle multiple simultaneous edits by multiple users, the event handlers are handled server-side. The major issue with this structure is that when an event trigger script fails, it fails on the server. If you want to see the debug info you'll need to setup an explicit trigger under the triggers menu that emails you the debug info when the event fails or else it will fail silently.

Answer 4

Just as a notice. I made a test function for my spreadsheet. I use the variable google throws in the onEdit(e) function (I called it e). Then I made a test function like this:

function test(){
var testRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(GetItemInfoSheetName).getRange(2,7)
var testObject = {
    range:testRange,
    value:"someValue"
}
onEdit(testObject)
SpreadsheetApp.getActiveSpreadsheet().getSheetByName(GetItemInfoSheetName).getRange(2,6).setValue(Logger.getLog())
}

Calling this test function makes all the code run as you had an event in the spreadsheet. I just put in the possision of the cell i edited whitch gave me an unexpected result, setting value as the value i put into the cell. OBS! for more variables googles gives to the function go here: https://developers.google.com/apps-script/guides/triggers/events#google_sheets_events

Answer 5

It's far from elegant, but while debugging, I often log to the Logger, and then use getLog() to fetch its contents. Then, I either:

• save the results to a variable (which can be inspected in the Google Scripts debugger—this works around cases where I can't set a breakpoint in some code, but I can set one in code that gets executed later)
• write it to some temporary DOM element
• display it in an alert

Essentially, it just becomes a JavaScript output issue.

It grossly lacks the functionality of modern console.log() implementations, but the Logger does still help debug Google Scripts.

Answer 6

As far as I can tell, you can't debug a live event from google docs, so you have to do it from the debugger, which is pointless since the event argument passed to my onEdit() function will always be undefined if I run it from the Script Editor.

True - so define the event argument yourself for debugging. See How can I test a trigger function in GAS?

I was trying to use the Logger.log method to log some data whenever the onEdit function gets called, but this too seems like it only works when run from the Script Editor. When I run it from the Script Editor, I can view the logs by going to View->Logs...

True again, but there is help. Peter Hermann's BetterLog library will redirect all logs to a spreadsheet, enabling logging even from code that is not attached to an instance of the editor / debugger.

If you're coding in a spreadsheet-contained script, for example, you can add just this one line to the top of your script file, and all logs will go to a "Logs" sheet in the spreadsheet. No other code necessary, just use Logger.log() as you usually would:

Logger = BetterLog.useSpreadsheet();