How to build a custom Google Map in Data Studio using Google Sheets and Geocode

In this post I’ll show a way of creating a detailed, custom Google Map in Google Data Studio.

So let’s say you have a list of Addresses that you’d like to map out in Data Studio. What are the steps to get this done? I’ll go over how you can do this using Google Sheets, the Geocode add-on and the newly released Google Maps chart type in Data Studio.

FYI there may be several ways of doing this but this method has worked for me so I decided to share it.

The Custom Google Map in Google Data Studio

So below is our new custom Google Map showing specific locations in Cape Town in Google Maps!

IMPORTANT = As of the moment Google Maps cannot be displayed in embedded reports.

That means in order to view the map you will need to open the report below in Google Data Studio itself.

Open report in Google Data Studio

Click on the Google Data Studio logo in the bottom right hand corner to Open the report in Google Data Studio/

PS – this is what the map is meant to look like if you’re unable to open the report in Google Data Studio.

a map of cape town bars made in google maps in google data studio

Note: Reasons for using Lat, Long instead of Address on a Google Map

So as part of the updates for Google Maps you can now create Address and Postcodes, along with Latitude and Longitude data types.

Latitude, Longitude, Postcode and Address data types for Google Maps in google data studio

Using the Address data type means that you can type in a specific address landmark and it will be mapped on your Google Map automatically, without any latitude or longitude needed.

However, while this works fine there is a maximum of 1000 values that you can currently have on your map. As shown below.

Up to 1000 bubbles in google map in google data studio

If you use the Latitude, Longitude data type to map bubbles on your Google Map you can have up to 10,000 data points.

Up to 10000 bubbles using latitude and longitude in google map in google data studio

So if you have less than 1000 locations for your map then use Address data type, but if you have more than 1000 then you’ll probably want to use Latitude and Longitude data type.

Step 1: Create a list of Addresses in Google Sheets

The first step is get a list of addresses, as shown below in Google Sheets. These are various restaurants and bars in Cape Town, South Africa.

A list of Addresses in Google Sheets for our custom Google Map in Data Studio

Step 2: Add the Geocode Add-on for Google Sheets

To do this, we will need to get an Add On for Google Sheets.

 get an Add On for Google Sheets.

Type in Geocode by Awesome Table to find the correct Add-on.

G Suite marketplace

We then select the GeoCode from the G Suite Marketplace.

Selecting geocode from the G Suite Marketplace

Once we click on Geocode by Awesome Table we are taken to a preview page. Click Install to add it to Google Sheets.

Installing geocode by awesome tables to google sheets

An Install pop-up will appear and you will need to click Continue.

installing geocode

You will then need to sign into your Google Account and authorize the Geocode Add-on. Once you’ve signed in and allowed access you will see that it has been installed in Google Sheets.

geo code installed

It is now in our list of Add-ons, and it’s time to start geocoding!

Step 3: Start Geocoding!

The next step is to click ‘Start Geocoding’.

adding goecode add-on which will allow us to make a custom Google Map in Data Studio

I’ve chosen to geocode based on the data in my Address column in my list of addresses. Now

addresses

After click ‘Geocode!’ two new columns with Latitude and Longitude data will appear in each row.

google sheets latitude and longitude in spreadsheet  for our custom Google Map in Data Studio

Step 4: Add your Data to Google Data Studio

We now need to add our Data to Google Data Studio. To do this we use the Google Sheets connector.

Adding Google Sheets as a data source

Once we’ve added our data, we then need to create a ‘Lat, Long’ data field. To do this, click to ‘Add a Field’ and then use the following formula; CONCAT(Latitude,”, “,Longitude). You can name this field ‘Lat, Long’.

You will then need to change the data type to Geographic > Latitude, Longitude.

Below is our data in Data Studio, ready to be used to make a report. Note that I’ve also added in a ‘Category’ field stating if the place is a bar, pub, takeaway or restaurant.

Below is our data in Data Studio, ready to be used to make a report

Step 5: Add a Google Map to display your Data

So to do this we add a Google Map which is the newly released chart type.

For this map we setup the data in the following way;
Bubble location = Lat, Long
Tooltip = Place
Bubble colour = Category
Bubble size = Record count

Since each record count is equal to one the bubbles will all be the same size. They will be different colours based on the category.

Also note that while I’ve set the default map type to the regular Google Map style you can also use the controls to change the map style. As an example below I’ve changed the map style to Satellite.

a custom Google Map in Data Studio using Google Sheets and Geocode

I hope you found this fun, interesting and maybe as an inspiration to create your own custom maps in Data Studio!

Download Google Data Studio Templates

If you’d like to download one of my Google Data Studio reports as a template to use for your own data, you can visit Data Studio Templates and purchase one.

Google Data Studio Templates

My Other Blog Posts

If you enjoyed this article you might enjoy my other blog posts.

Michael Howe-Ely