Google Map in Google Sheets

Google Apps Script

  • Key highlights

  • Analyze arbitrary number of addresses
  • Collect address metadata
  • Convert addresses to lat/long data
  • Display addresses as map markers
  • Full-screen map view

Summary

This project involves parsing addresses in a Google Sheet along with their metadata. A Google Apps Script is launched via a custom menu. It creates a sidebar with a Google Map, converts the addresses into lat/long data points, centers the map according to the addresses, and displays the addresses as markers on the map. Address metadata is shown when the user hovers over the marker. The map can be viewed in full-screen mode.

Easy to use

Users enter location information in a Google Sheet. They can provide metadata about each location in any number of columns they want.

Users launch the sidebar via a custom menu.

Interactive map

The Google Apps Script uses Google Maps JavaScript API and Google Geocoding API to display a Google Map inside the sidebar. The script translates each address to a geolocation and calculates the center of the map based on the addresses. It then displays a marker for each address in the sheet. The script loads each marker with the address metadata from the sheet, which is shown when the user hovers over the marker.

Users can interact with the map to zoom in/out, etc.

Full view

Users can switch to view the map in full screen. They can interact with the map by switching views, zoom level, etc.

Technologies and libraries in project
Google Apps Script
Google Sheets
Google Spreadsheet Service
Google Maps JavaScript API
Google Geocoding API
eslint
prettier
RELATED CASE STUDIES

This Google Apps Script looks for new IP addresses that are entered in a Google Sheet. The script validates each IP address, triggers a call to a Geo lookup API, and updates a different sheet with geographical data, such asa city, state, and internet service provider.

The client needed a Chrome extension, which his team would use to mark their website pages for scraping. A Google Apps Script accepts the extension's page requests, scrapes the web page, and saves the data in a Google Doc based on a template.

This Google Apps Script queries the Twitter API, pulling data about tweets and followers into a Google Sheet, which is synched with a Google Data Studio report.

This project embeds a Calendly form on a Google Apps Script web app page. Calendly can be pre-filled with data if the user is a returning customer. When the user completes the registration, a Calendly webhook calls the web app end point, which updates a Google Sheet with the registration data.

This Google Apps Script pulls daily stock quotes from the Yahoo Finance API. It stores the data in a Google Sheet for historical look-ups. The script updates a summary sheet by calculating today's price change in dollars and percentage.

This Google Apps Script pulls new entries from a Google Sheet in real-time. It uses a Zapier webhook to insert new to-do tasks into an Asana project. The payload includes a mix of standard and custom Asana fields. The script updates the sheet with Zapier IDs to eliminate duplicate inserts.