How to Track Your App’s Growth Using Google Sheets and Apps Script
Conduct app monitoring, data backup, and data analysis for your MongoDB
Monitoring health and growth metrics is critical for app performance and user experience. By tracking key metrics, such as downloads, active users, and engagement, we can better understand how our app is being used and whether it is meeting our goals.
This article shares a simple way to achieve this using Google Sheets and Apps Script. In this article, you will learn how to:
- Populate MongoDB data in Google Sheets for analysis
- Receive daily reports of your app’s growth metrics via email
From Your Database
Step 1: Allow network access from Google Sheets
If you don’t want to allow access from all networks (by specifying IP address as 0.0.0.0), you may choose to whitelist a specific list of IP addresses. For Google Apps Script, the list is here.
Step 2: Enable the Atlas Data API, retrieve the URL endpoint and API key
This URL endpoint and API key will access your database from Google Sheets. You may set the value for Data API access as Read Only.
For more detailed explanations, refer to MongoDB’s official documentation.
Most cloud databases can be accessed via API. If you are using another cloud database (not MongoDB Atlas), you may refer to the database’s official documentation to find out how to enable access from Google Apps Script.
From Google Apps Script
Step 3: Create a new Google Sheet and Apps Script
A sample script file named Code.gs will be created. Save the file. Click on the Run button to run myFunction.
Step 4: Set up myFunction to retrieve data from MongoDB
Copy the following code into myFunction. This code fetches data from your MongoDB and populates it in your Google Sheet. Remember to replace the configuration options with your personalized ones.
// Config for google sheets
const sheetId = "<GSHEET_ID>" // get from Google Sheet URL
const sheetname = "Sheet1" // change accordingly
// Config for mongo query
const apiKey = "<MONGO_API_KEY>"
const apiEndpoint = "<MONGO_URL_ENDPOINT>"
const database = "<MONGO_DATABASE_NAME>"
const collection = "<MONGO_COLLECTION_NAME>"
const dataSource = "Cluster0" // if you're using free tier, the default value is Cluster0
const limit = 50000
// Fetch data from mongo
const payload = {
method: "post",
contentType: "application/json",
payload: JSON.stringify({
filter: {},
skip: 0,
collection: collection,
database: database,
dataSource: dataSource,
limit: limit
}),
headers: { "api-key": apiKey }
};
const response = UrlFetchApp.fetch(apiEndpoint, payload);
const respContent = response.getContentText()
const documents = JSON.parse(respContent).documents // is an array of matching documents
// Transform data into gsheet readable format
var columns = Object.keys(documents[0]); // is an array of column headers
const values = documents.map(function(doc) {
return columns.map(function(x) {return doc[x]})
}) // is an array of arrays representing each row in google sheets
// Update values in the gsheet
const ss = SpreadsheetApp.openById(sheetId);
const sheet = ss.getSheetByName(sheetname);
sheet.getRange(1,1,1,columns.length).setValues([columns]);
sheet.getRange(2,1,values.length,columns.length).setValues(values);Step 5: Add a new sheet to calculate metrics
Use Google Sheets’ in-built formulas to generate any metric data you’re interested in. Store them in a single row.
These are the metrics I track for my Telegram bot, @cron_telebot.
Step 6: Create an email template
Create a new HTML file in Apps Script for the email template.
Copy and paste the following HTML code into the file:
<div dir="ltr">
<span id="gmail-docs-internal-guid-43c5b2ef-7fff-b3c4-efe7-083ca157a9f5">
<div dir="ltr" style="margin-left:0pt" align="left">Hello!</div>
<div style="margin-left:0pt" align="left">Here's your daily report <h3
class="gmail-LC20lb gmail-MBeuO gmail-DKV0Md"
style="font-weight:400;margin:0px;padding:5px 0px 0px;font-size:20px;line-height:1.3;display:inline-block">
🍻</h3>
</div>
<div dir="ltr" style="margin-left:0pt" align="left"><br>
<table style="border:none;border-collapse:collapse">
<colgroup>
<col width="160">
<col width="480">
</colgroup>
<tbody>
<tr>
<td
style="border-width:0.75pt;border-style:solid;border-color:rgb(204,204,204);vertical-align:bottom;background-color:rgb(229, 229, 229);padding:3pt;overflow:hidden">
<p dir="ltr" style="margin-top:0pt;margin-bottom:0pt">
<font face="arial, sans-serif">date</font>
</p>
</td>
<td
style="border-width:0.75pt;border-style:solid;border-color:rgb(204,204,204);vertical-align:bottom;background-color:transparent;padding:3pt;overflow:hidden">
<p dir="ltr" style="margin-top:0pt;margin-bottom:0pt">
<font face="arial, sans-serif"><?= date ?></font>
</p>
</td>
</tr>
<? for(var i = 0; i < store.length; i++) { ?>
<tr>
<td
style="border-width:0.75pt;border-style:solid;border-color:rgb(204,204,204);vertical-align:bottom;background-color:rgb(229, 229, 229);padding:3pt;overflow:hidden">
<p dir="ltr" style="margin-top:0pt;margin-bottom:0pt">
<font face="arial, sans-serif"><?= store[i].key ?></font>
</p>
</td>
<td
style="border-width:0.75pt;border-style:solid;border-color:rgb(204,204,204);vertical-align:bottom;background-color:transparent;padding:3pt;overflow:hidden">
<p dir="ltr" style="margin-top:0pt;margin-bottom:0pt">
<font face="arial, sans-serif"><?= store[i].value ?> </font>
</p>
</td>
</tr>
<? } ?>
</tbody>
</table>
</div>
<br>
</span>
</div>This template will generate an email that looks like this:
Step 7: Add the following code to myFunction to send the email
// Replace with your personalised configurations
const email = "<YOUR_EMAIL>"
const templateName = "Template.html" // change accordingly
const emailSubject = "✨ Report of the Day"
const sheetname = "<GSHEET_METRICS_SHEET_NAME>"
const sheet2 = ss.getSheetByName(sheetname);
const columns = sheet2.getRange(1,2,1,sheet2.getLastColumn()-1).getValues()[0];
const values = sheet2.getRange(2,2,1,sheet2.getLastColumn()-1).getValues()[0];
var store = []
for (var counter = 0; counter < sheet2.getLastColumn()-1; counter = counter + 1) {
store.push({
key: columns[counter],
value: values[counter],
})
}
var htmlTemplate = HtmlService.createTemplateFromFile(templateName);
htmlTemplate.date = new Date();
htmlTemplate.store = store; // template will get data from here
var htmlBody = htmlTemplate.evaluate().getContent();
MailApp.sendEmail({
to: email,
subject: emailSubject,
htmlBody: htmlBody
});Save the file and Run myFunction to test the email function. A dialog box will appear asking for authorization. Grant this project access to your email and Google Sheets.
Step 8: Create a daily cron job to send the email
Configure myFunction to run daily by creating a new trigger in Apps Script.
That’s it!
Extra: Using Script Properties
You may use script properties to store script configurations like your Mongo API key and access these properties in your script.
// Access script properties
const scriptProperties = PropertiesService.getScriptProperties();
const apiKey = scriptProperties.getProperty("MONGO_API_KEY")To add script properties, follow these instructions from the Google Workspace reference documentation:
- Open your Apps Script project.
- At the left, click “Project Settings”
- To add the first property, under Script Properties, click “Add script property.”
- To add second and subsequent properties, under Script Properties, click “Edit script properties” > “Add script property.”
- For Property, enter the key name.
- or Value, enter the value for the key.
- (Optional) To add more properties, click Add script property.
- Click Save script properties.
If you enjoyed this article, please visit my Medium homepage to browse more articles. Thank you for reading!
