0

In my spreadsheet, I have a formula that hyperlinks a calendar event. It looks like this in the sheet:

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

In my script I want it to analyze that cell and split it to specifically grab the XXXXX portion through base64 decode. Then, in that decode is the event ID and the calendar ID, and I want the event ID specifically.

var viewInvite = sheet.getRange(index, 13).getValue();

var beginningPiece = '=HYPERLINK("https://www.google.com/calendar/u/0/r/eventedit/';
var endPiece = '","View Invite")';
var splitEventId = Utilities.newBlob(Utilities.base64Decode(beginningPiece.split("=")[1])).getDataAsString().split(" ")[0];
Logger.log(splitEventId);

How do I grab the event ID from the formula? I tried to work a scenario like above but I can't figure it out

Rubén
  • 34,714
  • 9
  • 70
  • 166
usernametaken
  • 69
  • 6

1 Answers1

1

Explanation:

  • Use getFormula to get the formula from the sheet, otherwise if you use getValue you will get View Invite because that is the value the formula returns.

  • You don't need to use regex, just this simple/classical approach.

Solution:

const viewInvite = sheet.getRange(index, 13).getFormula();
const splitEventId  = viewInvite.substring(
                        viewInvite.lastIndexOf("r/") + 2, 
                        viewInvite.lastIndexOf("/"));
console.log(splitEventId);

Minimal reproducible example:

const viewInvite = '=HYPERLINK("https://www.google.com/calendar/u/0/r/eventedit/';
const splitEventId  = viewInvite.substring(
                        viewInvite.lastIndexOf("r/") + 2, 
                        viewInvite.lastIndexOf("/"));
console.log(splitEventId);
Marios
  • 26,333
  • 8
  • 32
  • 52
  • Thanks for the help! I was going down the regex route but knew there had to be a better way! Now comes another question. If I use the const splitEventId elsewhere, the "event ID" that it's referencing is still encoded base64, right? So I would need to use some decode line to make it readable? The reason is that I need to use getStartTime for the event ID in question, and then that getStartTime result will be used within a setValue to update a column where the date sits. In summary: setValue of getStartTime of splitEventId. Thanks again! – usernametaken Feb 19 '21 at 05:27
  • @usernametaken Sorry I just saw your reply and realized that your comment - question was solved in another thread. Glad the issue was resolved. – Marios Feb 19 '21 at 09:15