How to link Google Sheets to Google Maps and create interactive maps

Most of our day to day databases include geographic data such as country, region, city, address, latitude-Longitude, place names, etc. Many of you are used to store these data on spreadsheets applications like Google Sheets, Microsoft Excel, etc… If you can link these databases to a map, you can see the changes to your data in real-time on the map. In this post, I will show you how you can link Google Sheets to Google Maps to view changes to Spreadsheet dynamically.

Though you can easily create a Google My Map by importing Google Sheets, you need to import it every time to see the changes to Spreadsheet. You cannot refresh the map to apply changes to the Spreadsheet or do any sort and filtering etc…

However, if you create your map in Google Data Studio, you can get the benefits of many of its native features to enhance the functionality of your map. In this post, I will show you how you can use Google Data Studio to link Google Sheets to Google Maps.

If you haven’t used Google Data Studio before, I suggest you read “How to Create an Online Dashboard for free to Share and Visualize Your Data“.

Benefits of Using Google Data Studio to Link Google Sheets to Google Maps

  • Changes to your Google Sheets data updated automatically in your map
  • You can sort and filter your data and see the changes in real-time
  • Customize your map layers
  • Change bubble (marker) size & color
  • Set bubble size to varying with the values
  • You can plot many geographic dimensions (Eg. Address, City, Region, etc…) no need to provide coordinates every time
  • Can have multiple maps in the same window
  • Can use many other data sources
  • Use many other data visualization elements to create a complete dashboard
  • It’s completely free.

Limitations

There are also some limitations when linking Google Sheets to Google Maps using this method.

  • At present, data can only be represented as bubbles
  • Maximum Data Limits
    • 10,000 bubbles for Latitude, Longitude fields.
    • 1,000 bubbles for other geographic field types.

Step by step guide to link Google Sheets to Google Maps in Google Data Studio

For this example, I use the “World Airports” data set downloaded from ArcGIS Hub

You can make a copy of the Google Sheet with the data from here.
You can see a live demo built with the above data set from this link.

Step 01: Prepare your Google Sheet (Data Sheet)

  • Make sure your datasheet does not have merged cells.
  • The first row should be your column headers.
  • Each record should be represented by a single row.
  • If you are using city names, country names, addresses, etc… make sure you don’t have any typos.
  • The latitudes and longitudes should be in the form of [latitude,longitude]. If latitudes and longitudes are in separate columns, either;
    • You can create a new column in your Google Sheet and combine latitude and longitude values. You can use the following array formula if A and B columns contain latitude and longitude values.
      =ARRAYFORMULA(if(ISBLANK(A2:A),””,A2:A&”,”&B2:B))
    • Or you can create a calculated field in Google Data Studio, as explained in the next step.

Step 02: Create a new Data Source in Google Data Studio

  • Open Google Data Studio.
  • Click on blank google add 2x e1586089351584 Create button and select the Data source. If this is the first time you are using Data Studio, it will popup the configuration wizard and ask you to accept terms and conditions and to set up your preferences.
  • Click on the Google Sheets option.
  • The next page shows up all the Google Sheets saved in your Google Drive. Select the correct Spreadsheet. (World Airports in this case)
  • In the Worksheet column, select the worksheet with your data.
  • Click the CONNECT button at the top right corner of the page.
  • On the next page, you can rename your data source at the top left corner.
  • Select the correct data type for each field.
    • For your geographical fields, for example, if you have a country field, click the “type” cell next to your field name > hour over the Geo field > Select Country.
  • As I mentioned in Step 01, you can create new fields using the exiting fields. To create [Latitude, Longitude] field,
    • Click Add A Field butting closer to the top right corner
    • Type a field name (Eg. Latitude_Longitude)
    • In the formula box type following equation (assuming your existing field names are “latitude” and “longitude”)
      CONCAT(latitude,CONCAT(“,”,longitude))
    • Click SAVE
    • Change the data type to Latitude, Longitude as explained above.
  • Finally, click the CREATE REPORT button at the top right corner of your page.

Step 03: Add Google Maps to your Report

  • Once you click the CREATE REPORT button, you are directed to the report page. It will automatically add a sample table using a few fields of your data set. Click and delete it if you do not want it.
  • To add a Google Map, click Add a chart > Google Maps. Click somewhere in the report to place the map.
  • Once you add the map, it will be populated with one of your Geodata fields.
  • You can also add multiple maps inside the same page.

Step 04: Customizing the Maps

  • Click on the map to select it
  • Customization options are in the right-side panel. In this panel, there are two tabs, namely Data and Style.
  • Under the Dimension section in the Data tab
    • For the Bubble location, select your Geo field.
    • For the Tooltip, select the location name or any field that need to appear when hour over the bubble.
    • You can add different colours to your bubbles based on the unique data in the selected field. This is useful when you have a fever number of unique data. Add colours using the Bubble colour field.
  • Under the Metric section in the Data tab
    • You can also set the bubble size to be varying with the value of your data. You can set it using the Bubble size field.
  • In the style tab, there are many options that allow you to style the Background Layer, Bubble Layer, Colors, Map controls, Background and boarder, and Chart headers.
    • For example; using the bubble layer, you can set the bubble size and the number of bubbles to be displayed.

Step 05: Add Filters and other Visual Elements

You can build a complete dashboard with Google Data Studio. Google Maps is only one visual element out of plenty of visual elements available on Data Studio. You can add these visual elements to create a dynamic map.

Add Filters Controls

You can use filter controls to, filter your data inside the map.

  • Click the filter icon in the top menu and draw the filter box in the report.
  • You can add multiple filter controls.
  • Select all the related filter controls and the map.
  • Right-click on the selected elements and click group.

Add other Visual Elements

You can also add other visual elements such as different types of graphs, tables, scorecards, etc… Follow the same method described above for adding filter controls when adding other elements also. You need to group all these related items.

Step 06: View the Map/ Dashboard

Your map is not interactive in the edit mode. Click the View button at the top right corner to switch to the view mode.

Step 07: Share the Map/ Dashboard

There are multiple ways that you can share your report.

  • Invite people through their email address with a view or edit permission.
  • Schedule email delivery – useful if your database is continuously updating
  • Get report link and share it.
  • Embed report on your website (Google Map does not work on embedded reports)
  • Download as a PDF

Wrapping Up

The Google Map feature of the Google Data Studio allows you to map valid geographic data in your database. You can easily connect your Google Sheets with Google Data Studio with few clicks. These two features make a powerful environment that you can connect Google Sheets with Google Maps. The Google Maps feature of the Google Data Studio is highly customizable, and you can make use of the other Data Studio features to create an interactive map. Most importantly, Google provides all these features to you free of charge.

Accordingly, this post explains to you how you can link Google Sheets to Google Maps using Google Data Studio. However, the map you can build using this method is subject to some limitations, as mentioned above. The Google Maps feature is only one of the plenty of Google Data Studio features. By using all these features, you can build a feature-rich dashboard too.

References

  1. Data Studio Help
  2. Data Studio Help – Google Maps reference

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.