Editor's note: Posted by Romain Vialard, a Google Developer Expert and developer of Yet Another Mail Merge, a Google Sheets add-on.
Yet Another Mail Merge is a Google Sheets add-on that lets users send multiple personalized emails based on a template saved as a draft in Gmail and data in a Google Sheet. It can send hundreds of emails, but this kind of operation usually takes a few minutes to complete. This raises the question: what should be displayed in the user interface while a function is running on server side for a long time?
Real-time notifications in Add-ons
Firebase is all about real-time and became the answer to that issue. Last December, the Apps Script team announced a better version of the HtmlService with far fewer restrictions and the ability to use external JS libraries. With Firebase, we now had a solution to easily store and sync data in real-time.
Combined, users are able to know, in real-time, the number of emails sent by an Apps Script function running server-side. When the user starts the mail merge, it calls the Apps Script function that sends emails and connects to Firebase at the same time. Every time the Apps Script function has finished sending a new email, it increments a counter on Firebase and the UI is updated in real-time, as shown in the following image.
Implementation
Inside the loop, each time an email is sent (i.e. each time we use the method GmailApp.sendEmail()), we use the Apps Script UrlFetch service to write into Firebase using its REST API. Firebase's capabilities makes this easy & secure and there’s no need for an OAuth Authorization Flow, just a Firebase app secret, as shown in the following example:
function addNewUserToFirebase() {
var dbUrl = "https://test-apps-script.firebaseio.com";
var secret = PropertiesService.getScriptProperties().getProperty("fb-secret");
var path = "/users/";
var userData = {
romainvialard:{
firstName:"Romain",
lastName:"Vialard",
registrationDate: new Date()
}
};
var params = {
method: "PUT",
payload : JSON.stringify(userData)
}
UrlFetchApp.fetch(dbUrl + path + ".json?auth=" + secret, params);
}
On the client side, thanks to the improved Apps Script HtmlService, we can use the official JS client library to connect to Firebase and retrieve the data stored previously. Specifically, the on() method in this library can be used to listen for data changes at a particular location in our database. So each time a new task is completed on server side (e.g. new email sent), we notify Firebase and the UI is automatically updated accordingly.
var fb = new Firebase("https://test-apps-script.firebaseio.com");
var ref = fb.child('users/' + UID + '/nbOfEmailsSent');
ref.on("value", function(data) {
if (data.val()) {
document.getElementById("nbOfEmailsSent").innerHTML = data.val();
}
});
More Firebase in Add-ons
In addition to the example above, there are other places where Firebase can be useful in Google Apps Script add-ons.
- “Yet Another Mail Merge” also offers paid plans and it needs to store our customer list. It turns out, Firebase is perfect for that as well. Each time someone buys a plan, our payment tool calls an Apps Script web app which writes the payment details in Firebase. So right after the purchase, the user can open the add-on and a function on server side will call Firebase and see that premium features should now be enabled for this user.
- Last but not least, at the end of a mail merge, we also use Firebase to provide real-time reporting of emails opened, directly in the sidebar of the spreadsheet.
Those are just a few examples of what you can do with Apps Script and Firebase. Don’t hesitate to try it yourself or install Yet Another Mail Merge to see a live example. In addition, there is a public Apps Script library called FirebaseApp that can help you start with Firebase; use it like any other standard Apps Script library.
For example, you can easily fetch data from Firebase using specific query parameters:
function getFrenchContacts() {
var firebaseUrl = "https://script-examples.firebaseio.com/";
var base = FirebaseApp.getDatabaseByUrl(firebaseUrl);
var queryParameters = {orderBy:"country", equalTo: "France"};
var data = base.getData("", queryParameters);
for(var i in data) {
Logger.log(data[i].firstName + ' ' + data[i].lastName
+ ' - ' + data[i].country);
}
}
Build your own add-ons via Google Apps Script. Check out the documentation (developers.google.com/apps-script) to get more information as well as try out the Quickstart projects there. We look forward to seeing your add-ons soon!
Romain Vialard profile | website
Romain Vialard is a Google Developer Expert. After some years spent as a Google Apps consultant, he is now focused on products for Google Apps users, including add-ons such as Yet Another Mail Merge and Form Publisher.
