Google Docs Table to MongoDB Using Data API
If you have stumbled on this article and you are willing to explore it after looking at the title, it is likely that you have some interest in Google Docs or MongoDB.
Google docs are often used as a collaboration tool, and tables are a common way of recording structured information in these docs.
In example company, the team meetings are being run through Google docs. The team members post their questions in the table, and members of the leadership team provide the answers in detail. Several people are able to put the questions and edit the answers at the same time.
This document is then shared with the team members after completion.
Each meeting has a new document, and a new set of questions and answers. Similarly other meetings are also run in the same way with their own new doc.
Tom wants to look for the questions that he has asked in these meetings over the past one year. He also wants to look for all the answers where Project Alpha was referenced. To do this, he will need to look for all the meeting invites, find the doc links and then search through this information in each doc individually. Sounds difficult!
Tom wants to solve this problem by having all the questions and answers in one place and then allow easier searches on it.
However, the first piece of the puzzle is to get this data into his favorite database service, MongoDB Atlas which provides a free tier cluster and also allows creating full text search indexes through inbuilt service called Atlas Search.
Let’s help Tom get his Google docs data into MongoDB Atlas.
- Open the document and go to Tools. Click Script Editor.
2. Open Google Apps Script
3. Rename the file Code.gs as sendToMongoDB.gs
4. Copy the below Code snippet to this file and save the project as sendFirstTableToMongoDB.
In this code snippet, we are
- Creating the menu item for running the script. (Not needed if you want to run the script only once. You can use the Run button on the top as well.
- The menu item is created when the document is loaded.
- Then we look for the first table in the document, and then covert that to JSON which is then posted to MongoDB using the Data API.
The MongoDB Data API allows us to read and modify data in MongoDB Atlas over HTTP. Follow this link to get started with Data API.
5. Reload the document. You’ll find the button under Add-Ons.
When the button is clicked, the script finds the table in the document, reads the data and posts that to MongoDB Atlas.
Tom can now look into his MongoDB Atlas cluster and check the data.
Next, Tom is going to leverage MongoDB’s Application Data Platform services to power the search across his documents. He is planning to give access to this application to his colleagues but he would take you on that journey some other day.
If you would like to encourage Tom for this journey, send him a clap!
