javascript - 将 IF 添加到 COLUMNTOCHECK var

Question

我正在尝试在我的 var COLUMNTOCHECK 行中添加一个“if”语句，以便它检查列中的特定值，而不仅仅是单元格中的某些内容。到目前为止，这是我的脚本。我想让它只有在上述列包含“UC Phone”字样时才会激活时间戳。我希望它检查第 5、6 或 7 列中的情况是否属实。

var COLUMNTOCHECK = 5;

var DATETIMELOCATION = [0,13];

var SHEETNAME = ('December 17, 2018')

function check() {

  return true;
}

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();

  if( sheet.getSheetName() == SHEETNAME ) { 
    var selectedCell = ss.getActiveCell();

    if( selectedCell.getColumn() == COLUMNTOCHECK)
    { 
      var dateTimeCell = selectedCell.offset(DATETIMELOCATION[0],DATETIMELOCATION[1]);
      dateTimeCell.setValue(new Date());

      }
  }
}

我对 Google 表格中的脚本编写非常陌生，所以我一直在研究我在网上找到的代码。

标签： javascriptvariablesif-statementgoogle-sheetsgoogle-sheets-formula

Answer 1

我知道您用作此代码基础的教程。它非常有用，但它也有潜入深渊的效果。

您的 OnEdit(e) 函数称为“简单”触发器。
“e”参数使您能够捕获和使用关于编辑事件的七条信息。在 Google 文档中有一个信息列表。
我为每个值创建了一个 Logger.log 条目，以便您可以查看返回的信息。

function onEdit(e) {
    // so_53748818
    // Setup spreadsheet and target sheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();

    // setup some variables
    var SHEETNAME = ('53748818');
    var COLUMNTOCHECK = 5;
    var valuetocheck = "UC Phone";
    var DATETIMELOCATION = [0, 13];
    var counter = 0;

    // get and display the (e) event information
    var debug_e = {
        authMode: e.authMode,
        range: e.range.getA1Notation(),
        source: e.source.getId(),
        user: e.user,
        value: e.value,
        oldValue: e.oldValue
    };
    Logger.log("AuthMode: " + debug_e.authMode);// DEBUG
    Logger.log("Range: " + debug_e.range);// DEBUG
    Logger.log("Source: " + debug_e.source);// DEBUG
    Logger.log("User: " + debug_e.user);// DEBUG
    Logger.log("user email" + debug_e.user.getEmail());// DEBUG
    Logger.log("Value: " + debug_e.value);// DEBUG
    Logger.log("Old value: " + debug_e.oldValue);// DEBUG
    //Logger.log("AuthMode: "+debug_e.authMode+", Range: "+debug_e.range+", source: "+debug_e.source+", user: "+debug_e.user+", value: "+debug_e.value+", old value: "+debug_e.oldValue);// DEBUG


    // get the row number
    var editRow = e.range.getRow();
    Logger.log("the Row edited was " + editRow);// DEBUG

    // Assumes that the edit field is in Column D
    var checkrange = sheet.getRange(editRow, COLUMNTOCHECK, 1, 3)
    Logger.log("checkrange = " + checkrange.getA1Notation());// DEBUG
    var checkvalues = checkrange.getValues();

    // loop through the values in columns E, F & G
    for (var i = 0; i < 3; i++) {
        Logger.log("i: " + i + ", value: " + checkvalues[0][i]);// DEBUG
        if (checkvalues[0][i] == valuetocheck) {
          counter = counter + 1; // add one to the counter for each value matched
        }
    }
    if (counter == 3) { // if counter is 3, then all the cells contained the value
        Logger.log(" the counter is " + counter + ", so the date is inserted in column 13 (Q)");// DEBUG
        var dateTimeCell = e.range.offset(DATETIMELOCATION[0], DATETIMELOCATION[1]);
        dateTimeCell.setValue(new Date());
    } else { // the else is not strictly required, but included so that the counter value can be displayed for debug purposes.
        Logger.log(" the counter is " + counter + ", so the date is NOT inserted in column 13 (Q)");// DEBUG
    }

}