Search code examples
google-apps-scriptgoogle-sheetstriggersonchange

Google Sheets App Script: e.range is null when onChange function is run via a trigger


This question got me banned from asking questions... what did I do wrong?! I really don't understand.

I am bashing my head against my keyboard.

This seems like it should be simple... but I continue to get the error:

TypeError: Cannot read property 'getNumColumns' of undefined** at onChange(checkBoxes:5:20)

The goal would be to click a check box, get a date stamp, but I need to also check the column header (Row 4) to determine what function to run next... However, I can not get that far because I need to know the column of the cell that triggered the function.

  function onChange(e) {
    const range = e.range;
    Logger.log(range.getNumColumns());
  };

/**Trigger:
     Head:= Deployment
     Event:= From spreadsheet - On change
     Function:= onChange
     Error rate:= 100% */

Technically speaking I can use activecell but I am concerned that users are unreliable, what happens if they check a check box in col:1 then click to col:2, will activecell return col:1, or 2?

I have tried a verity of combinations to solve this, but each time it says "Cannot read property 'getNumColumns' of undefined"

I have tried the following:

e.range.columnStart

Solution

  • function onEdit(e) {
      e.range // is the location of the edit it could be more than one cell if it's something like a paste.
      e.range.getSheet() // is the sheet 
      e.range.columnStart // is the column of the edited cell
      e.range.rowStart // is the row of the edited cell
      e.range.columnEnd // is the last column in e.range
      e.range.rowEnd // is the last row in e.range
      e.source // is the active spreadsheet
      e.oldValue // is the value before the change
      e.value // is the value after the edit
      Logger.log(JSON.stringify(e)); // will provide a description of the entire event object
    }
    

    There are others like authorization level and user information sometimes