Tutorials and hands-on lessons for learning

Sheet Mapper Advanced

Improve the performance of Sheet Mapper by caching data.

View fullscreen demo


When choosing a backend solution for managing your data, there are a few considerations to remember: ease of updating the data, scalability of managing and serving the data, and security and privacy of the data.

This extension on the Sheet Mapper solution is appropriate for an organization looking to balance ease of use with potentially high usage, such as maps to coordinates marches and mobilization. Sheet Mapper Advanced: Caching leverages Amazon Web Services (AWS) to cache and serve sheet data.

This approach can also be used with more advanced functionality, including data joins to other tilesets, routing between locations, and more.

Getting started

There are a few resources you'll need to get started:

Templates:

Tools we'll use:

Create data in Google sheet

Set up a Google Spreadsheet with the data for your map. You can duplicate your sheet from this template or start your own. You need longitude and latitude for each point to place your map marker. Name, Address, and additional fields will be displayed in the popups.


Need help with generating lat/long points? You can use Nominatim, an open-source OpenStreetMap tool, for forward and reverse geocoding.

  1. Go to https://nominatim.openstreetmap.org/
  2. Search an address in the search bar
  3. Click the Details button, copy the coordinates listed in the Centre Point row, and paste into your spreadsheet.

Once your table is complete, set sharing settings to 'Anyone with the link can view' so the data is visible without granting access to change it. To create an export link, copy the following link to a notepad or some other document to be used later:

https://docs.google.com/spreadsheets/d/{key}/gviz/tq?tqx=out:csv&sheet={sheetname}

Replace {key} with your Google Sheet ID and {sheet_name} with the name of your sheet.

Google Sheet link:

https://docs.google.com/spreadsheets/d/1MiqwGe_7m6B0xFQfaS3GGRO8CmGm5xlXPICDPEeGHyo/edit#gid=0

Google Sheet ID: 1MiqwGe_7m6B0xFQfaS3GGRO8CmGm5xlXPICDPEeGHyo

Sheet name: Sheet1

Updated link:

https://docs.google.com/spreadsheets/d/1MiqwGe_7m6B0xFQfaS3GGRO8CmGm5xlXPICDPEeGHyo/gviz/tq?tqx=out:csv&sheet=Sheet1

Sign up for an AWS account

Amazon Web Services (AWS) is a cloud computing environment, including storage and code execution. This tutorial will show how to use AWS to cache and serve up our sheet data.

To access these services, sign up for an Amazon Web Services AWS account. AWS requires a credit card authorization upon sign up, however, for the purposes of this tutorial we will be selecting the free ‘Basic Plan’ to support our AWS usage.

Sign in to the console

Sign in to the console using the email address and password associated with your AWS account. If you are the account owner, sign in as the "Root user".

Create an S3 bucket

Simple Storage Service (S3) is a web storage service that enables users to store and retrieve any amount of data. S3 is where the sheet data will be stored.

Data on S3 is organized into “buckets”. To get started, we need to create an S3 bucket from the console. To access the S3 console, type S3 into the search bar on the Management Console:





You can also use the services tab at the top of the page to navigate to the S3 console. In the S3 console, select ‘Create Bucket’. Give the bucket a unique name, and choose the ‘Region’ geographically closest to you.





Since this bucket will publicly serve up the cached sheet for the map, toggle off ‘Block all public access'. AWS will ask for acknowledgment that the bucket will be public. Lastly, select ‘Create Bucket’.

You should now be able to access your new S3 bucket in your S3 console.

CORS Configuration

Next, set up the bucket so its contents can be requested from a website on another domain (i.e. the map). In the listing, click on the name of the bucket just created. Click the ‘Permissions’ tab and then ‘CORS configuration’. Copy and paste the code below in the ‘CORS configuration editor’ and select ‘Save’.

[
  {
    "AllowedHeaders": ["*"],
    "AllowedMethods": ["GET"],
    "AllowedOrigins": ["*"],
    "ExposeHeaders": [],
    "MaxAgeSeconds": 3000
  }
]

Create a Lambda function

Lambda is an AWS service to run code without setting up a server. We need a Lambda function to retrieve data from the Google Sheet, and save it on S3.

Create a lambda function from the lambda console.

In the lambda console, click ‘Create Function’ and select the following:

  • Keep ‘Author from scratch’ selected
  • Name the function
  • Select ‘Python 3.8’ for the Runtime environment
  • When finished, click ‘Create Function’.

Now that we’ve created a function, it’s time to add the function code. Scroll down to ‘Function Code’ section, and copy and paste this code into the function editor.

Change the code in the following ways:

  1. Line 16, replace <Name of your bucket goes here!> with the name of the bucket you created earlier.
  2. Line 26, replace <Link to your share spreadsheet goes here!> with the link to a published CSV version of the Google Sheet.
  3. Line 40, give your cached sheet a file name by replacing <Name of your cached sheet goes here!> with a name (i.e. ‘sheet.csv’).
  4. Click 'Save' and 'Deploy' to preserve your changes.

Grant the Lambda function access to the bucket

A Lambda function does not automatically have permission to access and create data in an S3 bucket. We need to configure access.

