Using the Atlas Data API with Google Apps Script
Rate this quickstart
This tutorial discusses the preview version of the Atlas Data API which is now generally available with more features and functionality. Learn more about the GA version here.
The MongoDB Atlas Data API is an HTTPS-based API which allows us to read and write data in Atlas where a MongoDB driver library is either not available or not desirable. In this article, we will see how a business analyst or other back office user, who often may not be a professional developer, can access data from and record data in Atlas. The Atlas Data API can easily be used by users unable to create or configure back-end services, who simply want to work with data in tools they know, like Google Sheets or Excel.
Google Office accesses external data using Google Apps Script, a cloud-based JavaScript platform that lets us integrate with and automate tasks across Google products. We will use Google Apps Script to call the Data API.
A common use of Atlas with Google Sheets might be to look up some business data manually, or produce an export for a third party. To test this, we first need to have some business data in MongoDB Atlas. This can be added by selecting the three dots next to our cluster name and choosing "Load Sample Dataset", or following the instructions here.
Our next step is to create a new Google sheet. We can do this by going to https://docs.google.com/spreadsheets/ and selecting a new blank sheet, or, if using Chrome, by going to the URL https://sheets.new . We end up viewing a sheet like this. Replace the name "Untitled spreadsheet" with "Atlas Data API Demo".
We are going to create a simple front end to allow us to verify the business inspection certificate and history for a property. We will get this from the collection inspections in the sample_training database. The first step is to add some labels in our sheet as shown below. Don't worry if your formatting isn't.exactly the same. Cell B1 is where we will enter the name we are searching for. For now, enter "American".
Now we need to add code that queries Atlas and retrieves the data. To do this, select Extensions -> Apps Script from the menu bar. (If you are using Google for Business, it might be under Tools->Script Editor instead.)
A new tab will open with the Apps Script Development environment, and an empty function named myFunction(). In this tab, we can write JavaScript code to interact with our sheet, MongoDB Atlas, and most other parts of the Google infrastructure.
Click on the name 'Untitled project", Type in "My Data API Script" in the popup and click Rename.
Before we connect to Atlas, we will first write and test some very basic code that gets a handle to our open spreadsheet and retrieves the contents of cell B1 where we enter what we want to search for. Replace all the code with the code below.
We need now to grant permission to the script to access our spreadsheet. Although we just created this script, Google requires explicit permission to trust scripts accessing documents or services.
Make sure the script is saved by typing Control/Command + S, then click "Run" on the toolbar, and then "Review Permissions" on the "Authorization required" popup. Select the name of the Google account you intend to run this as. You will then get a warning that "Google hasn't verified this app".
This warning is intended for someone who runs a sheet they got from someone else, rather than us as the author. To continue, click on Advanced, then "Go to My Data API Script (unsafe)". This is not unsafe for you as the author, but anyone else accessing this sheet should be aware it can access any of their Google sheets.
Finally, click "Allow" when asked if the app can "See, edit, create, and delete all your Google Sheets spreadsheets."
As we change our script and require additional permissions, we will need to go through this approval process again.
We now need to add a button on the sheet to call this function when we want to use it. Google Sheets does not have native buttons to launch scripts, but there is a trick to emulate one.
Return to the tab that shows the sheet, dismiss the popup if there is one, and use Insert->Drawing. Add a textbox by clicking the square with the letter T in the middle and dragging to make a small box. Double click it to set the text to "Search" and change the background colour to a nice MongoDB green. Then click "Save and Close."
Once back in the spreadsheet, drag this underneath the name "Search For:" at the top left. You can move and resize it to fit nicely.
Finally, click on the green button, then the three dots in the top right corner. Choose "Assign a Script" in the popup type lookupInspection. Whilst this feels quite a clumsy way to bind a script to a button, it's the only thing Google Sheets gives us.
Now click the green button you created, it should pop up a dialog that says 'American'. We have now bound our script to the button successfully. You change the value in cell B1 to "Pizza" and run the script again checking it says "Pizza" this time. Note the value of B1 does not change until you then click in another cell.
If, after you have bound a button to a script you need to select the button for moving, sizing or formatting you can do so with Command/Control + Click.
Now we have a button to launch our script, we can fill in the rest of the code to call the Data API and find any matching results.
From the menu bar on the sheet, once again select Extensions->Apps Script (or Tools->Script Editor). Now change the code to match the code shown below. Make sure you set the endpoint in the first line to your URL endpoint from the Atlas GUI. The part that says "amzuu" will be different for you.
We can now test this by clicking “Run” on the toolbar. As we have now requested an additional permission (the ability to connect to an external web service), we will once again have to approve permissions for our account by following the process above.
Once we have granted permission, the script will runLog a successful start but not appear to be continuing. This is because it is waiting for input. Returning to the tab with the sheet, we can see it is now requesting we enter our Atlas Data API key. If we paste our Atlas Data API key into the box, we will see it complete the search.
We can now search the company names by typing part of the name in B1 and clicking the Search button. This search uses an unindexed regular expression. For production use, you should use either indexed MongoDB searches or, for free text searching, Atlas Search, but that is outside the scope of this article.
Atlas API keys give the holder read and write access to all databases in the cluster, so it's important to manage the API key with care.
Rather than simply hard coding the API key in the script, where it might be seen by someone else with access to the spreadsheet, we check if it is in the user's personal property store (a server-side key-value only accessible by that Google user). If not, we prompt for it and store it. This is all encapsulated in the getAPIKey() function.
Should you enter the key incorrectly - or need to change the stored one. Change resetKey to true, run the script and enter the new key then change it back to false.
We have created this simple, sheets-based user interface and we could adapt it to perform any queries or aggregations when reading by changing the payload. We can also write to the database using the Data API. To keep the spreadsheet simple, we will add a usage log for our new search interface showing what was queried for, and when. Remember to change "amzuu" in the endpoint value at the top to the endpoint for your own project. Add this to the end of the code, keeping the existing functions.
When we add the data with this, we set the date field to be a date type in Atlas rather than a string type with an ISO string of the date. We do this using [EJSON](https://docs.mongodb.com/manual/reference/mongodb-extended-json/) syntax.
EJSON, or Extended JSON, is used to get around the limitation of plain JSON not being able to differentiate data types. JSON is unable to differentiate a date from a string, or specify if a number is a Double, 64 Bit Integer, or 128 Bit BigDecimal value. MongoDB data is data typed and when working with other languages and code, in addition to the Data API, it is important to be aware of this, especially if adding or updating data.
In this example, rather than using
{ date : (new Date()).toISOString() }, which would store the date as a string value, we use the much more efficient and flexible native date type in the database by specifying the value using EJSON. The EJSON form is { date : { $date : { $numberLong: <milliseconds since 1-1-1970> }}}.We must now modify our code to log each query that is performed by adding the following line in the correct place inside the
lookupInspection function.If we click the Search button now, not only do we get our search results but checking Atlas data explorer shows us a log of what we searched for, at what time, and what user performed it.
You can access the completed sheet here. This is read-only, so you will need to create a copy using the file menu to run the script.
Calling the Data API from Google Apps Script is simple. The HTTPS call is just a few lines of code. Securing the API key and specifying the correct data type when inserting or updating data are just a little more complex, but hopefully, this post will give you a good indication of how to go about it.
If you have questions, please head to ourdeveloper community websitewhere the MongoDB engineers and the MongoDB community will help you build your next big idea with MongoDB.
Rate this quickstart