3
\$\begingroup\$

I have multiple onEdit app script functions in my Google Spreadsheet which work individually but I cannot work out where to put the brackets to nest them. This is my code which is a little messy and probably needs a tidy up.

// Cut Employees Left from Unit Standards sheet and paste in Unit Standards - Employees Left sheet
function onEdit(e) {
  var ss = e.source;
  var sheet = ss.getActiveSheet();
  var sheetName = "Unit Standards"
  var range = e.range;
  var editedColumn = range.getColumn();
  var editedRow = range.getRow();
  var column = 2;
  var date = range.getValue();
  // Object.prototype.toString.call(date) === '[object Date]' --> checks if value is date
  // editedColumn == column && editedRow > 4 --> checks if edited cell is from 'Date Left'
  // sheet.getName() == sheetName --> checks if edited sheet is 'Unit Standards'
  if(Object.prototype.toString.call(date) === '[object Date]' && editedColumn == column && editedRow > 4 && sheet.getName() == sheetName) {
    var numCols = sheet.getLastColumn();
    var row = sheet.getRange(editedRow, 1, 1, numCols).getValues();
    var destinationSheet = ss.getSheetByName("Unit Standards - Employees Left");
    // Get first empty row:
    var emptyRow = destinationSheet.getLastRow() + 1;
    // Copy values from 'Unit Standards'
    destinationSheet.getRange(emptyRow, 1, 1, numCols).setValues(row);
    sheet.deleteRow(editedRow);
    sheet.hideColumns(column);
  }
  //Dependent Dropdowns for Event/Incidents Sheet
   {
    var range = e.range;
  var editedRow = range.getRow();

  var spreadsheet = SpreadsheetApp.getActive();
  var dropdownSheet = spreadsheet.getSheetByName("Dropdown Lists");
  var eventsSheet = spreadsheet.getSheetByName("Events/Incidents");

  var baseSelected = eventsSheet.getRange('C' + editedRow).getValue();
  var column;

  switch (baseSelected) {
     case 'EBOP': column = 'A'; break;
    case 'Tauranga': column = 'B'; break;
    case 'Palmerston North': column = 'C'; break;
    case 'Kapiti': column = 'D'; 
  }
  var startCell = dropdownSheet.getRange( column +'4');
  var endCellNotation = startCell.getNextDataCell(SpreadsheetApp.Direction.DOWN).getA1Notation();
  var ruleRange =  dropdownSheet.getRange(startCell.getA1Notation() + ':' + endCellNotation);

  var dropdown1 = eventsSheet.getRange('D' + editedRow);
  var dropdown2 = eventsSheet.getRange('E' + editedRow);

  var rule1 = SpreadsheetApp.newDataValidation().requireValueInRange(ruleRange).build();
  var rule2 = SpreadsheetApp.newDataValidation().requireValueInRange(ruleRange).build();

  dropdown1.setDataValidation(rule1);
  dropdown2.setDataValidation(rule2);
     }    
    }
    if (ss.getSheetName() == tabValidation) {
      var lock = LockService.getScriptLock();
      if (lock.tryLock(0)) {
        autoid_(ss);
        lock.releaseLock();
      }
    }

  }  
}

// Auto ID for Event/Incident Sheet
function autoid_(sheet) {
  var data = sheet.getDataRange().getValues();
  if (data.length < 2) return;
  var indexId = data[1].indexOf('ID');
  var indexDate = data[1].indexOf('Event/Incident Date');
  if (indexId < 0 || indexDate < 0) return;
  var id = data.reduce(
    function(p, row) {
      var year =
        row[indexDate] && row[indexDate].getTime
          ? row[indexDate].getFullYear() % 100
          : '-';
      if (!Object.prototype.hasOwnProperty.call(p.indexByGroup, year)) {
        p.indexByGroup[year] = [];
      }
      var match = ('' + row[indexId]).match(/(\d+)-(\d+)/);
      var idVal = row[indexId];
      if (match && match.length > 1) {
        idVal = match[2];
        p.indexByGroup[year].push(+idVal);
      }
      p.ids.push(idVal);
      p.years.push(year);
      return p;
    },
    { indexByGroup: {}, ids: [], years: [] }
  );

  // Logger.log(JSON.stringify(id, null, '  '));
  var newId = data
    .map(function(row, i) {
      if (row[indexId] !== '') return [row[indexId]];
      if (isNumeric(id.years[i])) {
        var lastId = Math.max.apply(
          null,
          id.indexByGroup[id.years[i]].filter(function(e) {
            return isNumeric(e);
          })
        );
        lastId = lastId === -Infinity ? 1 : lastId + 1;
        id.indexByGroup[id.years[i]].push(lastId);
        return [
          Utilities.formatString(
            '%s-%s',
            id.years[i],
            ('000000000' + lastId).slice(-3)
          )
        ];
      }
      return [''];
    })
    .slice(1);
  sheet.getRange(2, indexId + 1, newId.length).setValues(newId);
}

/**
 *
 * @param {any} n
 * @return {boolean}
 */
function isNumeric(n) {
  return !isNaN(parseFloat(n)) && isFinite(n);
}
\$\endgroup\$
0
\$\begingroup\$

To put together "multiple onEdit" functions consider to have one function with a descriptive name for each of them, then call them from the actual onEdit function

function onEdit(e){
  copyRow(e);
  setDataValidations(e);
}

Also you could save some code lines by removing lines like

  1. var ss = e.source and var spreadsheet = e.source;. Instead of ss and spreadsheet use e.source.
  2. var range = e.range;. Instead of range use e.range.
  3. var editedColumn = range.getColumn();. Instead of editedColumn use e.range.columnStart.
  4. var editedRow = range.getRow();. Instead of editedRow use e.range.rowStart.
  5. var date = range.getValue(); Instead of date use e.value

Bear in mind that if multiple cells are edited

  • e.value will return undefined
  • e.range.getValue() will return the value of the to top-left cell.
  • e.range.getColumn() will return the column number of the to top-left cell.
  • e.range.getRow() will return the row number of the to top-left cell.

Considering the above you might want to include some validations before calling / executing changes to your spreadsheet.

\$\endgroup\$
1

Not the answer you're looking for? Browse other questions tagged or ask your own question.