Tutorial Maps with Google App Script

In the following tutorial with Google App Script and Google Maps I want to place on the map the directions of theme parks that I have saved in a spreadsheet. Something like this:

google maps app script

Step 1:

Make a spreadsheet in Google Drive

I have created a spreadsheet with two columns: “Name” and “Address”
spreadsheet address

Step 2:

Give it a name to the spreadsheet (lower left corner):
spreadsheet name

I have called the spreadsheet “map”

Step 3:

Open the Script Editor Google App Script

Menu > Tools > Script editor…

script editor gas

Step 4:

In the script editor, we create onOpen function (this function will be executed every time we open our spreadsheet).

The function has the following code:

function onOpen() {

// Get the sheet named 'maps'
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('map');

// Store the restaurant name and address data in a 2-dimensional array called
// parksInfo. This is the data in cells A2:B4
var parksInfo = sheet.getRange(2, 1, sheet.getLastRow() - 1, 2).getValues();

// Create a new StaticMap
var parksMap = Maps.newStaticMap();

// Create a new UI Application, which we use to display the map
var ui = UiApp.createApplication();

// Create a grid widget to use for displaying the text of the restaurant names
// and addresses. Start by populating the header row in the grid.
var grid = ui.createGrid(parksInfo.length + 1, 3);
grid.setWidget(0, 0, ui.createLabel('N#').setStyleAttribute('fontWeight', 'bold'));
grid.setWidget(0, 1, ui.createLabel('Park').setStyleAttribute('fontWeight', 'bold'));
grid.setWidget(0, 2, ui.createLabel('Address').setStyleAttribute('fontWeight', 'bold'));

// For each entry in parksInfo, create a map marker with the address and
// the style we want. Also add the address info for this restaurant to the
// grid widget.
for (var i = 0; i < parksInfo.length; i++) { parksMap.setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.GREEN, i + 1); parksMap.addMarker(parksInfo[i][1]); grid.setWidget(i + 1, 0, ui.createLabel((i + 1).toString())); grid.setWidget(i + 1, 1, ui.createLabel(parksInfo[i][0])); grid.setWidget(i + 1, 2, ui.createLabel(parksInfo[i][1])); } // Create a Flow Panel widget. We add the map and the grid to this panel. // The height needs to be able to accomodate the number of restaurants, so we // use a calculation to scale it based on the number of restaurants. var panel = ui.createFlowPanel().setSize('500px', 515 + (parksInfo.length * 25) + 'px'); // Get the URL of the restaurant map and use that to create an image and add // it to the panel. Next add the grid to the panel. panel.add(ui.createImage(parksMap.getMapUrl())); panel.add(grid); // Finally, add the panel widget to our UI instance, and set its height, // width, and title. ui.add(panel); ui.setHeight(515 + (parksInfo.length * 25)); ui.setWidth(500); ui.setTitle('Theme Park Locations'); // Make the UI visible in the spreadsheet. SpreadsheetApp.getActiveSpreadsheet().show(ui); }

Via: Google Developers

Step 5:

Now we return to our spreadsheet and press F5 to refresh. OnOpne function will run and we will see the following:
google maps app script

We can change the color of the markers on the line:

Also we can change the size in:

If we want to change the type of map, we can do it by adding the following line :
Available types are:
google maps 2
google maps 1

Leave a Comment
Your comment: