Error with previously functional custom app script for google sheets-CodePudding

I reached out to the google app script community to request assistance with a custom script for my google sheets spreadsheet some time ago. It worked great and as intended when the original workbook was in use, however in the past couple months since I last employed the script something has changed. I am receiving an error in the debug of the script that seems to be the culprit. Script below:

function onEdit(e){
  if(e.value == "TRUE")
    e.source.getActiveSheet().getRange(e.range.rowStart,e.range.columnStart 1).setValue(new Date());
  else if(e.value == "FALSE")
    e.source.getActiveSheet().getRange(e.range.rowStart,e.range.columnStart 1).clearContent();
  else return;
}

Upon debugging it seems VALUE in e.value is the issue resulting as undefined. Error type 'Cannot read property 'value' of undefined' in the first if argument. I am unsure if there are any other bugs, however it appears this is the only one.

The purpose of this script is to enter the date of the day the checkbox in the adjacent cell is ticked true.

CodePudding user response：

As you are running this code from the script editor (by clicking the 'play' button), the error is normal.

An onEdit script is triggered (automatically) if edits take place in the spreadsheet. The script captures that edit (event) and uses that event-object (e) in the script. If you try to run the function from the script-editor no such event is captured (because no edit took place) and hence all properties of that event are undefined.

If the onEdit script does not give you results as it should, as is currently happening with some of mine, then you can set a Trigger that will encourage the script to run when an edit is done.

CodePudding user response：

Sitting n the tub typing from my phone, so I may not get things exactly right, but it should be enought to get you started.

When using a simple trigger like onEdit you can use SpreadsheetApp.toast() to display a pop-up in the bottom right corner to view variables and results of test conditions.

Try: SpreadsheetApp.toast(e.range.offset(0,1).getA1Notation()) to see if gets you the date cell. In which case you want: e.range.offset(0,1).setValue(new Date()) Try: SpreadsheetApp.toast(e.value === "TRUE") to see if your evaluation returns as you expect.

If you use 2 toasts in a row the second with cover the first. So add a delay. As I recall it's Utilities.delay(1000)

CodePudding user response：

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == "Your Sheet Name" && e.range.columnstart == 'your check box column') {
    if (e.value == "TRUE") {
      e.range.offset(0,1).setValue(new Date());
    } else if (e.value == "FALSE") {
      e.range.offset(0,1).clearContent();
    }
  }
}