0
Follow
0
View

Error with previously functional custom app script for google sheets

lianmaocc1 注册会员
2023-01-25 19:51

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)

dingran0916 注册会员
2023-01-25 19:51
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();
    }
  }
}
dorothyzhang81 注册会员
2023-01-25 19:51

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.