Google Sheets - Adding New Rows After First Edit of Specified Columns / Data Validation

1
votes

I've written the script below, so Sheets automatically inserts a new row and copies a formula to that new row.

I've specified that I'd like for this function to run only if changes are made to Columns 2 or 4, but the script runs regardless of the Column number or whether e.oldValue === undefined or not.

Two questions:

1) What is wrong with my code that it is ignoring the Column number and the data validation?

2) If a value is given into a cell and erased, does that cell revert back to undefined or is its value now " "?

Here is a link to the sheet: https://docs.google.com/spreadsheets/d/1m58J7vg_xfkrxK_9pxPSHAlBYULXbqNIiF8H2-KVBgE/edit#gid=1496977189

Code: 

function onEdit(e) {

  var sheet = SpreadsheetApp.getActiveSheet();
  var activeCell = sheet.getActiveCell();
  var activeColumn = activeCell.getColumn();
  var walkoutRange = sheet.getRange(7, 17, 1);
  var walkoutFormula = walkoutRange.getFormulas();
    Logger.log(walkoutFormula);
  var newRange = sheet.getRange((activeCell.getRow() + 1), 17, 1)

  if (sheet.getName() != "Form") {

    if (activeColumn === 2 || 4) {

      if (e.oldValue === undefined) {

        sheet.insertRowAfter(activeCell.getRow());
        newRange.setFormula(walkoutFormula);
      }
    }
  }
};
1
validationgoogle-apps-scripttriggersgoogle-sheets

1 Answers

0
votes

There is an uncaught error in the following line:

if (activeColumn === 2 || 4) {

Should be:

if (activeColumn === 2 || activeColumn === 4) {

The conditional test:

activeColumn === 2 || 4

Is always returning true;

function test() {
  var activeColumn = 9;
  Logger.log('is it true: ' + Boolean(activeColumn === 2 || 4))//returns true

  if (activeColumn === 2 || 4) {
    Logger.log(Boolean(activeColumn === 2 || 4))
  };

};