Idea about geocoding calendar events

[trakkeo]

Dear Dave,

Thank you for sharing your script, I’m trying to customize the syncFromCalendar function to sync events from a Google Calendar, and geocode the events locations to return the latitude and longitude so as to display them on a map. Besides the script is run everytime the calendar is edited thanks to this trigger :

//Installs Calendar trigger;
function calendarTrigger() {
  var trigger = ScriptApp.newTrigger('syncFromCalendar')
  .forUserCalendar(agendaUser)
  .onEventUpdated()
  .create();
}

I’ve managed to make it work ( after a lot of struggle as I’m a newbie …) by inserting the geocoding code inside the convertCalEvent function. My concern is that it geocodes all the events each time the script is triggered, even the ones which have already been geocoded, which is consuming a lot of Google Maps geocoding requests for nothing. Could you advise me on modifying the code so it geocodes a calendar event location only if it’s new or if its location has been changed ?

Here's the convertCalEvent function modified with the geocoding part :

// Converts a calendar event to a pseudo-sheet event.
function convertCalEvent(calEvent) {

var adresse = calEvent.getLocation();
var serviceUrl ="https://maps.googleapis.com/maps/api/geocode/json?address="+adresse+"&region=fr&key="+apiKey;
var options={
          muteHttpExceptions:true,
          contentType: "application/json",
         };

        var response = UrlFetchApp.fetch(serviceUrl, options);
        if (response.getResponseCode() == 200) {
            var location = JSON.parse(response.getContentText());
            // Logger.log(response.getContentText());

            if (location["status"] == "OK") {
                //return coordinates;
                var lat = location["results"][0]["geometry"]["location"]["lat"];
                var lng = location["results"][0]["geometry"]["location"]["lng"];
  }
}

var choosenLocation = (calEvent.getDescription().includes("[[") != true) ? (lat + ','+ lng) : (calEvent.getDescription().split('[[').pop().split('],')[0]+ ','+calEvent.getDescription().split(',[').pop().split(']]')[0]);

  convertedEvent = {

    'id': calEvent.getId(),
    'title': calEvent.getTitle().slice(0,33),
    'description': '<b>'+calendarName+'</b><br>'+ calEvent.getTitle()+ '<br>'+ calEvent.getDescription()+ '<br>Adresse: '+ calEvent.getLocation() +'<b><br>Debut: '+ calEvent.getStartTime().toLocaleTimeString()+ '<br>Fin: '+ calEvent.getEndTime().toLocaleTimeString() + "<br><br><a href='https://www.google.com/maps/dir/?api=1&destination="+ choosenLocation +"' target='blank'><img src='https://go.trakkeo.com/images/popup_icon/location.png' width='51' height='45' title='Itineraire' alt='Obtenir l'itineraire'></a><a href='https://waze.com/ul?ll="+ choosenLocation +"&navigate=yes' target='blank'><img src='https://go.trakkeo.com/images/popup_icon/waze.png' width='51' height='45' title='Waze' alt='Naviguer avecc Waze'></a><a href='https://www.google.com/maps/@?api=1&map_action=pano&viewpoint="+ choosenLocation +"' target='blank'><img src='https://go.trakkeo.com/images/popup_icon/streetview.png' width='51' height='45' title='Google Street View' alt='Voir sur Google Street View'></a>",
    'location': (calEvent.getDescription().includes("[[") != true) ? ('{"lat":'+ lat + ',"lng":'+ lng +'}') : ('{"lat":'+calEvent.getDescription().split('[[').pop().split('],')[0]+ ',"lng":'+calEvent.getDescription().split(',[').pop().split(']]')[0]+'}'),
    'user_id': trakkeoUserId,
    'map_icon_id': (calEvent.getStartTime()).toLocaleDateString() > todayDate.toLocaleDateString() ? ((calEvent.getStartTime()).toLocaleDateString() === tomorrowDate.toLocaleDateString() ? iconTomorrow : iconAfterTomorrow ) : ((calEvent.getStartTime()).toLocaleDateString() === todayDate.toLocaleDateString() ? iconToday : "hier"),
    'active': "1"
  };

Thanks for your help and for sharing this great piece of code

[davepar]

Sorry for the slow response. This is a cool idea. If you're still running into issues with triggering geocoding too often, I'd suggest this: Instead of calculating a new value for description that includes the lat/lon (and other fields), use a new field that the script doesn't track like "full_description". You can hide the "description" field in the spreadsheet to avoid confusion. The reason geocoding is running every time is that the script thinks the description has changed because the value in the calendar and the sheet don't match. If you instead leave description alone and use a new field, the script should only run geocoding when the calendar event is new or edited. You'd also need to add "full_description" or whatever you choose to the titleRowMap variable. Hope that helps.

[trakkeo]

Hello Dave, And thank you for your answer. I've followed your advice and created a new field, keeping the already existing ones unchanged, unfortunately the script keep running the "convertCalEvent" function for every event, edited or not. I've made a test from the original "Calendar Sync Example" file and adding an extra field generating a random number, it looks like in any case the "convertCalEvent" function is run when I call "Update From Calendar", is it the normal behaviour ? Or is there an issue with the "eventDifferences" function maybe ?

Thanks a lot for your help.