How to debug Google Apps Script (aka where does Logger.log log to?)

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?

Journeyman

Same issue here - accepted answer doesn't answer it but gives lots of plain wrong info. – Goodman

It seems like they fixed this now. As long as you open Script Editor from your spreadsheet, leave that tab open when you run the stuff in your Sheet. Then go back to the script tab and it will have the Logging info in it. – Marchant

tldr; copy, paste, and run Logger.log('firstLog');MailApp.sendEmail({to:'[email protected]',subject: "subject here ^_^",body: Logger.getLog()}); – Maidy

Maby you should change the accepted answer or add a note that Stackdriver Logging is available. – Trillby

I see my logs at script.google.com/home/executions – Interrogatory

Answer 1

UPDATE:

As written in this answer,

• Stackdriver Logging is the preferred method of logging now.

• Use console.log() to log to Stackdriver.

---

Logger.log will either send you an email (eventually) of errors that have happened in your scripts, or, if you are running things from the Script Editor, you can view the log from the last run function by going to View->Logs (still in script editor). Again, that will only show you anything that was logged from the last function you ran from inside Script Editor.

The script I was trying to get working had to do with spreadsheets - I made a spreadsheet todo-checklist type thing that sorted items by priorities and such.

The only triggers I installed for that script were the onOpen and onEdit triggers. Debugging the onEdit trigger was the hardest one to figure out, because I kept thinking that if I set a breakpoint in my onEdit function, opened the spreadsheet, edited a cell, that my breakpoint would be triggered. This is not the case.

To simulate having edited a cell, I did end up having to do something in the actual spreadsheet though. All I did was make sure the cell that I wanted it to treat as "edited" was selected, then in Script Editor, I would go to Run->onEdit. Then my breakpoint would be hit.

However, I did have to stop using the event argument that gets passed into the onEdit function - you can't simulate that by doing Run->onEdit. Any info I needed from the spreadsheet, like which cell was selected, etc, I had to figure out manually.

Anyways, long answer, but I figured it out eventually.

---

EDIT:

If you want to see the todo checklist I made, you can check it out here

