Google Sheets – How to Embed a Google Map of an Address

google mapsgoogle sheets

I have a google sheet and I have cell B1 called Address which contains:

405 E 42nd St, New York, NY 10017

I would like to embed a google map into my google sheet in cell D5 that looks like this:

enter image description here

Note: I don't need other landmarks, but if they are there it won't bother me.

Research:

I googled it, and came up with zilch. I found code on how to import an image, but that's not the hard part. The hard part is getting the image of the map, or better yet, embedding a live dynamic map that changes when the address on the sheet changes (preferable).

I can use this api to get a static image:

https://maps.googleapis.com/maps/api/staticmap?center=405%20E%2042nd%20St,%20New%20York,%20NY%2010017&size=600×300&key={myApiKey}

static map

but I don't know how to get it into the sheet.

Best Answer

Tl;Dr
Try =IMAGE(url) and if that doesn't work as you need it use Google Apps Script and the Google Maps Static API to insert the map as an OverGridImage object.

Few weeks ago Google announced that now is possible to insert an image into a cell. Below is the insert image into a cell dialog:

Google Sheets Insert Image dialog

I don't think that it could be used to automatically update an image when a cell value changes. That could be done by using IMAGE built-in function if the image is public. As the Google MAP Static require an API key and because it response could exceed the time execution limit of IMAGE, it could me more reliable to use Google Apps Script but it's known that on edit triggers works on a "best effort" basis (meaning that now always are triggered like when a several cells are updated in short time)

To use Google Apps Script

  1. Spreadsheet Service to have read/write access to the spreadsheet and have access to Class Sheet and Class OverGridImage
  2. Url Fetch Service to get the image from Google Maps Static API
  3. On edit trigger to make the spreadsheet be updated when B1 (or any other cell that you want to use as trigger) is edited by a user by using the Google Sheets UI.
Related Topic