我正在尝试为Google电子表格制作一个脚本,但我陷入了困境,我无法让它发挥作用 .

我的目的是创建一个脚本来检测我正在修改的单元格,以便稍后查看它是否在特定范围内,并根据特定单元格中写入范围来修改该范围 .

这是我目前所做的脚本:

function onEdit(e){
  var curDate = Utilities.formatDate(new Date(), "GMT+01:00", "hh:mm");
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Test2');
  var range = e.range;
  //var colInicio = e.range.getColumn();
  //var colInicio = e.getColumn();
  //var filaInicio = e.range.getRow();
  //var filaInicio = e.getRow();
  //var activeRange = e.getActiveRange();
  //var ref = e.getA1Notation(); // Notation tipe A1 or A1:B2
  var ref = range.getA1Notation;
  var colIndex = ref.getColIndex;
  var rowIndex = ref.getRowIndex;

  var watchRange1 = { 
    top : 11,         // start row
    bottom : 109,     // end row
    left : 3,        // start col
    right : 11,        // end col
  };

  var watchRange2 = { 
    top : 11,         // start row
    bottom : 109,     // end row
    left : 13,        // start col
    right : 21,        // end col
  };

  if(rowIndex >= watchRange1.top && rowIndex <= watchRange1.bottom && 
  colIndex >= watchRange1.left && colIndex <= watchRange1.rigth && 
  e.getValue() != 0){

    var DateCol1 = "K9"; //Cell you want to have the date
    SpreadsheetApp.getActiveSheet().getRange(DateCol1).setValue(curDate); 
   //Write the date in the cell

  }else if (rowIndex >= watchRange2.top && rowIndex <= watchRange2.bottom && 
  colIndex >= watchRange2.left && colIndex <= watchRange2.rigth &&  
  e.getValue() != 0){

    var DateCol2 = "U9"; //Cell you want to have the date
    SpreadsheetApp.getActiveSheet().getRange(DateCol2).setValue(curDate); 
    //Write the date in the cell

  }else{

    var DateCol1 = "K9";
    SpreadsheetApp.getActiveSheet().getRange(DateCol1).setValue("Error");

  };
}

我认为错误发生在if之前,但我无法解决它

错误migth在这一行:

var range = e.range;

在Apps脚本的Google帮助中,他们说这行应该有效:

https://developers.google.com/apps-script/guides/triggers/events#edit

这是我在谷歌表中的第一个脚本,所以我不知道我做错了什么

我不知道某行代码是否错误,但是当我修改特定范围内的单元格时,脚本应该在特定单元格中写入修改日期 .