Interested in speaking at MongoDB World 2022? Click here to become a speaker.
HomeLearnQuickstartUsing the Atlas Data Api with Google Apps Script

Using the Atlas Data Api with Google Apps Script

Updated: Jan 11, 2022 |

Published: Dec 01, 2021

  • Atlas
  • Atlas
  • JavaScript
  • ...

By John Page

Rate this article

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.

You can learn about enabling the Atlas Data API and obtaining API Keys here.

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.

#Prerequisites

Before we begin, we will need a Google account and the ability to create Google Sheets. We will also need an Atlas cluster for which we have enabled the Data API, and our endpoint URL and API Key. You can learn how to get these in this article or this video, if you do not have them already.

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.

https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/Screenshot_2021_11_29_at_09_50_02_af22cc2629.png

#Creating a Google Apps Script from a Google Sheet

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".

https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/shot1_16c1030d1b.png

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".

https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/shot2_a8c54ed267.png

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.)

https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/shot3_6ba8a3bb82.png

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.

https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/shot4_c582f247ff.png

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.

1function lookupInspection() {
2 const activeSheetsApp = SpreadsheetApp.getActiveSpreadsheet();
3 const sheet = activeSheetsApp.getSheets()[0];
4 const partialName = sheet.getRange("B1").getValue();
5 SpreadsheetApp.getUi().alert(partialName)
6}

#Granting Permissions to Google Apps Scripts

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".

https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/shot5_b4fcd9c198.png

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.

https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/shot6_ab231d894a.png

Finally, click "Allow" when asked if the app can "See, edit, create, and delete all your Google Sheets spreadsheets."

https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/shot7_e1199dccd2.png

As we change our script and require additional permissions, we will need to go through this approval process again.

#Adding a Launch Button for a Google Apps Script in a Spreadsheet


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."

https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/shot8_05abbe9798.png

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.

https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/shot9_e2ce85e389.png

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.

https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/shot10_21b20f1391.png

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.

#Retrieving data from MongoDB Atlas using Google Apps Scripts


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.

1const findEndpoint = 'https://data.mongodb-api.com/app/data-amzuu/endpoint/data/beta/action/find';
2const clusterName = "Cluster0"
3
4function getAPIKey() {
5 const userProperties = PropertiesService.getUserProperties();
6 let apikey = userProperties.getProperty('APIKEY');
7 let resetKey = false; //Make true if you have to change key
8 if (apikey == null || resetKey ) {
9 var result = SpreadsheetApp.getUi().prompt(
10 'Enter API Key',
11 'Key:', SpreadsheetApp.getUi().ButtonSet);
12 apikey = result.getResponseText()
13 userProperties.setProperty('APIKEY', apikey);
14 }
15 return apikey;
16}
17
18function lookupInspection() {
19 const activeSheetsApp = SpreadsheetApp.getActiveSpreadsheet();
20 const sheet = activeSheetsApp.getSheets()[0];
21 const partname = sheet.getRange("B1").getValue();
22
23
24 sheet.getRange(`C3:K103`).clear()
25
26 const apikey = getAPIKey()
27
28 //We can do operators like regular expression with the Data API
29 const query = { business_name: { $regex: `${partname}`, $options: 'i' } }
30 const order = { business_name: 1, date: -1 }
31 const limit = 100
32 //We can Specify sort, limit and a projection here if we want
33 const payload = {
34 filter: query, sort: order, limit: limit,
35 collection: "inspections", database: "sample_training", dataSource: clusterName
36 }
37
38 const options = {
39 method: 'post',
40 contentType: 'application/json',
41 payload: JSON.stringify(payload),
42 headers: { "api-key": apikey }
43 };
44
45 const response = UrlFetchApp.fetch(findEndpoint, options);
46 const documents = JSON.parse(response.getContentText()).documents
47
48 for (d = 1; d <= documents.length; d++) {
49 let doc = documents[d - 1]
50 fields = [[doc.business_name, doc.date, doc.result, doc.sector,
51 doc.certificate_number, doc.address.number,
52 doc.address.street, doc.address.city, doc.address.zip]]
53 let row = d + 2
54 sheet.getRange(`C${row}:K${row}`).setValues(fields)
55 }
56}

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.

https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/shot11_635765c12c.png

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.

#Securing Secret API Keys in Google Apps Scripts

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.

1function getAPIKey() {
2 const userProperties = PropertiesService.getUserProperties();
3 let apikey = userProperties.getProperty('APIKEY');
4 let resetKey = false; //Make true if you have to change key
5 if (apikey == null || resetKey ) {
6 var result = SpreadsheetApp.getUi().prompt(
7 'Enter API Key',
8 'Key:', SpreadsheetApp.getUi().ButtonSet);
9 apikey = result.getResponseText()
10 userProperties.setProperty('APIKEY', apikey);
11 }
12 return apikey;
13}

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.

#Writing to MongoDB Atlas from Google Apps Scripts

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.

1const insertOneEndpoint = '[https://data.mongodb-api.com/app/data-amzuu/endpoint/data/beta/action/insertOne](https://data.mongodb-api.com/app/data-amzuu/endpoint/data/beta/action/insertOne)'
2
3function logUsage(query, nresults, apikey) {
4const document = { date: { $date: { $numberLong: ${(new Date()).getTime()} } }, query, nresults, by: Session.getActiveUser().getEmail() }
5console.log(document)
6const payload = {
7document: document, collection: "log",
8database: "sheets_usage", dataSource: "Cluster0"
9}
10
11const options = {
12method: 'post',
13contentType: 'application/json',
14payload: JSON.stringify(payload),
15headers: { "api-key": apikey }
16};
17
18const response = UrlFetchApp.fetch(insertOneEndpoint, options);
19}

#Using Explicit Data Types in JSON with MongoDB EJSON


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> }}}.

#Connecting up our Query Logging Function

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.

1 const response = UrlFetchApp.fetch(findendpoint, options);
2 const documents = JSON.parse(response.getContentText()).documents
3
4 logUsage(partname, documents.length, apikey); // <---- Add This line
5
6 for (d = 1; d <= documents.length; d++) {
7...

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. https://mongodb-devhub-cms.s3.us-west-1.amazonaws.com/shot13_b098d0fd26.png

#Conclusion

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 article
MongoDB logo
© 2021 MongoDB, Inc.

About

  • Careers
  • Investor Relations
  • Legal Notices
  • Privacy Notices
  • Security Information
  • Trust Center
© 2021 MongoDB, Inc.