Every great web map application needs a trusty spot to fetch its data from—a database. It also needs a nifty interface to chat with that database, and that's where an API endpoint comes in. In this tutorial, we’ll show you how to build your very own back-end that generates data in a special geospatial format called GeoJSON, and the best part? We’ll be using a tool you're probably already quite familiar with—Google Sheets! And yes, it's completely free!

In this tutorial, we’ll learn how to build our own database and API endpoint as the back-end of our web map project, just like we did for the annual USE-IT meeting in Prague. All you need is a Google account – how neat is that? We’ll be using Google Sheets as a database to store points of interest in a tabular format, and Google Apps Script to automate processes and connect your data to the web. We’ll expose our database to the world as an API endpoint using the script’s ability to interact with external services.

By the end of this tutorial, you’ll have a fully functioning API endpoint that serves your data in GeoJSON format, a structured format commonly used for mapping applications to organise geographical features and their properties. And yes, you guessed it – this solution is completely free!

Connecting the Dots

First, head over to our tutorial's GitHub repository and download the example CSV file (that’s a simple text file where each line is a row and each value is separated by a comma). In Google Sheets, click on “File” and then “Import”. In the “Import File” window, click on “Upload” and simply drag in the example file.

Each row in the table represents a single point of interest, and each column is an attribute, like category, address, or title. Feel free to swap out the existing data with your own, but make sure the column names are unique – no duplicates, please!

We’ll be using two important columns: longitude and latitude, which will pinpoint the geographical location of each point of interest. These values are part of a coordinate system called WGS84, which is the global standard for GPS. You can easily find the longitude and latitude of new points using tools like Google Maps by right-clicking on a location and copying the coordinates.

Stick to the Script

Congratulations! The example data file now lives happily in your own Google Drive. Let’s get cracking on building the API endpoint. Click on “Extensions” and then “Apps Script”. Google Apps Script is powered by JavaScript, a programming language that lets you add some magic by automating tasks and interacting with various Google services. Now, delete the existing function in the script editor and paste the script we’ve provided either on our website or in our GitHub repository.

function doGet(e) {
  // set parameters
  const latitude_column_name = 'latitude';
  const longitude_column_name = 'longitude';
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getActiveSheet();
  var range = sheet.getDataRange();
  var values = range.getValues();
  var headers = values[0];
  var longitude_idx = headers.indexOf(longitude_column_name);
  var latitude_idx = headers.indexOf(latitude_column_name);
  
  // create an empty array of features
  var featuresArray = [];

  // interate over the values
  for (var i = 1; i < values.length; i++) {
    var row = values[i];
    var rowObject = {};
    var rowDataProperties = {};
    for (var j = 0; j < headers.length; j++) {
      rowDataProperties[headers[j]] = row[j];
    }
    
    // generate geometry object
    var rowGeometry = {"type": "Point", "coordinates": [ row[longitude_idx], row[latitude_idx] ] };
    rowObject['type'] = 'Feature';
    rowObject['properties'] = rowDataProperties;
    rowObject['geometry'] = rowGeometry;
    featuresArray.push(rowObject);
  }
  
  // create a geodata object
  var geodata = {"type": "FeatureCollection", "features": featuresArray}

  // return response
  return ContentService.createTextOutput(JSON.stringify(geodata)).setMimeType(ContentService.MimeType.JSON);


  }

  
function setup() {
}

You won’t need to make any changes to the script unless you’ve updated the column headers for longitude and latitude. Remember, our script is case sensitive – the values in the script must match the spreadsheet exactly.

Save the project, and then click on "Deploy" > "New deployment". Under "Select type", choose "Web app". In the description, write “initial deployment” or anything else you find useful. In the “Execute as” field, select "Me", and for the “Who has access” field, choose "Anyone". Finally, click "Deploy".

Brilliant! The deployment has been successfully updated. Now, copy the URL – this is our API endpoint. Open a new tab in your browser and navigate to the URL to access it.

That’s a Wrap: Your API is Live and Kicking!

Yay! You’ve just created a shiny new database and an API endpointfor your web map application. Any changes you make to the spreadsheet will instantly reflect through the API, making it a super handy and powerful collaboration tool. Don’t forget to explore the rest of our tutorials to discover more tools and tricks for your digital adventure.

What's next? Dive into adding even cooler features to your database and API endpoint. Try updating the data in your database and watch how the API endpoint’s output changes instantly – no need for additional deployments! For the advanced explorers: consider adding extra parameters to your endpoint. Perhaps you could filter by neighbourhood to get a subset of your data, or even set up a geographical location with a buffer to fetch points of interest within that area. We’d love to see what you come up with, so don’t be shy – share your creations with us. Happy mapmaking!