How to debug OnEdit event function in google sheets appscript?

Question

I have worked in windows forms app C#, uwp apps. As far those are concerned, the debugging is quite easy, I put a breakpoint where I need to pause execution to see the current values of the variables where I put breakpoint, I check the values by either mouseover or using autos. But I am new to Sheets app script, and tried to find how to debug. but could not get. Here is by onEdit event function.

function onEdit(e) {      
    var range = e.range;
    // range.setNote('Last modified: ' + new Date());
    // range.setBackground("green");
    Logger.log("hello this is onEdit");

    if(range.getNumRows() === 1 && range.getNumColumns() === 1 ) 
    {      
        if (range.getValue = "Done")
        {
         range.setValue("Weekly");
        }
    }
}

I put this function inside code.gs under files section. It works well. But If I put a breakpoint, and edit anywhere in the sheets, the breakpoint is not called. If I click the 'Debug'(Debug the selected function) menu, the debugging works. but the parameter e is not defined, since the OnEdit function is not called from sheets by cell edit.

I am clueless how to debug the onEdit function, by editing a cell ?

I just use `e.source.toast()` commands in different places to figure out what's going on. But you can run it from another function by creating an event object which is pretty easy. — Cooper, Sep 29 '21 at 15:39
if you want to create an event object try running `function onEdit(e) {Logger.log(JSON.stringify(e));}` and look at executions after editing and then you'll know what you need to create the event object. — Cooper, Sep 29 '21 at 15:41

score 2 · Answer 1 · answered Sep 29 '21 at 17:13

When you use onEdit() and you changes a value in spreadsheet, Apps Script will pass an event object to onEdit() function, typically called e. The event object contains information about the context that caused the trigger to fire.

The only way you can debug an event object is to print the value of e after an event was made. You can view each event by clicking the Executions tab of your Apps Script.

Example:

Code:

function onEdit(e) {
  Logger.log(e.range.getA1Notation());
}

Output:

References:

score 2 · Answer 2 · answered Sep 29 '21 at 17:47

2

Just in case, here is the structure of an event object in SpreadsheetApp:

var e = {
    "authMode": {object},
    "range": {
      "columnStart": int,
      "rowStart": int,
      "rowEnd": int,
      "columnEnd": int
    },
    "source": {Spreadsheet_object},
    "oldValue": "string",
    "user": {
      "nickname": "string",
      "email": "string"
    },
    "value": "string"
  };

answered Sep 29 '21 at 17:47

Yuri Khristich

13,448
2
8
23

I don't think that will result in loading e.range with a Range Object – Cooper Sep 29 '21 at 17:51
@Cooper , probably so. Nevertheless I'm using this structure as a simply short cheat-sheet pretty often. ) Congrats with 40.000, by the way! – Yuri Khristich Sep 29 '21 at 17:59

score 0 · Answer 3 · answered Sep 29 '21 at 14:57

What might be helpful as an intermediate solution is create a dummy e(I think it's an object) in your function like call it from a dummy function.

function dummy()
{
  var dummye = [];  //create a temp value by executing Logger.log(e) in onEdit
  // and by executing once from actual sheet which will run onEdit(e) and print it.

  onEdit(dummye);
}

function onEdit(e) {    
    //Logger.log(e);  //take this value to create dummye
    
    var range = e.range;
    // range.setNote('Last modified: ' + new Date());
    // range.setBackground("green");
    Logger.log("hello this is onEdit");

    if(range.getNumRows() === 1 && range.getNumColumns() === 1 ) 
    {      
        if (range.getValue = "Done")
        {
         range.setValue("Weekly");
        }
    }
}

How to debug OnEdit event function in google sheets appscript?

3 Answers3

Example:

References: