Script if Checkbox is true

Question

I am learning Google Apps Script and have received a lot of help from Stack Overflow - thank you to everyone that has helped. Such a good site for us learners.

I am struggling with onEdit functions because, from what I have learned, you can only have one. I, therefore, have 4 very different functions all nested under one onEdit(e) script function. All 4 complete very different commands when different cells are edited in different sheets.

The following script is nested in my onEdit(e) function and worked until I added another one into the nest. This script copies certain cells in a row to another row in another sheet when a checkbox in the source sheet is checked true.

// Copy and paste from Events/Incidents sheet to Vehicle Damage sheet
 {
  var range = e.range;
  var sheet = range.getSheet();
  var row = range.getRow();
  var column = range.getColumn();
  var sourcesheetname = "Events/Incidents";
  var checkbox = range.getValue();
  if (sheet.getName() == sourcesheetname && column == 24 && row > 3 && checkbox == true) {
    var targetsheetname = "Vehicle Damage";
    var target = e.source.getSheetByName(targetsheetname);
    var numCols = sheet.getLastColumn();
    var values = sheet.getRange(row, 1, 1, numCols).getValues()[0];
    values.splice(17)
    values.splice(8, 8)
    values.splice(5, 1)
    values.splice(3, 1); // Removing undesired values
    var lastRow = target.getLastRow();
    var lastCol = target.getLastColumn();
    target.appendRow(values); // Append new row  
  }

This is my whole onEdit(e) script:

// 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 = 4;
  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);
  }
  
  
// Copy and paste from Events/Incidents sheet to Vehicle Damage sheet
 {
  var range = e.range;
  var sheet = range.getSheet();
  var row = range.getRow();
  var column = range.getColumn();
  var sourcesheetname = "Events/Incidents";
  var checkbox = range.getValue();
  if (sheet.getName() == sourcesheetname && column == 24 && row > 3 && checkbox == true) {
    var targetsheetname = "Vehicle Damage";
    var target = e.source.getSheetByName(targetsheetname);
    var numCols = sheet.getLastColumn();
    var values = sheet.getRange(row, 1, 1, numCols).getValues()[0];
    values.splice(17)
    values.splice(8, 8)
    values.splice(5, 1)
    values.splice(3, 1); // Removing undesired values
    var lastRow = target.getLastRow();
    var lastCol = target.getLastColumn();
    target.appendRow(values); // Append new row  
  }
 
//SOP Internal Audit Required CheckBox if True
{
  var range = e.range
  var sheet = range.getSheet();
  var row = range.getRow();
  var column = range.getColumn();
  var sourcesheetname = "SOP Register";
  var checkbox = range.getValue();
  if (sheet.getName() == sourcesheetname && column == 5 && row > 3 && checkbox == true) {
    sheet.showColumns(6,2);
    sheet.getRange("F3").activate();
  }

// Copy and paste from SOP Register sheet to Internal Audit sheet
 {
  var spreadsheet = e.source;
  var sheet = spreadsheet.getActiveSheet();
  var sourcesheetname = "SOP Register"
  var range = e.range;
  var sheet = range.getSheet();
  var row = range.getRow();
  var column = range.getColumn();
  var editedColumn = range.getColumn();
  var editedRow = range.getRow();
  var column = 7;
  var date = range.getValue();
  if(Object.prototype.toString.call(date) === '[object Date]' && editedColumn == column && editedRow > 3 && sheet.getName() == sourcesheetname) {
    var targetsheetname = "Internal Audit Register";
    var target = e.source.getSheetByName(targetsheetname);
    var numCols = sheet.getLastColumn();
    var values = sheet.getRange(row, 1, 1, numCols).getValues()[0];
    values.splice(9) //Up to and including column I
    values.splice(7, 1) //Remove column H
    values.splice(2, 3); //Keep columns all columns and leave out columns C, D & E 
    var lastRow = target.getLastRow();
    var lastCol = target.getLastColumn();
    values.unshift("SOP");
    target.appendRow(values); // Append new row  
    sheet.hideColumns(6,2);
    
  }
  }
}
}
}//End of onEdit Functions

//Dependent Dropdowns for Event/Incidents Sheet  
  {
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var tablists = 'Dropdown Lists';
    var tabValidation = 'Events/Incidents';
    var ss = spreadsheet.getActiveSheet();
    var datass = spreadsheet.getSheetByName(tablists);
    var activeCell = ss.getActiveCell();
    if (
      activeCell.getColumn() == 3 &&
      activeCell.getRow() > 1 &&
      ss.getSheetName() == tabValidation
    ) {
      activeCell
        .offset(0, 1, 1, 2)
        .clearContent()
        .clearDataValidations();
      var base = datass.getRange(2, 1, 1, 5).getValues();
      var baseIndex = base[0].indexOf(activeCell.getValue()) + 1;
      Logger.log(baseIndex);
      if (baseIndex != 0) {
        //Dynamic dropdown for 'Event Recorded By'
        var validationRange = datass.getRange(3, baseIndex, 150);
        var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
        activeCell.offset(0, 1).setDataValidation(validationRule);
         //Dynamic dropdown for 'Employee'
        var validationRange2 = datass.getRange(4, baseIndex, 150);
        var validationRule2 = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange2).build();
        activeCell.offset(0, 2).setDataValidation(validationRule2);
         }
    }
    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);
}

I have tried to use a different function but nothing seems to work. Is there another way to trigger the same result without using onEdit(e)?

When your script in your question is one of 4 very different functions all nested under one onEdit(e) script function, I think that when you provide the whole script and the detail of your goal and the detail information about ` nothing seems to work`, it will help users understand about your issue and think of the solution. — Tanaike
– Tanaike, Commented May 7, 2020 at 4:53
Have you tried using conditionals in the on edit and if the condition is applied to run one of the 4 functions? — Kessy
– Kessy, Commented May 7, 2020 at 8:29
Thank you for replying. I noticed that an answer has already been posted. I think that it will resolve your issue. — Tanaike
– Tanaike, Commented May 7, 2020 at 11:56

Cooper · Accepted Answer · 2020-05-07 05:04:19Z

2

function onEdit(e) {
  var sh=e.range.getSheet();
  if (sh.getName() == "Events/Incidents" && e.range.columnStart==24 && e.range.rowStart>3 && e.value == 'TRUE') {
    var tsh = e.source.getSheetByName("Vehicle Damage");
    var values=sh.getRange(e.range.rowStart,1,1,26).getValues()[0];
    values.splice(8, 8)
    values.splice(5, 1)
    values.splice(3, 1);
    tsh.appendRow(values);
  }
  if(sh.getName()=="Some other Sheet") {
    //do some other stuff
  }
}

answered May 7, 2020 at 5:04

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Script if Checkbox is true

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related