Easily Add Google Maps to Your Google Sheets Automatically and for Free

Just like many Makers, I built few GPS tracker projects. Today, we will be able to quickly visualize GPS points straight in Google Sheets without using any external website or API.

Best of all, it's FREE!

Step 1: Create a Blank Spreadsheet

Go to sheets.google.com or docs.google.com/spreadsheets to create a blank spreadsheet. If you've never created a spreadsheet on Google before, you can quickly get started by watching this video.

I named my spreadsheet MapsChallenge, but you can use any name you like.

Step 2: Add Your GPS Data

First row should be reserved for column headers. Starting at second row, enter GPS points. You will need three columns and they need to be in the following order:

Time

Latitude

Longitude

Here are some GPS points from a quick trip between a hotel and a restaurant in Houston, Texas:

Time Latitude Longitude

11:55:33 PM 29.7384 -95.4722

11:55:43 PM 29.7391 -95.4704

11:55:53 PM 29.7398 -95.4686

11:56:03 PM 29.7403 -95.4669

11:56:13 PM 29.7405 -95.4654

11:56:33 PM 29.7406 -95.4639

11:56:43 PM 29.7407 -95.4622

11:56:53 PM 29.7408 -95.461

11:57:03 PM 29.7412 -95.4607

11:57:13 PM 29.7421 -95.4608

11:57:23 PM 29.7432 -95.4608

11:57:33 PM 29.7443 -95.4608

11:57:43 PM 29.7451 -95.4608

11:57:53 PM 29.7452 -95.4608

11:58:03 PM 29.746 -95.4608

Step 3: Add Automation

If you are familiar with macros in applications like Microsoft Excel you'll find this concept familiar. The code we will write here does not run locally and it is JavaScript (ish) not VBA. Click Tools menu then select Script editor. I named my script MapsChallenge as well.

Step 4: Use My Code

Delete the contents of Code.gs then add the following code and click Save:

var ThisSheet;

var map;

var ThisRow;

var LastPointTime;

var ThisPointTime;

// Run once sheet is open

function onOpen() {

ThisRow=2;

// Resize columns width

ThisSheet = SpreadsheetApp.getActiveSheet().setColumnWidths(1, 4, 85);

// Remove all map images

ThisSheet.getImages().forEach(function(i){i.remove()});

// Keep text in cells

ThisSheet.getRange('A:D').setWrapStrategy(SpreadsheetApp.WrapStrategy.CLIP);

var Seq=1;

ThisPointTime=ThisSheet.getRange(ThisRow,1).getValue();

while (ThisPointTime != '') {

// Start map caption

ThisSheet.getRange(((Seq-1)*30)+27, 5).setValue('Starting at row '+ThisRow);

// Create a map

map = Maps.newStaticMap();

// First marker

PlaceMarker(Maps.StaticMap.MarkerSize.SMALL, "0x00FF00", 'Green');

// The difference between this point and the last one is less than 10 minutes

while (ThisPointTime - LastPointTime < 600000) {

// Is there a next marker or last one?

(ThisSheet.getRange(ThisRow+1,1).getValue() - LastPointTime < 600000)? PlaceMarker(Maps.StaticMap.MarkerSize.TINY, "0x0000FF", 'Blue'): PlaceMarker(Maps.StaticMap.MarkerSize.SMALL, "0xFF0000", 'Red');

}

// Add GPS track image to sheet

ThisSheet.insertImage(Utilities.newBlob(map.getMapImage(), 'image/png', Seq), 5, ((Seq-1)*30)+2);

// End map caption

ThisSheet.getRange(((Seq-1)*30)+27, 5).setValue(ThisSheet.getRange(((Seq-1)*30)+27, 5).getValue() + ' ending at row ' + (ThisRow-1)).setFontWeight("bold");

Seq++;

}

function PlaceMarker(a,b,c) {

map.setMarkerStyle(a,b,c);

map.addMarker(ThisSheet.getRange(ThisRow,2).getValue(), ThisSheet.getRange(ThisRow,3).getValue());

LastPointTime=ThisPointTime;

ThisRow++;

ThisPointTime=ThisSheet.getRange(ThisRow,1).getValue();

}

Step 5: Close Then Reopen Your Spreadsheet

The automation we created will be triggered only by Spreadsheet opening event. After closing the Spreadsheet, go to drive.google.com and open your Spreadsheet.