Is There a Workaround to Allow for an Internal Trigger within a Spreadsheet to Create a Calendar Event?

Question

I recently asked this in an admittedly overly verbose Post (Here), but I created a Google Script that gathers information from within a Google Sheet and formats it into a Google Calendar Entry, and it works, but the hope was to have a "button" within the Sheet that a user could then Trigger the function to happen.

However, due to how Google authorizations work (or my lack of understanding of them), this doesn't seem possible.

So my question is, what options do I have (by either using a different Script to give me authorization, or by some other annoying workaround) to make this actually work how I'd like it to, which is allow whoever had the sheet open to trigger the event from within by running the function internally (as opposed to opening the Script App, which is currently the only way I can make it work).

Any advice?

function climbevent() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Calendar Creation");
  var lr = ss.getLastRow();
  var cal = CalendarApp.getCalendarById("ee0eb69f0021b2b30a29268670a990408e1c3a128a096676428f9333506568a7@group.calendar.google.com");

  var data = ss.getRange("A10:F10").getValues()

  for(var i=0;i<data.length;i++){
   
   var event = cal.createEvent(data[i][0], data[i][1], data[i][2],{location: data[i][3], description: data[i][4],guests: data[i][5]})

  }
  var eventId = event.getId();
  ss.getRange('F13').setValue(eventId);

  var title = event.getTitle();
    if (/Play/.test(title)) {
        event.setColor('9');
      } else if (/Class:/.test(title)) {
        event.setColor('5');
      } else if (/Rally:/.test(title)) {
        event.setColor('3');
      } else {}
}

UPDATE: SOLVED!

Martin provided me with a link below that I was not coming across anywhere else that completely solved the whole thing for me.

Everywhere I looked, I kept finding people link to Google's Installable Trigger explanation, which seemed incredibly lacking on the front of onEdit functionality.

I was really caught up on the fact that the Installable Trigger for onEdit was on any edit, and what I really wanted was the trigger to be specific.

My limited understanding of coding in general led me to think that this version of the Installable Trigger was overreaching, and I needed a different, more focused Trigger.

What I failed to understand (in case anyone ends up in a similar mental trap as me), is that the solution is to create an Installable Trigger for a middle function that nests the desired function is the key.

Installable Trigger logic flow then is:

Trigger: Whenever ANY Edit happens, do function 'X'

X = If Y, then Z

Y = Check specific cell (or range)to see if it has changed (or is of a specific value)

Z = DO Desired Function

This makes logical sense, but I had been looking for (and assuming was possible) that I could JUST create the "onEdit" to focus on the desired cell. It's a shorter chain, but apparently not how this works at all, so I was missing the correct path.

Thanks again, Martin!

Answer 1

What you can do is to set it to run on edit, but as an installable trigger. This way, you make it run by a modification in a cell instead of a button

For example you could have a checkbox in A1, and a function like this with the installable trigger:

function installedEdit(e){
  if(e.range.getA1Notation()=="A1" && e.range.getSheet().getName() == "Calendar Creation") {
   climbevent()
   SpreadsheetApp.flush()
   e.range.setValue(false)
  }
}

What I usually do is have a dropdown with references to functions, and a similar function with if statements that launches the necessary function according to the option selected in the dropdown. Possibly you won't need it now, but just in case so you have the idea!