In this article:

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.

MongoDB Atlas main page.


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. 

Enable the Data API. This appears after clicking the Data API option on the left sidebar.


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

The drop-down box for selecting the data source to enable the API on. All Data Sources selected option chosen.


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.

Data API screen. This contains the URL Endpoint, which needs to be modified a bit later on. The Create API Key also appears on the upper-right side of the page.


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

Create Data API Key pop-up box. There is a textbox for naming the key. Generate API Key button is located below the textbox. 


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

Create Data API Key pop-up box. Generate API Key button replaced by a textbox containing the actual API Key. 


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:

Blank spreadsheet containing the template for the data


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.

A list of fields for each collection can be accessed through Databases, then Collections tab. 


Insert the script to Google Apps Script and run it

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

Google Sheets. Extensions option in the main toolbar, Apps Script highlighted in the drop-down list.


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

Google Apps Script. The coding area is empty. The code will be inserted here.


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.

Pop-up box labeled “Authorization required”. Review permissions option on the lower-right side of the box.


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.

Google hasn’t verified this app warning. This appears when running a code loaded in the Google Apps Script for the first time. Clicking Show Advanced allows you to choose to run the code by clicking the link starting with Go to at the end.


After the app warning, the list of permissions needed to be granted to the script will be listed. Click Allow.


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

The spreadsheet after running the script. The data is from the dummy database bundled with the MongoDB account. 


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


Automate everything you track in spreadsheets with Lido
Learn more

Level up your Google Sheets skills with our free Google Sheets automation guide

Wasting too much time doing things manually in spreadsheets? Want to spend more time doing what you love? Our 100% free, 27-page Google Sheets automation guide is full of new tips and tricks that will save you time and money!