Stitching Sheets: Using MongoDB Stitch To Create An API For Data In Google Sheets

MongoDB Stitch is a power tool in your toolbox, capable of integrating your databases to any service with a publically available API. I've written a number of articles on this on MongoDB's Blog but in this article, I'll share a quick lesson on how to integrate data from Google Sheets, Google's online spreadsheet, into MongoDB using MongoDB Stitch. Watch the video or read on to learn what's involved.

Starting the Workflow

There are two key elements that make this solution work. First, we have a Google Sheets script which runs from a menu item we add to the sheet. This script collects a row of data at a time and POSTs it to a MongoDB Stitch HTTP Service incoming webhook. The second is the function that runs when the webhook is called — this is where the data is received and inserted into a MongoDB Database Collection.

Here's an example spreadsheet that contains data for my team's event tracking spreadsheet.

Google Sheets works well for this because we need to collaborate on the events that we'll cover. Each Developer Advocate adds interesting events or conferences to the sheet. But what if I wanted to make this data available outside of Google Sheets?

What if I wanted to build an API so that this data was exposed and available for another application to consume? Slack, for example?

To accomplish this, we'll use Google Sheets Scripting to send the data from a worksheet to a MongoDB Stitch Service API.

Create a Google Sheets Script

Google Apps Script is a scripting language for light-weight application development in the G Suite platform. It is based on JavaScript 1.6 with some portions of 1.7 and 1.8 and provides a subset of ECMAScript 5 API, however instead of running on the client, it gets executed in the Google Cloud.

Finding the Google Script Editor

From the Tools menu in Google Sheets, select Script editor. If you want to skip this section, you can make a copy of the spreadsheet that already has the script attached.

Sending Data from Sheets to MongoDB

In our sheet, we have the following structure - columns: Events, URL, Type, Start, End, location, Status and Owner.

Columns and Rows in our Sheet

The script simply loops through the active data in the sheet, each column in each row and builds an object with the values. This is what an object from a row of values looks like:

{
	"_id" : ObjectId("5c7bf99caf6a96a9b45f84b4"),
	"owner" : "Steve",
	"date_start" : "2019-02-09T05:00:00Z",
	"name" : "PyTennessee",
	"location" : "Nashville, TN",
	"date_end" : "2019-02-10T05:00:00Z",
	"type" : "Conference",
	"status" : "Approved"
}

Then we form a POST request using the Google Script class UrlFetchApp to send the object with our values to a MongoDB Stitch HTTP Service.

/****
 * Export the events from the sheet to a MongoDB Database via Stitch
 ****/
function exportEventsToMongoDB() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("Events");
  var headerRows = 1;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange(); // determine the range of populated data
  var numRows = range.getNumRows(); // get the number of rows in the range
  var data = range.getValues(); // get the actual data in an array data[row][column]
  
  for (var i=headerRows; i<numRows; i++) {
    var eventIdCell = range.getCell(i+1, columns.event_id+1);
    var desc = data[i][columns.desc];
    var date_start = Utilities.formatDate(new Date(data[i][columns.date_start]), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");
    var date_end = Utilities.formatDate(new Date(data[i][columns.date_end]), "GMT", "yyyy-MM-dd'T'HH:mm:ss'Z'");    
    // Make a POST request with form data.
    var formData = {
      'name': data[i][columns.desc],
      'location': data[i][columns.loc],
      'date_start': date_start,
      'date_end': date_end,
      'status': data[i][columns.status],
      'owner': data[i][columns.owner],
      'url': data[i][columns.url],
      'type': data[i][columns.type],
      'event_id': data[i][columns.event_id]
    };
    var options = {
      'method' : 'post',
      'payload' : formData
    };
    if (desc) {
      var insertID = UrlFetchApp.fetch('https://webhooks.mongodb-stitch.com/api/client/v2.0/app/stitch-sheets-zkvuv/service/sheets/incoming_webhook/import', options);
      eventIdCell.setValue(insertID); // Insert the new event ID
    }
  }
}

From https://gist.github.com/mrlynn/08786459db46b731c478468869bcb7a2

Inserting the Data from the Sheet from an HTTP Service Webhook

