Formatting of description

[Sc8s]

I find this extension really useful, but I am having an issue getting the formatting of whatever is in the Google calendar events description to stick in Sheets. I understand you probably have to convert it into HTML, but I need some sort of way to easily convert everything back to an easily readable and digestible view. The things in the calendar description include zoom links, Google Drive links, and other kinds of clickable links. There is more than one link in each event description. I need all of them to feed to one cell. How can this be solved?

[GCalToolkit]

Sheets2GCal doesn't currently modify the description at all as doing so would mess up the description in Google Calendar the next time the event was exported.

I'll look into this later today, but I think the best approach will be to create a new column that presents the description data in the way you want it with formatting, clickable links etc.

[Sc8s]

I only need to import Google Cal events, never export. Does this help?

[Sc8s]

"but I think the best approach will be to create a new column that presents the description data in the way you want it with formatting, clickable links etc." right - only way I can find out how to do that would be manually and that defeats the purpose I have for this extension.

and all the AppScripts I try don't convert all the different kinds of description links, or only convert one to a clickable link.

basically I am trying to pull all of my bosses events, every morning, into another tab that will then feed to a google doc. I am just pulling these items: Title Start (Date) Start Time Description*

and Description is where I am stuck. I just need the Descriptions to be as functional as they are in the Cal event. They have important links, that she needs to reference.

Thanks so much for your help!

[Sc8s]

any update? :)

[GCalToolkit]

I'm still working on this - it might be possible to use a Google Docs Add-on to render the HTML as it seems Google removed this option a few years ago... Otherwise I have put together a script that appends clickable links to the end of the description, but it's not quite ready for testing. Soon!

[Sc8s]

awesome - thank you! Looking forward :)

Best,

SC

SC Billings

Executive Assistant & Office Manager

The sleep fitness company

On Wed, Jul 31, 2024 at 6:12 AM GCalTools @.***> wrote:

I'm still working on this - it might be possible to use a Google Docs Add-on to render the HTML as it seems Google removed this option a few years ago... Otherwise I have put together a script that appends clickable links to the end of the description, but it's not quite ready for testing. Soon!

— Reply to this email directly, view it on GitHub https://github.com/GCalToolkit/GCalExtras/issues/4#issuecomment-2260160461, or unsubscribe https://github.com/notifications/unsubscribe-auth/BKEI2WM6AZRK6KJKD7FJK5LZPC2BFAVCNFSM6AAAAABLRJNZFGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDENRQGE3DANBWGE . You are receiving this because you authored the thread.Message ID: @.***>

[Sc8s]

Hi there - incorrect times are being pulled when I do quick imports, btw. Is there a solve to this coming soon along with the link problem?

Best,

SC

SC Billings

Executive Assistant & Office Manager

The sleep fitness company

On Wed, Jul 31, 2024 at 8:45 AM SC (Sarah Caroline) Billings < @.***> wrote:

awesome - thank you! Looking forward :)

Best,

SC

SC Billings

Executive Assistant & Office Manager

The sleep fitness company

On Wed, Jul 31, 2024 at 6:12 AM GCalTools @.***> wrote:

I'm still working on this - it might be possible to use a Google Docs Add-on to render the HTML as it seems Google removed this option a few years ago... Otherwise I have put together a script that appends clickable links to the end of the description, but it's not quite ready for testing. Soon!

— Reply to this email directly, view it on GitHub https://github.com/GCalToolkit/GCalExtras/issues/4#issuecomment-2260160461, or unsubscribe https://github.com/notifications/unsubscribe-auth/BKEI2WM6AZRK6KJKD7FJK5LZPC2BFAVCNFSM6AAAAABLRJNZFGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDENRQGE3DANBWGE . You are receiving this because you authored the thread.Message ID: @.***>

[GCalToolkit]

I'm sorry for the delay. It's proving more complicated than I thought rendering the HTML of Google Calendar descriptions in Google Sheets or Documents.

Google Sheets seem to have updated their display of clickable links (i.e links that have been configured using the Link formatting button in Google Calendar), but there's something you need to do first: if you click in any cell containing GCal clickable links and then leave the cell, the link becomes clickable in Google Sheets. That said, if a link is not clickable in Google Calendar it won't become clickable in Google Sheets.

I'm now working on a new approach to this, and for your problem with incorrect times please open a new Issue with some screenshots showing the problem, and your time zone settings for the Sheet and Calendar.

