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 storing 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.

You may also be interested in How to generate static Google Maps in Google Sheets.

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 the 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 Looker 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 Looker Studio to link Google Sheets to Google Maps.

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

  • Changes to your Google Sheets data are updated automatically on 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 vary 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 linking Google Sheets to Google Maps in Looker 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.

Feel free to watch the following video for a comprehensive demonstration, or continue reading for a detailed breakdown.

YouTube player

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 Looker Studio, as explained in the next step.

Step 02: Create a new Data Source in Looker Studio

  • Open Looker Studio.
  • Click on blank google add 2x e1586089351584 Create button and select the Data source. If this is the first time you are using Looker 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 the [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 the 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 on 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 needs to appear when hour over the bubble.
    • You can add different colors 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 colors using the Bubble color 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 border, 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 Looker Studio. Google Maps is only one visual element out of plenty of visual elements available on Looker 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 addresses with a view or edit permission.
  • Schedule email delivery – useful if your database is continuously updating
  • Get the 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 Looker Studio allows you to map valid geographic data in your database. You can easily connect your Google Sheets with Looker Studio with a few clicks. These two features make a powerful environment where you can connect Google Sheets with Google Maps. The Google Maps feature of the Looker Studio is highly customizable, and you can make use of the other Looker 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 Looker 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 Looker Studio features. By using all these features, you can build a feature-rich dashboard too.

References

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

Leave a Comment

Share via
Copy link