CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Yaniv Aflalo
Yaniv Aflalo

Reputation: 249

Object does not allow properties to be added or changed - installable edit trigger

I am getting the error in the title for the function below. In shorts- the function checks if, for a specific month, all titles for a customer promotion articles were filled. If so- the order status in a master sheet for this customer + specific month is changed to "Yes". If titles are erased - the order status is changed to blank.

How can I change the code so it will work without giving an error?

Thanks!

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;
        }
      }
    }
  }
}

Upvotes: 0

Views: 55

Answers (1)

Diego
Diego

Reputation: 9571

In the conditional for the "Inactive customers" sheet, you have the typo sheet.getRange(row,5).getValue = 'Promotion articles'. That should be .getValue() == 'Promotion articles'.

Also, looks like you could probably improve the performance of this code if you adopted batch operations.

Upvotes: 3

Related Questions