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

Question

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

Provide debugging details. See https://stackoverflow.com/questions/16089041/how-can-i-test-a-trigger-function-in-gas — TheMaster, Jul 10 '22 at 18:42
Did you read my answer there? I already told you how to debug. — TheMaster, Jul 12 '22 at 14:12

Cooper · Accepted Answer · 2022-07-11T20:45:17.327

1

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

edited Jul 11 '22 at 20:45

answered Jul 10 '22 at 19:11

Cooper

59,616
6
23
54

Tried the given code (added sheet name), but the script is not running (searchData) – Humming Bird Jul 11 '22 at 04:39
Do I need to change any settings! – Humming Bird Jul 11 '22 at 04:43
Sheet name, checkboxes in D1 and E1 with values of "TRUE" and "FALSE" – Cooper Jul 11 '22 at 11:28
Yes! Sheet name is given, inserted checkboxes in D1 and E1, checked; still script not running – Humming Bird Jul 11 '22 at 11:59
Do you have an e in the function definition? – Cooper Jul 11 '22 at 15:42
I am getting a toast D1 and E1 but SearchData function is not running. :( – Humming Bird Jul 11 '22 at 17:19
You mean onEdit(e) - YES – Humming Bird Jul 11 '22 at 17:19
Search data not running is not very helpful. Are getting an error? Are getting the alert? – Cooper Jul 11 '22 at 17:30
SearchData function is running with a button click. But I am trying the same function using CheckBox (because I can use it to search data from mobile/tablet). While using the code provided by you I am not getting any error or alert. Nothing happens. Please help, attached the working sheet link too – Humming Bird Jul 11 '22 at 17:49
It could by that you need an installable onEdit(e) because GmailApp requires permissions – Cooper Jul 11 '22 at 18:53
Please Can you explain in more detail?. What changes should do in the script or should I enable some settings? – Humming Bird Jul 11 '22 at 19:18
1

You don't need to change the script. Just change the name and create an installable trigger onEdit trigger – Cooper Jul 11 '22 at 20:36
See last part of my answer. – Cooper Jul 11 '22 at 20:46
Sorry! :( still nothing happening. > I changed the Sheet name and added the given installable trigger function. The script is not running. But still button click runs the script. Is there any alternate method of calling the function using mobile – Humming Bird Jul 12 '22 at 02:46
I can't help you without more debugging information – Cooper Jul 12 '22 at 02:51
added debugging information - Screenshots for your reference – Humming Bird Jul 12 '22 at 05:30
Personally, I would have preferred having you try to figure out why searchdata is not running. – Cooper Jul 12 '22 at 13:42
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/246378/discussion-between-humming-bird-and-cooper). – Humming Bird Jul 12 '22 at 15:12
1

You haven't created an installable trigger in your code example – Cooper Jul 12 '22 at 15:18
Yes, Now its after creating an installable trigger. thank you. – Humming Bird Jul 17 '22 at 09:49

jeanjai77 · Answer 2 · 2022-07-10T19:28:51.920

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

Tried the given code, but the script is not running (searchData) — Humming Bird, Jul 11 '22 at 04:41

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

2 Answers2