Export Google Calendar events with hyperlink to sheets

Question

I am exporting Google Calendar events in the description is hyperlinked text. When I export to Sheets I get

Test event :<a href="https://xxxx/following"> Registration</a>

I need to either export as a true hyperlinked text or convert the cell text in Google Sheets back to hyperlinked text.

Here is a [sample export] (https://docs.google.com/spreadsheets/d/1ojtLP1HOXrH4q3Rr7T4KRR86rtC_XM1F2KhgQClhDUA/edit#gid=0)

Clarifying I would be willing to split the column into 2 columns to get the following result to appear as a hyperlink. Test event : Registration

I think you need to clarify exactly what your goal is. Maybe show "current result" vs. "expected result". Would you consider breaking the link into two columns: first column contains the link text ("Test event: Registration") and the next column contains the URL (which will be clickable in the sheet) https://xxxx/following ? — dwmorrin

dwmorrin dwmorrin · Accepted Answer · 2019-04-24T03:46:29

Here is an example function that will split the link, formatted as you describe, into separate text and URL columns:

/**
 * Splits an html <a> tag into its text content and URL components
 * @param {string} Format is: "Optional leading text: <a href="url">Link Text</a>"
 * @returns {string[][]} 1 row by 2 column string of text and url
 * @customfunction
 */
function LINKSPLIT(string) {
  var split = /(^[^<]*)(<a.*a>)/.exec(string);
  try {
    var document = XmlService.parse(split[2]);
  } catch (error) {
    throw new Error("Format is: \"Optional leading text: <a href=\"url\">Link Text</a>\"");
  }
  var content = document.getAllContent();
  if (content.length != 1) {
    throw new Error("XML parser found multiple tags. Please enter a single <a> tag only.");
  }
  return [[split[1] + content[0].getText(), content[0].getAttribute("href").getValue()]];
}

This is written as a custom function for Google Sheets, i.e. something you can test in a cell with =LINKTEST(ROW:COLUMN). If you are importing the calendar events via script, then you can take this example and incorporate it into your script so that it parses the links during the import process.

Export Google Calendar events with hyperlink to sheets

1 Answers