google-mapsgoogle-apps-scriptgoogle-sheetsgoogle-maps-urls

Un-shorten a goo.gl/maps link to coordinates

I am wondering if there is a simple way to convert a shortened google maps link into its equivalent coordinates, inside of google sheets. For example a point near the Eiffel Tower:

Coordinates: 48.857469, 2.295821

Long URL: https://www.google.com/maps/search/?api=1&query=48.857469,2.295821

Short URL: https://goo.gl/maps/fxgX5rM1snM2

My Problem:

I have a spreadsheet of locations that were shared to me using the 'share' button inside of google maps, which automatically shortens the url. I am wondering if there is a way to un-shorten the url so that I can extract the coordinates for further use.

My Attempt:

This question was asked, and a solution below shows how this can be done using Python. I would like to do this using Google Apps Script, and potentially any existing APIs that can work with URLs. I am unfamiliar with .gs code, and any tricks to using them.

UPDATE: I have followed this setup tutorial and my script is associated with my Google API project.

My questions:

This seems like a simple thing to do. Is it? If it is simple and someone can write the code quickly that would be a great help. I will go back and understand the code myself.

Solution

  • You can do this in javascript.

    In Google Scripts you can implement the method used in the other question you referenced. Google Scripts provides URLFetchApp. In a vanilla JS context you would use the fetch api or XMLHttpRequest.

    Since you are following a redirect you can provide an options object to PREVENT following the redirect.

    var response = URLFetchApp(url, {followRedirects: false});

    Then you can find the redirect URL in the response Location header. The header is encoded so you can use decodeURIComponent to work with the URL.

    var longUrl = decodeURIComponent(response.getHeaders()['Location']);

    From there you can use the RegExp from the second question you referenced to get the relevant portion:

    var matches = longUrl.match(/@([0-9]?[0-9]\.[0-9]*),([0-9]?[0-9]\.[0-9]*)/);

    You'll want to check the response status code and handle any errors in case the URL doesn't work or is not a redirect.