Connect Google Sheets to MongoDB and Automate Updates with Triggers

A simple guide to connect sheets with MongoDB and automate updates.

Using the ‘Stitch’ service in MongoDB not longer works since this is now deprecated and has been replaced with Realm.

I’ll show you how to connect google sheets to MongoDB and automate updates with triggers using Realm in this article (you can also watch the YouTube video I made here if you prefer video format).

The first step is to create a MongoDB account if you haven’t already. You can signup and create a free one with ~half a GB of storage.

Create a new cluster and select your preferred cloud provider and region (this can take a few minutes to complete setting up).

Source: MongoDB

Once the cluster is created click on the ‘Realm’ button near the top left corner.

Then click the green ‘Create a New App’ button near the top right.

Name the application whatever you want and once created click on ‘3rd Party Services’ under the “Build” section on the left hand side of the screen. Click the ‘Add Services’ button and then click the ‘HTTP’ option. Name and add the service.

Next, click on the ‘Add Incoming Webhook’ button. We can leave the settings to whatever is specified by default unless you want to change them. Go to the ‘Function Editor’ tab and replace the code there with the code below:

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

Click the ‘Run’ button in the bottom right of the text editor and then click ‘REVIEW & DEPLOY’ to deploy this code on the app.

Once deployed go to your Goolge Sheet and click tools -> script editor.

And then replaced the empty default ‘MyFunction()’ with the below code:

function connectToMongDB(){
var sh1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Sheet1”)
var rng = sh1.getDataRange().getValues();
for(var i=1;i<rng.length; i++){
 var formData = {
 ‘name’: rng[i][0],
 ‘designation’:rng[i][1]
 }
 var params = {
 ‘method’ : ‘post’,
 ‘payload’: formData
 }
 var getId = UrlFetchApp.fetch(‘<YOUR_WEBHOOK_URL>’, params);
 sh1.getRange(i+1, 4).setValue(getId);
}
}

Notice, the sheet name in the third line is “Sheet1”. Make sure this is the same name as the name of your sheet in the Google Sheets. Also note, the last line of code with update the fourth column (column D) of our google sheet with a confirmation code from MongoDB that that row has been recorded. If you don’t want this then just get rid of the last line.

In the second to last line we’ll need to update the

'<YOUR_WEBHOOK_URL>'

with the unique webhook URL associated with our deployed service.

Go back to the deployed service and in the settings tab under the ‘WEBHOOK SETTINGS’ section copy the webhook URL and then paste this in place of

'<YOUR_WEBHOOK_URL>'

in the Google Sheets script.

Run the code in the Google Sheets script editor, making sure to name and save the script and giving MongoDB access to Google Sheets. If you get a “Script function not found: MyFunction” error just click the run button again as the function name hasn’t updated.

Once this runs we can go back to our cluster and click on the “collections” tab and then the name of our application. This will show us the data pulled in from our Google Sheet as well as the confirmation IDs for that row of data. If you kept the last line of code that we used for our service then the confirmation IDs will now be shown in the specified column of the Google Sheet.

Lastly, we’ll likely want to automate the data being pulled from the Google Sheet. We can do this by going back to the Google Sheets script editor and clicking the clock symbol to the left of the run arrow.

Click the “Add Trigger” button. We can then configure this run the script in Google Sheets based on various criteria, such as every time the sheet is updated (e.g. from a Google Form) or some interval of time.

Save this and now we our Google Sheets will automatically update our MongoDB cluster!

Thanks for reading and make sure to check out the YouTube video and subscribe there and on here for more technical guides!