Search code examples
javascriptgoogle-apps-script

shorten a URL in googlesheets with Apps Script?

I have a long URL for a google slide that is put into a calendar event. Everything works, but the long URL looks funky and I would like to be able to use a short URL that google sheets can generate on it's own, without needing another API other than Apps Script. enter image description here

/*
* Calendar must be created in advance. 
* Once created go to Settings and scroll down to  Calender ID. 
* Copy and paste ID into <Calendar ID> cell of main worksheet
* Copy and paste the calendar name into <Calendar Name> cell of main worksheet
*/
function CreateManyEvents() {
//Retrieve information from the spreadsheet 
// Make the 3rd sheet active in the active spreadsheet.
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
SpreadsheetApp.setActiveSheet(spreadsheet.getSheets()[2]);

// Call function BulkDeleteEventsDelete on any pre-existing events 
BulkDeleteEvents(spreadsheet)

//Retrieve the value of the calendar ID from the cell that it lives in.
//Call CalendarApp service to open the calendar
var calendarId = spreadsheet.getRange("A1").getValue();
var eventCal = CalendarApp.getCalendarById(calendarId);

var classes = spreadsheet.getRange("A3:F8").getValues();
for (x=0; x<classes.length; x++) {   
    var item = classes[x];
    
    var title = item[0];
    var startTime = item[1];
    var endTime = item[2];
    var session = item[3];
    var what = item[5] + '\r\n\n' + item[4];

    //create event with data selected
    eventCal.createEvent(title, startTime, endTime,{location: session,description: what});  
    
  }

}

So is there a way to shorten the URL, item[5], before joining it with the other item that will go in the calendar event description?

I've looked around and solutions all involve changing a setting in Tools that I don't have - all I've got is the Apps Script under Extensions - and/or using some 3rd party API.

I'm hoping for a solution that is 'in house' as it were. Even changing the URL to its IP address would be OK.

Solution

  • you may try embedding your URL inside an a tag with a name you like and put the item[5] value inside the href attribute.

    Screenshot

    I've tried this and this is how it looks.

    Screenshot Calendar Event

    Code Example

    Try to add html inside the description key like this.

    {description: "<a href='www.google.com'>Link</a>"});