MongoDB is a non-relational database service available that allows you to store a wide variety of data. In this article, we will be detailing how to access structured data stored in MongoDB Atlas via its API, as well as how to connect MongoDB to Google sheets.
How to connect MongoDB to Google Sheets
Follow these steps judiciously to successfully connect MongoDB to Google Sheets:
Step #1. Retrieve API key from the MongoDB Atlas database
- Login with your MongoDB account
- Go to the main page of MongoDB after logging in
- Click the Data API below the Data Services label in the left sidebar
- When you have done this, the first page that appears will ask you to select the data source(s) you would like to enable the API on
- Click on “Enable the Data API”. This appears when you click the Data API option on the left sidebar
- The Data API page will load in another tab
- Also copy the URL Endpoint and the Cluster Name; this will be needed in the code
- And click Create API Key
- A pop-up window tagged Create Data API Key will appear. Then click Generate API Key after adding a name for the key in the textbox for naming the key
- The API key will appear in a textbox; then copy it and store it in a secure location
- Then click Close
Step #2. Prepare the Google Sheet
The second step after retrieving the API key is to prepare your Google Sheets.
Create and prepare the spreadsheet and the code. You can use a code modified from MongoDB itself. You can modify the code and add more functionality to it; you can specify the number of results to print, the number of results to skip, whether to arrange the results in ascending or descending order, or based on their name. Then insert the Endpoint URL to its place.
Tip: Before inserting the URL, insert action or find at the end of the URL. Then replace the values for the Endpoint URL, the Cluster Name, and the API Key.
Also important to note in this process:
- Database name – you have to name the database, probably based on the name of the database stored in your MongoDB Atlas
- Collection name – each database in your MongoDB Atlas contains several collections. The collection name is needed to point to a specific collection
- Fields – the specific fields stored in a specific collection also needs to be listed in the line containing the fields; Go to the Databases > Click Collections tab > Click a specific collection name > the entries are displayed on the right side of the window
Step #3 Connect to Google Sheets
The third step is to connect MongoDB to Google Sheet.
- Go to Google Sheets
- Click Extensions
- Select Apps Script
- The Apps Script will load in a new tab, you can rename it
- The coding area is empty, so paste the code
- Click the Save button
- Click Run and a pop-up box labeled Authorization required will appear.
- Click on Review permissions
- A pop-up window labelled Google hasn’t verified this app will appear.
- Click Show Advanced
- Proceed to click the small link Go to *type in the name of your script in the blank page*
- A list of permissions will appear; click Allow
Tip: the Google hasn’t verified this app notification appears when running a code loaded in the Google Apps Script for the first time. By clicking “Show Advanced”, you can run the code when you clicking the link starting with Go to at the end. When you get past the app warning, the list of permissions needed to be granted to the script will be listed. Click Allow.
The final step will have the spreadsheet being updated with the list of results from the sample databases loaded into the MongoDB account.
Final thoughts
These steps are quite easy, follow them carefully to know how to connect MongoDB to Google Sheets.
