Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

google apps script - onEdit() doesn't catch all changes

I have this simple trigger script that should add a timestamp in the next column. simple, onEdit doesn't catch all edits. Can I do anything in the settings?

function onEdit(e){
  if (e.value == "TRUE") {e.range.offset(0, 1).setValue(new Date())} 
}

enter image description here

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

About your situation, there is a thread. In this thread, Rubén says that

This is a known limitation of onEdit.

About the direct solution of this issue, it is required to wait for Google's update.

Here, I would like to think of a workaround for your situation. The flow of this workaround is as follows.

This workaround supposes that there are the checkboxes in the range of "F1:F20".

  1. Check whether the edited range is in "F1:F20".
  2. If the edited range is in "F1:F20", retrieve values of "F1:F20" and check each value.
  3. Create an array for putting the result.
  4. Overwite the created array to "F1:F20".

By this, although it might be not perfect, it can be artificially achieved. Please think of this as just one of several workarounds.

Sample script:

function onEdit(e){
  if (e.range.columnStart == 6 && e.range.columnEnd == 6 && e.range.rowStart <= 20) {
    var ckeckboxRange = "F1:F20";
    var date = new Date();
    var range = e.source.getRange(ckeckboxRange);
    var values = range.getValues().map(function(e) {return e[0] === true ? [date] : [""]});
    range.offset(0, 1).setValues(values);
  }
}

Result:

enter image description here

Note:

  • This is a simple sample script. So please modify this for your situation.

If this was not the result you want, I apologize.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...