[Sc8s]

Google Sheets seem to have updated their display of clickable links (i.e links that have been configured using the Link formatting button in Google Calendar), but there's something you need to do first: if you click in any cell containing GCal clickable links and then leave the cell, the link becomes clickable in Google Sheets. That said, if a link is not clickable in Google Calendar it won't become clickable in Google Sheets.

Hmm - I have not found this to be true. Links are being added to GCal and clicking into their Sheets cell, selecting out, and selecting back in is not working for me, unfortunately.

Best,

SC

SC Billings

Executive Assistant & Office Manager

The sleep fitness company

On Sat, Aug 24, 2024 at 7:18 PM GCalTools @.***> wrote:

I'm sorry for the delay. It's proving more complicated than I thought rendering the HTML of Google Calendar descriptions in Google Sheets or Documents.

Google Sheets seem to have updated their display of clickable links (i.e links that have been configured using the Link formatting button in Google Calendar), but there's something you need to do first: if you click in any cell containing GCal clickable links and then leave the cell, the link becomes clickable in Google Sheets. That said, if a link is not clickable in Google Calendar it won't become clickable in Google Sheets.

I'm now working on a new approach to this, and for your problem with incorrect times please open a new Issue with some screenshots showing the problem, and your time zone settings for the Sheet and Calendar.

— Reply to this email directly, view it on GitHub https://github.com/GCalToolkit/GCalExtras/issues/4#issuecomment-2308570414, or unsubscribe https://github.com/notifications/unsubscribe-auth/BKEI2WPFO72GMAMJ4S2Q6B3ZTEIFBAVCNFSM6AAAAABLRJNZFGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGMBYGU3TANBRGQ . You are receiving this because you authored the thread.Message ID: @.***>

[GCalToolkit]

You wrote: "clicking into their Sheets cell, selecting out, and selecting back in is not working for me, unfortunately."

It seems the cursor needed to be positioned immediately after the URL when leaving the cell for this to work. However, I've just submitted an update which should fix this problem - all URLs in the Description should now be clickable. For Sheets you've already imported you can just click "Import Changes and New Events" (even if there are none) and it should reformat the Descriptions.

Unfortunately rendering HTML is currently not possible in Sheets, but if your URLs are just added to Google Calendar events using plain text (not the "Link" button) this avoids cluttering the Sheet with raw HTML, and instead you should now see clickable links, and a list of these links should show up when you hover over a cell.

I don't know how this will work when copied to a Google Doc, but if you can preserve Rich Text in the process the clickable links should also be preserved.

I hope this helps, and please let me know how you get on. The update should already be live, there's no need to update anything as the new version should be available automatically.

[Sc8s]

Hi there,

Thanks for working on this. I noticed, it's sort of working in the original "Import" sheet tab, but when I pull into another tab a selected day and its columns - the clickable links are not clickable anymore.

Best,

SC

SC Billings

Executive Assistant & Office Manager

The sleep fitness company

On Wed, Sep 4, 2024 at 4:24 AM GCalTools @.***> wrote:

You wrote: "clicking into their Sheets cell, selecting out, and selecting back in is not working for me, unfortunately."

It seems the cursor needed to be positioned immediately after the URL when leaving the cell for this to work. However, I've just submitted an update which should fix this problem - all URLs in the Description should now be clickable.

Unfortunately rendering HTML is currently not possible in Sheets, but if your URLs are just added to Google Calendar events using plain text (not the "Link" button) this avoids cluttering the Sheet with raw HTML, and instead you should now see clickable links, and a list of these links should show up when you hover over a cell.

I don't know how this will work when copied to a Google Doc, but if you can preserve Rich Text in the process the clickable links should be preserved.

I hope this helps, and please let me know how you get on. The update should already be live, there's no need to update anything as the new version should be available automatically.

— Reply to this email directly, view it on GitHub https://github.com/GCalToolkit/GCalExtras/issues/4#issuecomment-2328226454, or unsubscribe https://github.com/notifications/unsubscribe-auth/BKEI2WLJPHM2QWRLKTAWASLZU27SLAVCNFSM6AAAAABLRJNZFGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGMRYGIZDMNBVGQ . You are receiving this because you authored the thread.Message ID: @.***>

[Sc8s]

All - there are a ton of incorrect times / dates in tomorrow's pull of info after quick import. I don't want to screenshot here as that's private information thoughm

