MongoDB Stitch is an amazing Back-end as a Service that provides a framework for integrating services from virtually any publicly available API. I've written a bunch on this at MongoDB's Blog but in this article, I'll share a quick lesson on how to integrate data from Google Sheets into MongoDB using MongoDB Stitch.

Stitching Sheets: Youtube Video

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.

NameURLTypeDate StartDate EndLocationStatusOwner
PyTennesseehttps://www.pytennessee.org/Conference09-Feb-201910-Feb-2019Nashville, TNApprovedSteve
HopHacks Spring 2019https://hophacks.com/Hackathon15-Feb-201917-Feb-2019Baltimore, MDDeferredJess
BrickHackhttps://brickhack.io/Hackathon16-Feb-201917-Feb-2019Rochester, NYDeferredDan
HackIllinoishttps://hackillinois.org/Hackathon22-Feb-201924-Feb-2019Urbana-Champaign, ILDeferredAdam
HackCUhttps://hackcu.org/Hackathon22-Feb-201923-Feb-2019Boulder, CODeclinedLisa
hoohacks.iohttp://hoohacks.io/Hackathon02-Mar-201903-Mar-2019Charlottesville, VAResearchingSusan
Confoohttp://confoo.caFM Event13-Mar-201915-Mar-2019Montreal, CanadaResearchingCarey
API the Docshttps://apithedocs.org/Conference15-Mar-201915-Mar-2019Chicago, ILResearchingBill
Game Developers Conferencehttp://gdconf.comConference18-Mar-201922-Mar-2019San Francisco, CAResearchingBill
Boise Code Camphttp://boisecodecamp.com/Conference23-Mar-201923-Mar-2019Boise, IdahoResearchingEli

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.6with some portions of 1.7 and 1.8 and provides 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.

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.

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.

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.

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 to remove entries in the same way.

Now, 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.

Now, when we open our Google Sheet, we should see something like the following menu.

For the complete Google Script, visit this GIST.

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