(yes, I know anybody can edit it - that's the point of sharing it!)

I was hoping it'd let you see the script as well. Since you can't see it there, here it is:

function onOpen() {
  setCheckboxes();
};

function setCheckboxes() {
  var checklist = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("checklist");
  var checklist_data_range = checklist.getDataRange();
  var checklist_num_rows = checklist_data_range.getNumRows();
  Logger.log("checklist num rows: " + checklist_num_rows);

  var coredata = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("core_data");
  var coredata_data_range = coredata.getDataRange();

  for(var i = 0 ; i < checklist_num_rows-1; i++) {
    var split = checklist_data_range.getCell(i+2, 3).getValue().split(" || ");
    var item_id = split[split.length - 1];
    if(item_id != "") {
      item_id = parseInt(item_id);
      Logger.log("setting value at ("+(i+2)+",2) to " + coredata_data_range.getCell(item_id+1, 3).getValue());
      checklist_data_range.getCell(i+2,2).setValue(coredata_data_range.getCell(item_id+1, 3).getValue());
    }
  }
}

function onEdit() {
  Logger.log("TESTING TESTING ON EDIT");
  var active_sheet = SpreadsheetApp.getActiveSheet();
  if(active_sheet.getName() == "checklist") {
    var active_range = SpreadsheetApp.getActiveSheet().getActiveRange();
    Logger.log("active_range: " + active_range);
    Logger.log("active range col: " + active_range.getColumn() + "active range row: " + active_range.getRow());
    Logger.log("active_range.value: " + active_range.getCell(1, 1).getValue());
    Logger.log("active_range. colidx: " + active_range.getColumnIndex());
    if(active_range.getCell(1,1).getValue() == "?" || active_range.getCell(1,1).getValue() == "?") {
      Logger.log("made it!");
      var next_cell = active_sheet.getRange(active_range.getRow(), active_range.getColumn()+1, 1, 1).getCell(1,1);
      var val = next_cell.getValue();
      Logger.log("val: " + val);
      var splits = val.split(" || ");
      var item_id = splits[splits.length-1];
      Logger.log("item_id: " + item_id);

      var core_data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("core_data");
      var sheet_data_range = core_data.getDataRange();
      var num_rows = sheet_data_range.getNumRows();
      var sheet_values = sheet_data_range.getValues();
      Logger.log("num_rows: " + num_rows);

      for(var i = 0; i < num_rows; i++) {
        Logger.log("sheet_values[" + (i) + "][" + (8) + "] = " + sheet_values[i][8]);
        if(sheet_values[i][8] == item_id) {
          Logger.log("found it! tyring to set it...");
          sheet_data_range.getCell(i+1, 2+1).setValue(active_range.getCell(1,1).getValue());
        }
      }

    }
  }

  setCheckboxes();
};

Journeyman

Pff, logging will only be visible in mail? That kinda makes all these debugging and logging features useless, because effectively we cant use real data from the spreadsheet. – Vereeniging

No. As the author said, logging is available in the log (Script Editor, View, Log or Ctrl-Enter) – Perforated

@Perforated Yes, logging is available in the Script Editor. However, when a script depends on the event argument and the event argument is not available in the Script Editor, it effectively means that the developers of these types of scripts have no real-time way of accessing the logs. – Brainchild

I couldn't comment before, so I provied an answer below, but: IF you have the script editor open and YOU trigger an event in the open spreadsheet, you can go back to the script editor browser instance and see the information in the Logs. It works well as long as you don't have to test something under a user who cannot open the script or one you cannot log in as. – Tallow

I would highly recommend anyone viewing this to take a look at Mogsdad's answer below. In my opinion it should be marked as the correct answer instead of this. It is not impossible to do what OP is asking for. – Horologe

Outdated answer. Kindly include console.log() – Whitethorn

This answer is outdated and should not be seen as the accepted answer. Stackdriver Logging is available and works like a charm. Take a look at random-parts answer! – Trillby

Answer 2

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();

Betjeman

Outdated.console.log() should be the best answer now – Whitethorn

Answer 3

2017 Update: Stackdriver Logging is now available for Google Apps Script. From the menu bar in the script editor, goto: View > Stackdriver Logging to view or stream the logs.

console.log() will write DEBUG level messages

Example onEdit() logging:

function onEdit (e) {
  var debug_e = {
    authMode:  e.authMode,  
    range:  e.range.getA1Notation(),    
    source:  e.source.getId(),
    user:  e.user,   
    value:  e.value,
    oldValue: e. oldValue
  }

  console.log({message: 'onEdit() Event Object', eventObject: debug_e});
}

Then check the logs in the Stackdriver UI labeled onEdit() Event Object to see the output

Skip

The original question specifically asks about Logger.log. How is this different from console.log which you use? I'm very new to the tools so just trying to figure out what everything is. – Perennate

there's no "View" menu in the script editor. – Inactive

@Inactive The "view" menu is in the legacy editor, you can switch to it by clicking on "Use classic editor" on the top-right, but it says "This version of the Apps Script editor will be completely turned off by September 2022". In the new editor, there is just "Execution log" button on the top – Grig

I still cannot find where the realtime logs are going.. – Gownsman

Answer 4

I've gone through these posts and somehow ended up finding a simple answer, which I'm posting here for those how want short and sweet solutions:

1. Use console.log("Hello World") in your script.
2. Go to https://script.google.com/home/my and select your add-on.
3. Click on the ellipsis menu on Project Details, select Executions.

4. Click on the header of the latest execution and read the log.

Cinchonidine

This is the basic "Stackdriver logging" for Google Apps Scripts created after April 2019 (which is when accessing the Google Cloud Project for "automatic" projects behind Apps Scripts became impossible). If you change the GCP for an Apps Script project, then the regular Stackdriver logging answers apply. – Exterminatory

I only see direct executions here (i.e. those for which I clicked "run" in the script editor), but I do not see executions caused by a change of data in the sheet. How do I debug those? – Cable

I haven’t tried that out sorry. I would imagine that if a change in the sheet triggered a function and the function had a log, the log would show along. Changes would always be user-induced right? – Cinchonidine

Yes, I would have imagined that, too. Unfortuantely that is not what happens :-( Changes to data do trigger my function, but console.log() messages aren't shown in the Stackdriver log. I tried adding an on-change activator, but that invokes my function without parameters :-( – Cable

Executions shows as empty, despite having logged data – Inactive

omg thanks so much. I saw that list of executions. The last click step eluded me and wasted so much time! Google should add an eye icon or something I must say – Armijo

Answer 5

For Apps Script projects that are tied to a single Sheet (or doc) — in 2022 — there is no View menu like other answers suggest. Instead you need to look in the Executions menu on the left sidebar to see the executions of your onSelectionChange function (or any other function), from there you can click REFRESH until your console.log messages appear.

Fjeld

Answer 6

If you have the script editor open you will see the logs under View->Logs. If your script has an onedit trigger, make a change to the spreadsheet which should trigger the function with the script editor opened in a second tab. Then go to the script editor tab and open the log. You will see whatever your function passes to the logger.

Basically as long as the script editor is open, the event will write to the log and show it for you. It will not show if someone else is in the file elsewhere.

Tallow

there is no view menu. – Inactive

You are correct. The editor has gone through a lot of change since this reply was made. More recent posts should be followed. If you switch to the Classic Editor you will see the View pulldown menu. In the new editor you can expand each item in the Executions screen to see what has been passed to the logs for that specific execution. – Tallow

Answer 7

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.

Stephi

Hmm, yeah, I did run into this - it emailed me a whole bunch of errors from my scripts the next morning. I did end up figuring it out though (posting my own answer now) – Journeyman

Answer 8

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";
}

Morvin

in js console.log('smth') works perfectly, but how about in GAS? – Ashok

console.log won't work simply because GAS are not scripts running in the same web page as your spreadsheet, they are scripts handled by google's app engine so you have to follow their unwieldy Logger.log debugger or use a hack like mine – Morvin

how about your array 'console'? When I just returned console how do you output it? – Ashok

Sorry I didn't explain very well, but essentially spreadsheet functions return the value to the cell, so if you return your "console" you will see everything you logged inside your spreadsheet cell – Morvin

Answer 9

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.

Macaulay

Answer 10

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

Ripple

Answer 11

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.

Quartern

Answer 12

The dev console will log errors thrown by the app script, so you can just throw an error to get it logged as a normal console.log. It will stop execution, but it might still be useful for step by step debugging.

throw Error('hello world!');

will show up in the console similarly to console.log('hello world')

Instanter

Answer 13

just debug your spreadsheet code like this:

...
throw whatAmI;
...

shows like this:

Leoine

I think that you should mention that the image shows how a custom function shows an error but the OP mentions that he is using a simple trigger (onEdit) – Cartomancy