Trigger a funtion when a check box is true - google sheet script-CodePudding

I am trying to trigger a function if the (D1) checkbox is true

Instead of a button, I need a checkbox. Button click works only on desktop but not on Mobile/Tablet.

I tried referring to this similar post. But still the script is not running Calling function when checking checkbox - Google Sheets

this code is not working for me. Please guide.

function onEdit() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  var range = sheet.getActiveRange()
  if (range.isChecked()) {
    if (range.getA1Notation() == "D1") {
      searchData()
    } else if (range.getA1Notation() == "E1") {
      submitData()
    }
    range.uncheck()
  }
}

for your reference, below is the function I am trying to trigger/call

**// Please run this function if the D1 is true.**
function searchdata() {
  const srcSpreadsheetId = "1QL0jaNts2YRkZTlxmS0bk7V1fVVHBsJFmxS5C05PEmA"; 
  const srcSheetName = "DataSheet";
  const dstSheetName = "UserForm";

  // Retrieve values from source sheet and create an array and search value.
  const dstSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const dstSheet = dstSpreadsheet.getSheetByName(dstSheetName);
  const search = dstSheet.getRange("B1").getValue();

  // Search the value.
  const srcSpreadsheet = SpreadsheetApp.openById(srcSpreadsheetId);
  const srcSheet = srcSpreadsheet.getSheetByName(srcSheetName);
  const range = srcSheet.getRange("A2:A"   srcSheet.getLastRow()).createTextFinder(search).findNext();
  if (!range) {
    SpreadsheetApp.getUi().alert('UserForm Number Not Found');
  }

also, this is the working file link https://docs.google.com/spreadsheets/d/1NY_ckzEWxU7DCGro5tTqzpiOi6iG5PAQFxpZg0OKodY/edit?usp=sharing

CodePudding user response：

Try this:

function onEdit(e) {
  var range = e.range;
  var activeSheet = e.source.getActiveSheet();
  if (activeSheet.getName() != "UserForm") return;
  if (range.isChecked()) {
    if (range.getA1Notation() == "D1") {
      searchData();
    } else if (range.getA1Notation() == "E1") {
      submitData();
    }
    range.uncheck();
  }
}

When using the onEdit() function, make sure to take advantage of the on edit event object (e) as it can save you from potential bugs.

You might also want to reduce the amount of nested if statements by using this code:

function onEdit(e) {
  var range = e.range;
  var activeSheet = e.source.getActiveSheet();
  if (activeSheet.getName() != "UserForm") return;
  if (!range.isChecked()) return;
  if (range.getA1Notation() == "D1") {
    searchData();
  } else if (range.getA1Notation() == "E1") {
    submitData();
  }
  
  range.uncheck();
}

CodePudding user response：

Try this:

function onEdit(e) {
  const sh = e.range.getSheet();
  if(sh.getName() == "Your sheet name" && e.range.rowStart == 1 && e.value == "TRUE") {
    if(e.range.columnStart == 4) {
      searchData();
    }
    if(e.range.columnStart == 5) {
      submitData();
    }
    e.range.setValue("FALSE");
  }
}

This works for me

function onMyEdit(e) {
  const sh = e.range.getSheet();
  if(sh.getName() == "Sheet0" && e.range.rowStart == 1 && e.value == "TRUE") {
    if(e.range.columnStart == 4) {
      e.source.toast("D1")
      //searchData();
    }
    if(e.range.columnStart == 5) {
      e.source.toast("E1")
      //submitData();
    }
    e.range.setValue("FALSE");
  }
}

function createInstallableTrigger(funcname="onMyEdit") {
  if(ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == funcname).length == 0 ) {
    ScriptApp.newTrigger().forSpreadsheet(SpreadsheetApp.getActive()).onEdit().create();
  }
}