1

I have the following var:

var isEventLinkBlank = ssa.getSheetByName("Tracker").getRange(index, 13).isBlank();

It's used to check if a certain column is blank or not, which I then use within an if statement. The if statement is perfect, but I want to figure out the else statement where it is the case that the column is actually filled.

else {
        
ss.toast("New date found and updated!", "Notice", 25);

const viewInvite = sheet.getRange(index, 13).getFormula(); 
// or const viewInvite = '=HYPERLINK("https://www.google.com/calendar/u/0/r/eventedit/';
const getEventId  = viewInvite.substring(viewInvite.lastIndexOf("r/") + 2, viewInvite.lastIndexOf("/"));
Logger.log(getEventId);
      
var timeOfEvent = Utilities.base64Decode(getEventId.split("=")).getStartTime();
isEventLinkBlank = interview_date.setValue(timeOfEvent);
}

When the column is filled, there will be a formula that is sitting there:

=HYPERLINK("https://www.google.com/calendar/u/0/r/eventedit/XXXXXXXXXXXXXXXXX","View Invite")

So far the code posted above should:

  1. read that formula
  2. get the XXXXXXXX portion only
  3. decode that XXXXXXXX portion (which is both the event ID and calendar ID from what I decoded)
  4. split the event ID away from the calendar ID in what is decoded
  5. utilize the decoded event ID to get the start time of the event (the date)
  6. and finally setValue that start time in the interview_date variable (which is a specified column).

But it spits out "undefined" and I've tried to rework it several times.

FYI the lastIndexOf solution was provided by a user but I can't seem to understand why "r/" is written, etc.

Thank you

usernametaken
  • 69
  • 6
  • I think that in your script, when `=HYPERLINK("https://www.google.com/calendar/u/0/r/eventedit/XXXXXXXXXXXXXXXXX","View Invite")` is used for `const getEventId = viewInvite.substring(viewInvite.lastIndexOf("r/") + 2, viewInvite.lastIndexOf("/"));`, `eventedit` is returned. So I cannot understand about your goal. I apologize for this. Which do you want to retrieve from the formula `eventedit` or `XXXXXXXXXXXXXXXXX`? – Tanaike Feb 19 '21 at 08:07
  • @Tanaike I want it to get the XXXXXXX part of the formula. Then I want that XXXXXX decoded and the event ID within that XXXXXXX split from the calendar ID that's encoded with it. Then I want that event ID's getStartTime! – usernametaken Feb 19 '21 at 08:25
  • @Tanaike I got that portion from the answer here: https://stackoverflow.com/questions/66270670/how-to-split-a-string-and-get-the-calendar-event-id – usernametaken Feb 19 '21 at 08:33

1 Answers1

1

I believe your goal as follows.

  • You want to retrieve the value of XXXXXXXXXXXXXXXXX from the text of =HYPERLINK("https://www.google.com/calendar/u/0/r/eventedit/XXXXXXXXXXXXXXXXX","View Invite").
  • You want to retrieve the start time of the event on a Google calendar using the retrieved value of XXXXXXXXXXXXXXXXX.

Modification points:

  • In your script, when =HYPERLINK("https://www.google.com/calendar/u/0/r/eventedit/XXXXXXXXXXXXXXXXX","View Invite") is used for const getEventId = viewInvite.substring(viewInvite.lastIndexOf("r/") + 2, viewInvite.lastIndexOf("/"));, the value of eventedit is retrieved. In this case, XXXXXXXXXXXXXXXXX cannot be retrieved. So at first, it is required to modify this script.
  • At Utilities.base64Decode(getEventId.split("=")).getStartTime();, Utilities.base64Decode() returns the byte array. So .getStartTime() cannot be used.

When above points are reflected to your script, it becomes as follows.

Modified script:

From: 
const viewInvite = sheet.getRange(index, 13).getFormula(); 
// or const viewInvite = '=HYPERLINK("https://www.google.com/calendar/u/0/r/eventedit/';
const getEventId  = viewInvite.substring(viewInvite.lastIndexOf("r/") + 2, viewInvite.lastIndexOf("/"));
Logger.log(getEventId);
      
var timeOfEvent = Utilities.base64Decode(getEventId.split("=")).getStartTime();
isEventLinkBlank = interview_date.setValue(timeOfEvent);
To: 
const viewInvite = sheet.getRange(index, 13).getFormula();  // or const viewInvite = '=HYPERLINK("https://www.google.com/calendar/u/0/r/eventedit/XXXXXXXXXXXXXXXXX","View Invite")'

const getEventId = viewInvite.match(/eventedit\/(\w+)/);
if (getEventId && getEventId.length == 2) {
  const eventId = Utilities.newBlob(Utilities.base64Decode(getEventId[1])).getDataAsString().split(" ");

  const calendarId = "###";  // Please set your calendar ID here.

  const timeOfEvent = CalendarApp.getCalendarById(calendarId).getEventById(eventId[0]).getStartTime();
  Logger.log(eventId[0]);
  
  isEventLinkBlank = interview_date.setValue(timeOfEvent);
}

Note:

  • In this modified script, it supposes that your interview_date can be used for putting values and viewInvite is the value like HYPERLINK("https://www.google.com/calendar/u/0/r/eventedit/XXXXXXXXXXXXXXXXX","View Invite"). Please be careful this.

References:

Tanaike
  • 181,128
  • 11
  • 97
  • 165