In this blog, we have discussed several methods that you can use to create Google Maps from Google Sheets data. In this post, let’s see how we can generate Google Maps in Google Sheets.
In this post, I am going to use Google Apps Script to create the map image inside the Google Sheets. You can use this script even if you have zero knowledge of coding.
Table of Contents
About Google Static Maps
A static Google Maps is an image of a map generated by Google Maps. It is called “static” because the map image has no interactive features like zooming, panning, or clicking on markers and cannot be updated in real-time.
Static maps can be useful for a variety of purposes, such as adding a location map to a website, generating map images for reports, or creating custom map overlays.
In this post, I will show you how to generate static Google Map images within Google Sheets using the data stored in it.
Google Apps Script StaticMap Class
This is the main Google Apps Script class we will use in this post. The Google Apps Script StaticMap class provides several methods to create and decorate static map images. You can use these methods to customize Google Maps, add points and paths to Google Maps, and generate the map image.
Generating the static Google Maps in Google Sheets
Currently, there is no direct option in Google Sheets to generate a statistic map. However, you can send data from Google Sheets to the Google Maps service and generate the statistic map image using the Apps Script class mentioned above. Then you can extend the code to insert this image into Google Sheets.
The Methods of StaticMap class that we are going to use in this post to create static Google Maps
The StaticMap class has several methods. But, in this post, I am going to use the following methods to create static Google Maps in Google Sheets.
- addMarker(latitude, longitude)
- addPath(points)
- setMarkerStyle(size, color, label)
- setSize(width, height), You can see the complete method list at this link.
Creating a custom menu in Google Sheets to run the code
In each example below, you will see a new custom menu in Google Sheets named “My Menu”. This menu is also created from the code given in the examples. The menu is created automatically every time you open Google Sheets. The menu is created using the onOpen() reserved function. The following code snippet which you can see in each example below does the job.
/**CREATE CUSTOM MENU TO RUN THE SCRIPT */
function onOpen(){
SpreadsheetApp.getUi().createMenu('My Menu')
.addItem('Create Static Map','createStaticGoogleMap')
.addToUi();
}Example 01: Create Static Google Maps with place markers
Let’s explore how to display a set of geographic coordinates stored in Google Sheets on a static Google Map.
Watch the following demo video to see how it functions.
I am going to use the following sample data set for this example. In this table, the 4th column contains the geographic coordinates (latitude and longitude) in the form of “latitude, longitude”.
The following Apps Script can create the map we want. To test this code, you can either make a copy of the Google Sheets from the link given below, or
- Open the Google Sheet’s script editor from Extensions > Apps Script
- Then copy the following code and replace any code in the Code.gs file of the script editor and save it.
- Refresh the Google Sheet and, you can see a new custom menu name “My Menu”
- Now select the range with latitude and longitude and click the “Create Static Map” option in the “My Menu”
- The map with the chosen geographic coordinates’ place markers will then appear.
You can make a copy of the above Google Sheet from the following link.
Example 02: Add distinct colors and labels for each marker category
In the preceding instance, we utilized identical labeling and color for all place markers. To enhance the map’s visual appeal, you can vary the marker labels and assign distinct colors to markers based on their respective categories.
To explain this, I will use the same data set used in the previous example. The markers are going to categorize based on the “Region” column (first column) of the dataset.
Watch the video below to discover how it functions.
The Google Apps Script behind this Google Sheets is given below.
In this code, the data range is the range you select in the Google Sheet (the active range of the “Data” tab). Unlike in the previous example, here you need to select the entire data table (excluding the title row).
In the code, you need to define based on which column you are going to define the categories (line 11 of the above Apps Script). Since we are going to categorize the markers based on the region, your column number is 0 (zero). Then the code extracts unique values from this column and updates the “categories” variable.
The function “getRandomColor” returns a random color every time it calls in RGBA format.
Then the function loop through each category and data range and apply unique color and label for markers in each category.
You can make a copy of the above Google Sheet from the following link.
Example 03: Create a Static Google Map with paths
The examples we’ve seen so far involved adding place markers to a Google Map based on specific geographic coordinates. However, with a few modifications to the code, it’s also possible to map paths between different points on the map. This can be useful for visualizing routes or directions.
To see how this works in action, please take a look at the demo video below.
The Apps Script code is given below.
In the above code, we use the class DirectionFinder in addition to the class StaticMap. We use the DirectionFinder class to obtain the paths between the origin and destination coordinates via the waypoints.
The code selects the first route of the directions returned as our path. Then we assign points in this route to the addPath method of the StaticMap class. By doing this, we can map our path along the road network.
You can make a copy of the above Google Sheet from the following link.
Wrapping Up
With the Google Apps Script’s StaticMap class, we can create custom Google Maps images. In this post, we discussed how to generate such static maps using a set of geographic coordinates stored in Google Sheets and insert that map image into the same Google Sheet. Further, we discussed categorizing the markers and adding distinct colors and labels to each marker category. We also discussed how to map paths between given origin and destination coordinates via the given waypoints.
You can copy the Google Sheets from the links given above to your Google Drive. Then you can customize them to meet your requirements.