[GCalToolkit]

"when I pull into another tab a selected day and its columns - the clickable links are not clickable anymore."

This is out of the control of Sheets2GCal and is caused by a bug in Google Sheets which doesn't always correctly identify URLs to make them clickable. This is also why URLs in Sheets2GCal were not automatically clickable.

You should find that if a cell only contains a URL (with no text before it) then the URL does automatically becomes clickable. Also, if you click to enter a cell and position the cursor right at the end of the URL then click outside the cell... the URL becomes clickable. I've tested this thoroughly with another user who has a similar problem to yours - so if you don't see that happening then please check again that you followed the steps correctly.

I should be able to modify the script I wrote for the Descriptions in Sheets2GCal so you can add it to your Sheets under "Extensions > App Script" to make URLs in any column clickable. I'll let you know when that's ready.

---

For the event times, could you contact me by email directly so you don't have to share private data here? The best approach is to reproduce the problem on a test calendar/sheet that you can share with me so I can see all the details. If that's not possible please send screenshots showing Time Zone Settings for the Sheet and Google Calendar, the event details in the full edit window and the entire contents of the Sheet row with the wrong times.

[GCalToolkit]

Ok, here's the code you need. It won't work on Formulas directly, obviously... but once the data is there you can convert the column to values, then select the entire column and run "URL Tools" > "Make URLs Clickable":

Open your Sheet, go to "Extensions > App Script", delete anything in the file and replace it with this code. Once that's done just reload the document and you'll see "URL Tools" in the menu. Grant permissions and you should be able to make any content clickable.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Add a new custom menu to the Google Sheets UI
  ui.createMenu('URL Tools')
    .addItem('Make URLs Clickable', 'convertColumnUrlsToClickable')
    .addToUi();
}

function convertColumnUrlsToClickable() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getActiveRange(); // Gets the currently selected range

  // Iterate through each cell in the selected column
  var numRows = range.getNumRows();
  for (var i = 1; i <= numRows; i++) {
    var cell = range.getCell(i, 1); // Assuming single column selection, we look at the first column of the range
    var cellText = cell.getValue();
    var richTextValue = makeUrlsClickable(cellText);

    if (richTextValue) {
      cell.setRichTextValue(richTextValue); // Apply the rich text with clickable URLs
    }
  }
}

function makeUrlsClickable(cellText) {
  // Use regex to find all URLs in the cell text
  var urlRegex = new RegExp("((http|https)://|www\\.)[a-zA-Z0-9+;:&#%./?=_-]+","ig");
  var urls = [];
  var match;

  // Store all URLs and their positions
  while (match = urlRegex.exec(cellText)) {
    urls.push({url: match[0], start: match.index, end: match.index + match[0].length});
  }

  // If no URLs found, return null
  if (urls.length === 0) {
    return null;
  }

  // Create a new RichTextValueBuilder with the original cell text
  var richTextValueBuilder = SpreadsheetApp.newRichTextValue().setText(cellText);

  // Apply setLinkUrl to each detected URL
  urls.forEach(function(link) {
    richTextValueBuilder.setLinkUrl(link.start, link.end, link.url);
  });

  // Build and return the RichTextValue
  return richTextValueBuilder.build();
}

[Sc8s]

We;re getting close! But it's still not glitching for pulled from another tab. Screen Recording 2024-09-04 at 5.43.08 PM.mov https://drive.google.com/file/d/1Xu13jXrNySSG82_l53vCD9luvvFff2Nj/view?usp=drive_web

Best,

SC

SC Billings

Executive Assistant & Office Manager

The sleep fitness company

On Wed, Sep 4, 2024 at 5:37 PM GCalTools @.***> wrote:

Ok, here's the code you need. It won't work on Formulas directly, obviously... but once the data is there you can convert the column to values, then select the entire column and run "URL Tools" > "Make URLs Clickable":

Open your Sheet, go to "Extensions > App Script", delete anything in the file and replace it with this code. Once that's done just reload the document and you'll see "URL Tools" in the menu. Grant permissions and you should be able to make any content clickable.

function onOpen() { var ui = SpreadsheetApp.getUi(); // Add a new custom menu to the Google Sheets UI ui.createMenu('URL Tools') .addItem('Make URLs Clickable', 'convertColumnUrlsToClickable') .addToUi(); }

function convertColumnUrlsToClickable() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var range = sheet.getActiveRange(); // Gets the currently selected range

// Iterate through each cell in the selected column var numRows = range.getNumRows(); for (var i = 1; i <= numRows; i++) { var cell = range.getCell(i, 1); // Assuming single column selection, we look at the first column of the range var cellText = cell.getValue(); var richTextValue = makeUrlsClickable(cellText);

if (richTextValue) {
  cell.setRichTextValue(richTextValue); // Apply the rich text with clickable URLs
}

} }

function makeUrlsClickable(cellText) { // Use regex to find all URLs in the cell text var urlRegex = new RegExp("((http|https)://|www\.)[a-zA-Z0-9+;:&#%./?=_-]+","ig"); var urls = []; var match;

// Store all URLs and their positions while (match = urlRegex.exec(cellText)) { urls.push({url: match[0], start: match.index, end: match.index + match[0].length}); }

// If no URLs found, return null if (urls.length === 0) { return null; }

// Create a new RichTextValueBuilder with the original cell text var richTextValueBuilder = SpreadsheetApp.newRichTextValue().setText(cellText);

// Apply setLinkUrl to each detected URL urls.forEach(function(link) { richTextValueBuilder.setLinkUrl(link.start, link.end, link.url); });

// Build and return the RichTextValue return richTextValueBuilder.build(); }

— Reply to this email directly, view it on GitHub https://github.com/GCalToolkit/GCalExtras/issues/4#issuecomment-2330189395, or unsubscribe https://github.com/notifications/unsubscribe-auth/BKEI2WKAQC5PNXBH3Z3P5QTZU54RLAVCNFSM6AAAAABLRJNZFGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGMZQGE4DSMZZGU . You are receiving this because you authored the thread.Message ID: @.***>

[GCalToolkit]

That's strange... it works but then reverts immediately? Is there anything else going on in that Sheet that could cause that? If you create a new document and run the script on text there does it do the same thing?

[Sc8s]

No - it only converts the top one. I think because it's pulled from another tab. However, if I simply copy, it works. I will also say I am still getting inaccuracies in times of meetings immediately after doing a "quick import".

Best,

SC

SC Billings

Executive Assistant & Office Manager

The sleep fitness company

On Wed, Sep 4, 2024 at 5:53 PM GCalTools @.***> wrote:

That's strange... it works but then reverts immediately? Is there anything else going on in that Sheet that could cause that? If you create a new document and run the script on text there does it do the same thing?

— Reply to this email directly, view it on GitHub https://github.com/GCalToolkit/GCalExtras/issues/4#issuecomment-2330208981, or unsubscribe https://github.com/notifications/unsubscribe-auth/BKEI2WO4YVICVEQQCBEL67DZU56NLAVCNFSM6AAAAABLRJNZFGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGMZQGIYDQOJYGE . You are receiving this because you authored the thread.Message ID: @.***>

[GCalToolkit]

In that case this is a Sheets bug and unfortunately I don't think there's any way around it. The new version of Sheets2GCal should now display a list of links to click when you give over the Descriptions though, so that might help.

For the incorrect event times, if you are sure your time zone settings are correct in both the Sheet and the Calendar then it's probably best to email me screenshots of the event details and how it shows in the Sheet.

[Sc8s]

Ok thanks - what email is that? I do not want to post these publicly on GitHub.

Best,

SC

SC Billings

Executive Assistant & Office Manager

The sleep fitness company

On Thu, Sep 19, 2024 at 4:29 PM GCalTools @.***> wrote:

In that case this is a Sheets bug and unfortunately I don't think there's any way around it. The new version of Sheets2GCal should now display a list of links to click when you give over the Descriptions though, so that might help.

For the incorrect event times, if you are sure your time zone settings are correct in both the Sheet and the Calendar then it's probably best to email me screenshots of the event details and how it shows in the Sheet.

— Reply to this email directly, view it on GitHub https://github.com/GCalTools/GCalExtras/issues/4#issuecomment-2362117082, or unsubscribe https://github.com/notifications/unsubscribe-auth/BKEI2WIL7K2DHNYYVBHXHKLZXMX33AVCNFSM6AAAAABLRJNZFGVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDGNRSGEYTOMBYGI . You are receiving this because you authored the thread.Message ID: @.***>

[GCalToolkit]

You can use sheets2gcal at gcaltools.com