I Can't get Apps Script to print the date in a cell

Justin Urry 0
Viewed 38

I have a spreadsheet that tracks when students "cash in" prizes they've won. I wrote a script that checks to see if the neighboring checkbox is checked and should print the month and day when it was checked.

function installableonEdit(e) {
  var s = SpreadsheetApp.getActiveSheet(); //gets active spreadsheet
  if (s.getName() == 'Main') { //determines whether the active spreadsheet is the 'Main' or not
    var value = (typeof e.value == "object" ? e.range.getValue() : e.value);
    if (e.range.columnStart == 3 && value == "TRUE" || e.range.columnStart == 8 && value == "TRUE" || e.range.columnStart == 13 && value == "TRUE" || e.range.columnStart == 18 && value == "TRUE" || e.range.columnStart == 23 && value == "TRUE") {
      e.range.offset(0, 1).setValue(new Date()).setNumberFormat("mm/dd"); //checks to see if 'out' checkboxes have been ticked, if yes, inputs current time in next column over
    }    
  }
}

I installed a trigger to make it work when it is edited but nothing prints in the cell when I click a textbox. What am I doing wrong?

Here's a link to my spreadsheet

https://docs.google.com/spreadsheets/d/1IW28Aho5oYbrrLKmyAWiq-vYTSLq-JXIerfDAzR3gCI/edit?usp=sharing

google-sheets google-apps-script
2 Answers

Your script is correctly written with one exception: the e.range.columnStart values you've given are off by 1. One way to see this (and in general, to debug a triggered function) is to add this line at the top of your function:

console.log(JSON.stringify(e,null,2))

then click one of the checkboxes and view the execution logs. You'll see a (nicely formatted) copy of the entire trigger event e. For example, when clicking a checkbox in column D, you'll see this:

{
  "authMode": "FULL",
  "oldValue": "false",
  "range": {
    "columnEnd": 4,
    "columnStart": 4,
    "rowEnd": 5,
    "rowStart": 5
  },
  "source": {},
  "triggerUid": "12835616",
  "user": {
    "email": "...",
    "nickname": "..."
  },
  "value": "TRUE"
}

In general, Google Apps Script (and the Google Sheets API) uses 1-based indexing to refer to columns on a sheet (column A has index 1 and so on), whereas Javascript uses 0-based indexing for arrays (and therefore for the 2D array we get when we call range.getValues())--this can cause confusion when translating between the two. If you were expecting 0-based indexing that may have been the cause of the problem.

Just add 1 to all of those column references and your script should work correctly.

Aaron Dunigan AtLee0

Dealing with insertions due to changes in multiple columns with a fixed offset

function onEdit(e) {
  const sh = e.range.getSheet();
  const cols = [4,9,14,19,24];//columns where checkboxes are
  const idx = cols.indexOf(e.range.columnStart)
  if(sh.getName() == "Main" && ~idx && e.value == "TRUE") {
    e.range.offset(0,1).setValue(new Date()).setNumberFormat("mm/dd");
  }
}

Bitwise Not(~)

user30hu0
Related