谷歌脚本在编辑或删除后检测空单元格 [英] 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();
  }  

我的测试表的屏幕:

推荐答案

您要针对编辑空单元格的情况和删除单元格值的情况运行每个函数.如果我的理解是正确的，那么这个解决方法如何?我认为可能有几种解决方法.因此，请将此视为其中之一.在此解决方法中，假设您使用的是 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.value 和 e.oldValue 都包含在 e 中.

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

从有值的单元格中删除值的情况

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

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

• 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().
• 事件对象

如果我误解了你的问题，我很抱歉.

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 提到了这一点.

• 从具有值的单元格中删除值的情况
  • e.value 和 e.oldValue 都不包含在 e 中.
  • 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.
            }
            

            注意:

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

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