Connect your MongoDB Atlas Database to Google Sheets via Google Apps Script

One of the major non-relational database services available today is MongoDB. MongoDB allows you to store a wide variety of data. In this tutorial we will learn how to access structured data stored in MongoDB Atlas via its API.

Retrieve API key from MongoDB Atlas database

Step 1: On the main page of MongoDB after logging in, click the Data API below the Data Services label in the left sidebar.

‍

Step 2: The first page that will appear will ask you to select the data source(s) you would like to enable the API on. 

Select All Data Sources selected, then click Enable the Data API. 

‍

Step 3: The Data API page will load, where you can access. Copy the URL Endpoint and the Cluster Name; we will need it in the code. Click Create API Key.

‍

Step 4: A pop-up window labeled Create Data API Key will appear. After adding a name for the key, click Generate API Key. 

‍

Step 5: The API key will appear in a textbox. Copy it and store it in a secure location. Click Close. 

‍

Prepare the spreadsheet and the code

The code that we will use is modified from MongoDB itself; we will modify this to add more functionality, such as specifying the number of results to print, the number of results to skip, and whether to arrange the results in ascending or descending order based on their name.

For starters, we created the following layout:

‍

The query string is in B1, the number of results to print is in D1, the number of results to skip is in F1, and the order options are in H1. We reference this in the code that we will use. The results will be printed starting Row 4. You can type down a specific string in B1. If you want to list all results, simply type a space in B1. We will use this for our example.

Below is the modified code(below the picture is a text version of the code to copy):

const findEndpoint = ’*insert endpoint url here*action/find’;

const clusterName = "*insert cluster name here*"

const apikey = "*insert api key here*"

function lookupInspection() {

  const activeSheetsApp = SpreadsheetApp.getActiveSpreadsheet();

  const sheet = activeSheetsApp.getSheets()[0];

  const partname = sheet.getRange("B1").getValue();

  const limit = sheet.getRange("D1").getValue();

  const skips = sheet.getRange("F1").getValue();

  const orderopt = sheet.getRange("H1").getValue();

  var orderr = 0;

 sheet.getRange(`A4:I${limit+3}`).clear()

if (orderopt == "Ascending") {

        orderr=1;

    } else if (orderopt == "Descending") {

        orderr=-1;

    }

 //We can do operators like regular expression with the Data API

 const query = { business_name: { $regex: `${partname}`, $options: 'i' } }

 const order = { business_name: orderr }

 //We can Specify sort, limit and a projection here if we want

 const payload = {

  filter: query, sort: order, limit: limit, skip: skips,

  collection: "*collection name*", database: "*database name*", dataSource: clusterName

 }

 const options = {

  method: 'post',

  contentType: 'application/json',

  payload: JSON.stringify(payload),

  headers: { "api-key": apikey }

 };

 const response = UrlFetchApp.fetch(findEndpoint, options);

 const documents = JSON.parse(response.getContentText()).documents

 // logUsage(partname, documents.length, apikey); //Log Usage

 for (d = 1; d <= documents.length; d++) {

  let doc = documents[d - 1]

  fields = [[doc.business_name, doc.date, doc.result, doc.sector, 

       doc.certificate_number, doc.address.number,

  doc.address.street, doc.address.city, doc.address.zip]]

  let row = d + 3

  sheet.getRange(`A${row}:I${row}`).setValues(fields)

 }

}

Before inserting the Endpoint URL to its place, add action/find in the end of it. Replace the values for the Endpoint URL, the Cluster Name, and the API Key. 

Besides this information, you also need to note the following:

Database name - in our example, its sample_training, but that depends on the name of the database stored in your MongoDB Atlas.

Collection name - each database contains several collections. You need the collection name to point to a specific collection.

Fields - the specific fields stored in the specific collection needs to be listed in the line containing the fields. To check which are used in the collection, go to the Databases, click Collections tab, then click a specific collection name. Some of the entries are displayed on the right side of the window.

‍

Insert the script to Google Apps Script and run it

Step 1: On Google Sheets, click Extensions then Apps Script. 

‍

Step 2: The Google Apps Script will load in a new tab. You can rename it.

‍

Step 3: Paste the code. Check the previous section for the code to paste. 

Step 4: Click the Save button, then click Run. A pop-up box labeled Authorization required will appear. Click Review permissions.

‍

Another pop-up window will appear, labeled Google hasn’t verified this app. Click Show advanced, then click the small link Go to *name of your script*. A list of permissions will appear after it. Click Allow.

‍

‍

Step 5: The spreadsheet should be updated with the list of results:

‍

The data used is from the sample databases loaded into the MongoDB account.

If you changed one of the inputs in Row 1, you need to run the code again in the Google Apps Script by clicking Run. 

More functionality?

You can further improve the spreadsheet and the code by checking our existing tutorials here at Lido. If you know a little bit about programming, you can perhaps even improve the code given to us by MongoDB. However, if this is too much of a hassle for you, we offer a better solution: Lido app. No more need for copy-pasting and improving the codes just to load data from MongoDB to Google Sheets! Lido integrates directly with MongoDB so you can go straight to building dashboards for the precious metrics that you need to make important business decisions. Get started here.

References

Using the Atlas Data Api with Google Apps Script