Getting script to run from drop down menu in sheet with multiple tabs

Question

Hopefully I am in the right place. I am working on building a maintenance program and I am having some trouble. What I have is a work order form created in sheets, I have a script wrote that will save a copy of the template under a new name (tied to a cell value in the sheet), a folder on drive. I also have this script scheduling the work order on a google calendar. This code is below.

function Create() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); // Get spreadsheet
  var id = ss.getId(); // Get spreadsheet ID
  var sstocopy = DriveApp.getFileById(id); //Get spreadsheet with DriveApp
  var sheet = ss.getActiveSheet(); // Grab the sheet in order to find the name of the new spreadsheet 
  to be created
  var sheet_name = sheet.getRange("C4").getValue(); // Get the value, in this case: Project Urn
  var folder = DriveApp.getFolderById("1D9gWUuHTPbWkzpSOp5vQdg-K8N6kUGo6"); // Get the folder where 
  the sheet needs to be placed.
  sstocopy.makeCopy(sheet_name,folder); // Make the copy of the sheet in that folder.

  var eventCal = 
  CalendarApp.getCalendarById("c_4um01s3eqvq7d5kqi0ga4i1098@group.calendar.google.com") ;
   var eventname = sheet_name;
   var eventstart = sheet.getRange("E3").getValue();
 var eventstop = sheet.getRange("E4").getValue();
  eventCal.createAllDayEvent(eventname, eventstart, eventstop);
  } 

Now this code works great, I originally had a clickable button on the sheet to run this code, and that worked great from the computer. The sticky part is that I want to be able to run this off the mobile app from a Ipad. So I have been trying to set up a drop down menu in order to run the script. My sheets file has several pages/tabs, and I think this is what is giving me issues. I am assuming that I need to call the right sheet but I can't seem to get it to work.

The first code I tried was this (except my drop down is in F3)

function onEdit(e) {
  if (e.range.getA1Notation() == 'A1') {
    if (/^\w+$/.test(e.value)) {        
      eval(e.value)();
      e.range.clear();
    }
  }
}
    

But I get an undefined error for e.range.getA1Notation. I've tried some other things but I don't really know what I'm doing, as my understanding is pretty basic, and its been a long time since I really did anything like this. Any help would be appreciated.

Answer 1

Here's one way you can use checkboxes as button's to run functions. I used an installable onEdit(e) trigger for this purpose so that it could perform functions that require permission..

function onMyEdit(e) {
  e.source.toast('Entry');
  console.log(JSON.stringify(e));//use this to see all of the features in the event object
  const sh=e.range.getSheet(); 
  if(sh.getName()=="Your Sheet Name" && e.range.columnStart==1 && e.value=="TRUE" ) {
    e.range.setValue('FALSE');
    switch(e.range.rowStart) {
      case 1:
        e.source.toast('function 1');
        break;
      case 2:
        e.source.toast('function 2');
    }
  }
}

I don't have any function attached the idea is that where I have e.source.toast('function 1'); you would replace that whole line with a function call. And this is setup to run off of only one page but that could be modified if desired.

Animation:

As you can see it can be setup so that the checkbox is reset every time and instead of the toast showing up your selected function would show up. You can always put labels next to the checkboxes for the function names.

And this example uses datavalidation to create a dropdown function selection process. It's important to keep in mind that the onEdit() function has to complete in 30 seconds.

function onMyEdit(e) {
  e.source.toast('Entry');
  console.log(JSON.stringify(e));
  const sh=e.range.getSheet(); 
  if(sh.getName()=="Your Sheet Name" && e.range.columnStart==1 && e.value=="TRUE" ) {
    e.range.setValue('FALSE');
    switch(e.range.rowStart) {
      case 1:
        e.source.toast('function 1');
        break;
      case 2:
        e.source.toast('function 2');
    }
  }
  if(sh.getName()=="Your Sheet Name" && e.range.columnStart==2 && e.range.rowStart==1 && e.value) {
    switch(e.value) {
      case '1':
        e.source.toast('function 1');
        break;
      case '2':
        e.source.toast('function 2');
    }
    e.range.setValue('');
  }
}

Animation:

Event Object