Grant permission to the Lambda function to access the S3 bucket. At the top of the Lambda function configuration page, select the ‘Permissions’ tab, and then click on the ‘Role name’.





In the ‘Permissions policies’ section, click the one Policy listed. On the next page, click ‘Edit Policy’.

  • Click ‘Add Additional Permissions’
  • For Service, select ‘S3’

Within the Actions drop down menu:

  • Expand the Read access level and select: GetObject, GetObjectAcl
  • Expand the Write access level and select: PutObject
  • Expand the Permissions management access level and select: PutObjectAcl
  • For Resources, click Add ARN. Enter the ‘Bucket Name’, and select Any for Object Name. Click ‘Add’.

Click ‘Review Policy’, and then ‘Save Changes’.

Test the Lambda function

Test the Lambda function to make sure everything is set up correctly.

Configure the test event with no arguments. Return to the Lambda function console and select the sheetMapperCaching function that we created to return to the function console.

In the ‘Function Code’ section:

  1. Click the dropdown ‘Select a test event’ at the top of the page and click ‘Configure Events’.
  2. Give the test event a name (“test” is fine), and delete all the information between the curly brackets“{}
  3. Select ‘Create’





In the Lambda configuration page, click ‘Test’ to run the test.

If all goes well, the response in your ‘Execution results’ tab below will be “null”. You can check the results of this function by loading the resulting file from S3 in a web browser. To do this, replace the name of the bucket and file in this link template: https://{BUCKET_NAME}.s3.amazonaws.com/{FILE_NAME}

Example: https://sheetmapper-bucket.s3.amazonaws.com/sheet.csv

Configure to run hourly

The cache needs to update periodically to get new data from the original Google Sheet.

Set the Lambda function to run every hour. In the ‘Designer’ section of the Lambda configuration page click ‘Add Trigger’.

Next, from the drop down menu, select ‘CloudWatch Events/EventsBridge’.

Within the ‘Rule’ dropdown:

  1. Select ‘Create a new rule’
  2. Add a rule name ex. ‘Hourly’
  3. Add a rule description (optional)
  4. Under 'Rule type', select Schedule expression and input 'rate(1 hour)' in the text box.

To disable the Lambda function from running automatically, navigate to the 'Designer' section of your function console and select the ‘CloudWatch Events/EventsBridge’ box. Then in the panel below toggle ‘Enabled’ to ‘Disabled’. Scroll to the top of the console and select ‘Save’.

Connect map to the cache

Open a text editor and create a file called index.html. Set up the document by copying and pasting this template code into your new HTML file. In this tutorial, we use the D3.js library to load the cached CSV.

Add your Mapbox access token

Without an access token, the rest of the code will not work.‍

Login or create a free Mapbox account. Find your access token on your Access tokens page or the main page you sign into your Mapbox account.

Note: We recommend using the URL restriction feature on the token to avoid token misuse and want to emphasize that only public tokens should be posted to public repositories. You can find out more information about how to securely manage your access tokens here.

Copy and paste your access token into the code, here:

//YOUR TURN: add your Mapbox token
mapboxgl.accessToken =
  'pk.eyJ1IjoibWFwYm94LWNvbW11bml0eSIsImEiOiJja2tkN21jcjAwMG51MnBxdHAxemdueGpzIn0.e0IzLkytGq4pcGGieP8KNA'; //Mapbox token

Add a basemap style

There are several Mapbox-designed map styles that you can choose "out of the box" or you can design your own using the Mapbox Studio style editor. Let’s use Mapbox Streets:


Add Streets Style to your map by replacing 'Replace with Mapbox style URL' with mapbox://styles/mapbox/streets-v11.

//YOUR TURN: add your Mapbox token
mapboxgl.accessToken =
  'pk.eyJ1IjoibWFwYm94LWNvbW11bml0eSIsImEiOiJja2tkN21jcjAwMG51MnBxdHAxemdueGpzIn0.e0IzLkytGq4pcGGieP8KNA'; //Mapbox token
var map = new mapboxgl.Map({
  container: 'map', // container id
  style: 'mapbox://styles/mapbox/streets-v11', //stylesheet location
  center: [-122.411, 37.785], // starting position
  zoom: 10, // starting zoom
  transformRequest: transformRequest
});

In the code, set the link of the file hosted on S3.

// YOUR TURN: Add the link to your shared spreadsheet to allow D3 to access your table
function init() {
  d3.csv(
    'https://sheetmapper-advanced-test.s3-us-west-2.amazonaws.com/Sheet1.csv',
    addPoints
  );
}

Congratulations! Your map is now loading from the cache rather than hitting Google Sheets directly!

Publish your map


You’ve made a web map! But it isn’t a webpage yet… to do that we need some way to host a webpage. There are many different ways to host a webpage. Read this guide to learn how to publish your finished map on GitHub, Netlify, or Glitch.

Finished project

View our finished example map at https://labs.mapbox.com/impact-tools/spreadsheet-to-map/.

Need more help? Ask questions on Stack Overflow or contact Mapbox Support. If you are working on a social good project, contact the Mapbox Community Team.

Want to share what you’ve built? Tweet it with #builtwithmapbox

Was this page helpful?