时间戳

首页 > 解决方案 > 对象不允许添加或更改属性 - 可安装的编辑触发器

google-apps-script - 对象不允许添加或更改属性 - 可安装的编辑触发器

问题描述

我在以下函数的标题中收到错误。简而言之，该功能检查是否在特定月份中填写了客户促销文章的所有标题。如果是这样 - 此客户 + 特定月份的主表中的订单状态更改为“是”。如果标题被删除 - 订单状态更改为空白。

如何更改代码以使其正常工作而不会出错？

谢谢！

function onEdit1(e) {
  var range = e.range;
  var column = range.getColumn();
  var row = range.getRow();
  var sheet = range.getSheet();
  var cell = sheet.getActiveCell();
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var masterSheet = spreadSheet.getSheetByName('Master');
  var masterLastRow = masterSheet.getLastRow();

  if(sheet == masterSheet) {
    // in case user changed cell in the "Ordered articles?" column of a specific month in the master sheet
    if ((column > 7) && (sheet.getRange(1,column).getValue() !== '')) {
      // in case status marked as "Yes"
      if (cell.getValue() == 'Yes') {
        Browser.msgBox('In tab ' + sheet.getRange(1,column).getValue() + ': please verify that ' + sheet.getRange(row,3).getValue() + ' articles were submitted for the customer:' + sheet.getRange(row,1).getValue());
      }
      // in case existing status removed
      else if (cell.isBlank()) {
        Browser.msgBox('Cell cleared. Please verify that the info in tab ' + sheet.getRange(1,column).getValue() + ' matches this change, for the customer ' + sheet.getRange(row,1).getValue());
      }
    }
  }
  // in case user changed a cell of "article title" in a specific month sheet
  else if ((sheet.getName() !== 'Inactive customers') && (column == 1) && (sheet.getRange(row,5).getValue = 'Promotion articles')) {
    // in case added title is not added from top down for a specific customer (specified in column 2)
    if ((sheet.getRange(row,2).getValue() == sheet.getRange(row - 1,2).getValue()) && (sheet.getRange(row - 1,column).isBlank())) {
      Browser.msgBox('Please fill articles titles from top downwareds!');
      cell.setValue('');
    }
    // in any other case
    else {
      var masterColumn;
      // for loop finds the customer row in master sheet
      for (var masterRow = 2; masterRow <= masterLastRow; masterRow++) {
        // in case added title in specific month sheet is for the same customer in current row in master sheet
        if (masterSheet.getRange(masterRow,2).getValue() == sheet.getRange(row,2).getValue()) {
          masterColumn = 8;
          // while loop finds the column for the specific month in master sheet
          while (masterSheet.getRange(1,masterColumn).getValue() !== sheet.getName()) {
            masterColumn++;
          }
          // in case title was erased, not added - status for "Ordered articles?" of specific month is cleared from master sheet
          if (cell.isBlank()) {
            masterSheet.getRange(masterRow,masterColumn).setValue('');
          }
          // in case title was added to the last row of a customer in specific month sheet - status is changed to "Yes" in master sheet
          else if (sheet.getRange(row,2).getValue() !== sheet.getRange(row + 1,2).getValue()) {
            masterSheet.getRange(masterRow,masterColumn).setValue('Yes');
          }
          // force end of for loop, after action was done
          masterRow = masterLastRow;
        }
      }
    }
  }
}

标签： google-apps-scriptgoogle-sheets

解决方案

在“非活动客户”表的条件中，您有错字sheet.getRange(row,5).getValue = 'Promotion articles'。那应该是.getValue() == 'Promotion articles'。

此外，如果您采用批处理操作，看起来您可能会提高此代码的性能。

推荐阅读