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.

1

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.

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.