Google脚本在编辑或删除后检测到空单元格 [英] google script detect empty cell after edit or delete

问题描述

我的代码有问题.看起来不错，但是它不起作用，我真的不知道为什么.我尝试了一切.

I have a problem with my code. It seems fine but it doesn't work and I really don't know why. I tried everything.

我想将我的google excel与我的google日历同步.一切正常，但是现在我只编辑空白单元格时想在日历中创建事件:

I want sync my google excel with my google calendar. Everything work but now I want make event in calendar when I edit only blank cell:

if (e.oldValue == null) { // also tried if(e.oldValue == undefinded)
var date = new Date(dayRange);
cal.createAllDayEvent(
  e.value,
  date,
)

}

它工作正常.但是接下来，我要在删除单元格时从日历中删除事件(因此它再次为空):

And it works fine. But next I want delete event from calendar when I delete cell (so it is blank again):

else if(e.value==null){
  var events = cal.getEvents(date, {
  search: ss.getRange(row,2)
  });  
  for (i in events){
    events[i].deleteEvent();
}

在我删除"日历中的单元格之后，我得到了下一个带有眨眼标题的事件，但我不知道为什么会创建该事件.看来这否则"就行不通了.我真的不知道为什么我阅读了所有示例，代码看起来还可以.

After i "deleted" cell in calendar I get next event with blink title and I don;t know why this event is creating. It seems like this "else if" doesn't work. I really don't know why. I read all example and code seems ok.

这是我创建触发器的方式:

This is how I create my trigger:

function createEditTrigger() {
   var ss = SpreadsheetApp.getActive();
   ScriptApp.newTrigger('ToCalendar')
   .forSpreadsheet(ss)
   .onEdit()
   .create();
 }     

我将非常感谢所有建议.

I will be very grateful for all the advice.

编辑

完整代码:

function ToCalendar(e) {  
    var ss = SpreadsheetApp.getActiveSheet();
    var cal = CalendarApp.getCalendarById("myID");

    var range = e.range;
    var column = range.getColumn();
    var row = range.getRow();

    var day = ss.getRange(1,column);
    var dayRange = day.getValues();

    if ((e.value != null) && (e.oldValue == null)) {
        var date = new Date(dayRange);
         cal.createAllDayEvent(
         ss.getRange(row,2).getValue(),
         date,
         {
             description: e.value,//ss.getRange(row, column).getValue(),
         }
        )
     }

    //If we edit cell:

     else if(e.oldValue!=undefined){
        var events= cal.getEventsForDay(date,{search: e.oldValue});
        var ev= events[0];
        Logger.log(ev);
        ev.deleteEvent();
        cal.createAllDayEvent(ss.getRange(row,2).getValue(),date, 
        {description: ss.getRange(row, column).getValue()})


   // If we delete cell   



    else if((e.value == null) && (e.oldValue != null)){
         var events = cal.getEvents(date, {
         search: ss.getRange(row,2)
       });  
         for (i in events){
            events[i].deleteEvent();
          }

  }

创建触发器:

function createEditTrigger() {
   var ss = SpreadsheetApp.getActive();
   ScriptApp.newTrigger('ToCalendar')
   .forSpreadsheet(ss)
   .onEdit()
   .create();
  }  

和我的测试表的屏幕:

And screen of my test sheet:

推荐答案

您要针对编辑空单元格和删除单元格值的情况运行每个函数.如果我的理解是正确的，该解决方法如何?我认为可能有几种解决方法.因此，请将此视为其中之一.在这种解决方法中，假定您正在使用onEdit(e).更改单元格时，onEdit(e)中的e如下更改.

You want to run each function for the case for editing an empty cell and the case for deleting a value of cell. If my understanding is correct, how about this workaround? I think that there may be several workarounds. So please think of this as one of them. In this workaround, it supposes that you are using onEdit(e). When the cell is changed, e of onEdit(e) is changed as follows.

1. 在将值编辑为空单元格的情况下

1. In the case for editing a value to empty cell

• e.value包含在e中.
• e.oldValue不包含在e中.

• e.value is included in e.
• e.oldValue is NOT included in e.

在用另一个值覆盖一个值的单元格的情况下

In the case for overwriting a cell with a value by other value

• e中都包含e.value和e.oldValue.

• Both e.value and e.oldValue are included in e.

从具有值的单元格中删除值

In the case for deleting a value from a cell with a value

• e均不包含e.value和e.oldValue.

• Both e.value and e.oldValue are NOT included in e.

使用以上结果，为了运行用于编辑空白单元格和删除单元格值的案例的每个功能，可以使用以下示例脚本.

Using above results, in order to run each function for the case for editing an empty cell and the case for deleting a value of cell, you can use the following sample script.

function onEdit(e) {
  if (("value" in e) && !("oldValue" in e)) {
    Logger.log("In the case for editing a value to empty cell")
  }
  if (!("value" in e) && !("oldValue" in e)) {
    Logger.log("In the case for deleting a value from a cell with a value")
  }
}

当然，您也可以使用以下脚本.

Of course, you can also use the following script.

function onEdit(e) {
  if ((e.value != null) && (e.oldValue == null)) {
    Logger.log("In the case for editing a value to empty cell")
  }
  if ((e.value == null) && (e.oldValue == null)) {
    Logger.log("In the case for deleting a value from a cell with a value")
  }
}

注意:

• 如果要运行需要授权的方法，请在onEdit()上安装触发器.

Note :

• If you want to run the methods which require to authorize, please install a trigger to onEdit().
• 事件对象

如果我误解了你的问题，对不起.

If I misunderstand your question, I'm sorry.

已删除语法错误并修改了脚本.在此修改后的脚本中，

The syntax errors are removed and modified your script. In this modified script,

• 编辑空单元格后，将运行if ((e.value != null) && (e.oldValue == null)) { script }中的脚本.
• 当带有值的单元格被值覆盖时，将运行else if(e.oldValue!=undefined) { script }中的脚本.
• 删除具有值的单元格的值时，将运行else if((e.value == null) && (e.oldValue == null)) { script }中的脚本.
• When the empty cell is edited, the script in if ((e.value != null) && (e.oldValue == null)) { script } is run.
• When the cell with a value is overwritten by a value, the script in else if(e.oldValue!=undefined) { script } is run.
• When the value of cell with a value is removed, the script in else if((e.value == null) && (e.oldValue == null)) { script } is run.

function ToCalendar(e) {  
  var ss = SpreadsheetApp.getActiveSheet();
  var cal = CalendarApp.getCalendarById("myID");
  var range = e.range;
  var column = range.getColumn();
  var row = range.getRow();
  var day = ss.getRange(1,column);
  var dayRange = day.getValues();
  if ((e.value != null) && (e.oldValue == null)) { // When the empty cell is edited, this becomes true.
    var date = new Date(dayRange);
    cal.createAllDayEvent(ss.getRange(row,2).getValue(),date,{
      description: e.value,//ss.getRange(row, column).getValue(),
    })

  // In your situation, this might not be required.
  } else if(e.oldValue!=undefined) { // When the cell with a value is overwritten by a value, this becomes true.
    //If we edit cell:
    var events= cal.getEventsForDay(date,{search: e.oldValue});
    var ev= events[0];
    Logger.log(ev);
    ev.deleteEvent();
    cal.createAllDayEvent(ss.getRange(row,2).getValue(),date,{description: ss.getRange(row, column).getValue()})

  } else if((e.value == null) && (e.oldValue == null)) { // When the value of cell with a value is removed, this becomes true.
    // If we delete cell
    var events = cal.getEvents(date, {
      search: ss.getRange(row,2)
    });  
    for (i in events){
      events[i].deleteEvent();
    }
  }
}

已添加:

已确认事件对象的规范已更改.确认了以下修改. @I'-'I 提到了这一点.

Added:

It was confirmed that the specification of the event object is changed. The following modification was confirmed. This was mentioned by @I'-'I.

• 从具有值的单元格中删除值的情况
  • e不包含e.value和e.oldValue.
  • In the case for deleting a value from a cell with a value
    • Both e.value and e.oldValue are NOT included in e.
    • 从具有值的单元格中删除值的情况
      • e.value和e.oldValue包含在e中.
        • e.value是类似于{"oldValue":"deleted value"}的对象.
        • e.oldValue是类似于"deleted value"的字符串.
        • In the case for deleting a value from a cell with a value
          • e.value and e.oldValue are included in e.
            • e.value is an object like {"oldValue":"deleted value"}.
            • e.oldValue is a string like "deleted value".

            通过此修改，当从具有该值的单元格中删除该值时，可以通过以下脚本进行检查.

            By this modification, when the value was removed from a cell with the value, this can be checked by the following script.

            if (e.value.oldValue) {
                // value was removed from cell.
            } else {
                // value is NOT removed from cell.
            }
            

            注意:

            • 在将值编辑为空单元格的情况下，以及用一个值用其他值覆盖单元格的情况下，每个结果均不会更改.

            这篇关于Google脚本在编辑或删除后检测到空单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！