Once this row of data has been gathered and submitted, the receiving MongoDB Stitch Service inserts the posted query object into the database. I'll not go into how to create a service, but if you want to see that visit this post from a few weeks back.

Here's the webhook data to perform the insert into the database.

exports = async function(payload) {
    const mongodb = context.services.get("mongodb-atlas");
    const eventsdb = mongodb.db("events");
    const eventscoll = eventsdb.collection("events");
    const result= await eventscoll.insertOne(payload.query);
    var id = result.insertedId.toString();
    if(result) {
        return JSON.stringify(id,false,false);  
     }
    return { text: `Error saving` };
}

Sending Rows to be Removed

Similarly, and just as simple, we can create another function in our Google Sheets script to remove the entries from MongoDB.

/****
 * Delete the events from the sheet and remove the eventID Reference from the sheet.
 ****/
function removeEventsFromMongoDB() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName("Events");
  var headerRows = 1;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var numRows = range.getNumRows();
  var data = range.getValues();
  
  for (var i=headerRows; i<numRows; i++) {
    // Cells are 1 indexed
    var eventIdCell = range.getCell(i+1, columns.event_id+1);
    // Make a POST request with form data.
    var formData = {
      'name': data[i][columns.desc],
      'location': data[i][columns.loc],
      'date_start': data[i][columns.date_start],
      'date_end': data[i][columns.date_end],
      'status': data[i][columns.status],
      'owner': data[i][columns.owner],
      'type': data[i][columns.type],
      'event_id': data[i][columns.event_id]
    };
    var options = {
      'method' : 'post',
      'payload' : formData
    };
    var insertID = UrlFetchApp.fetch('https://webhooks.mongodb-stitch.com/api/client/v2.0/app/stitch-sheets-zkvuv/service/sheets/incoming_webhook/remove', options);
    eventIdCell.setValue(""); // Insert the new event ID

  }
}

From https://gist.github.com/mrlynn/94c63489014795aec4af34f874536531

Removing Data from MongoDB using an HTTP Service Webhook

And just as simply, we can create a new incoming webhook in Stitch called "remove" that will catch this POST request from our sheet script and remove the entries that match the data from the database.

exports = async function(payload) {
    const mongodb = context.services.get("mongodb-atlas");
    const eventsdb = mongodb.db("events");
    const eventscoll = eventsdb.collection("events");
    const delresult = await eventscoll.deleteOne({name:payload.query.name, location: payload.query.location});
    return { text: `Deleted ${delresult.deletedCount} items` };
   };

rawremoveEvent.js

Once we have the Sheets Scripts in place and the Stitch service functions, the rest is covered in a recent article where I create a slack slash command in about 10 minutes.

Wrapping Up and Resources

We've taken a sheet with rows and columns, created a script to send this data in object form to a Stitch webhook to be inserted into a MongoDB Database collection. Then we created a script with a couple of basic functions to export and remove the data via the URLFetchApp class through a MongoDB HTTP Webhook.

To wrap things up, let's create a menu item in your google sheet that calls each of these functions in the Google Script. onOpen is a special trigger in Google Scripts that fires automatically when the sheet is opened.

/****
* This function runs automatically and adds a menu item to Google Sheets
***/
function onOpen() {
 var sheet = SpreadsheetApp.getActiveSpreadsheet();
 sheet.setActiveSheet(sheet.getSheetByName("Events"));
 var entries = [{
    name : "Export Events to MongoDB",
    functionName : "exportEventsToMongoDB"
  },{
    name: "Remove All Events from MongoDB (Delete Documents)",
    functionName: "removeEventsFromMongoDB"
  }];
  sheet.addMenu("Demo Stitch Sheets Integration", entries);
 };

rawOnOpen.js

When we open our Google Sheet, we should see something like the following menu with two handy items that let you call the functions we created previously.

Export and Remove menu

Once you leverage the Export menu item your data is freed from the confines of the Google Sheets interface and you can easily create a Slack Slash command that uses MongoDB Stitch to expose this data to the rest of your team members.

Using the slack commands

For the complete Google Script, visit this gist. Grab, fork or clone the MongoDB Stitch code from this repository. Good luck and please comment below with your progress and follow me for more articles like this one.