Managining Firebase database using Google Sheets via Google Apps Script.

Working with Google Apps Script has been a great learning for me. The things one can automate, and the level to which you can automate certain functions using just few lines of code is amazing. G Suite provides various tools to get started and work with using Google Apps Script.

Recently we launched a cashback platform called Paybackhub which gives customers exclusive cashbacks on purchase of their favorite software tools. We are also launching an offers page on Paybackhub listing amazing offers on software tools and more. Handling so much data about offers and cashback is no easy task. Adding data in a complex realtime databse everytime we get a new company onboard is tedious. Also, if some error occurs when changing values it can cost us a lot. We also wanted a simpler method to onboard a compnay to our platfrom. Easy to edit, easy to manage and quick. Thats when we looked into our options and decide to go with adding new updates on google sheets which through a Google Apps script will automatically update the neccessary information to a real time database located on Firebase.

Paybackhub webpage

Handling data on Google sheets is easy and comfortable. We add company name, cashback offered by our platfom and other neccessary details to note are entered into the sheets. When all these details are verified, a script is run which updates needed vital data in a stuctured format to our database hosted on Firebase. Data is fetched from here and all the functions are carried out by our website.

Getting into automating Firebase with Google sheets.

Before getting into code, having a google account is mandatory. Create an application on firebase . After creating an application, move on to Databse tab an select relative database. For security concerns, rules are enabled on the database. These rules allow users and developers the liberty to read/write the database. As we are building an application, we move on to the rules tab and do the following

{
 “rules”: {
 “.read”: true,
 “.write”: true
 }
}

For testing, we enable both read and write. It means that anyone having access to the database url can read and write data into it.

Apps Script is a language derived from Javascript. So basics of Javascript is enough to get along really well with Apps Scipt.

Understanding methods used

1. getDatabaseByUrl(url, optSecret) : Given the url of the firebase application’s database and a secret code(maintains security), this method returns the database at that url.

var url = "URL_OF_FIREBASE_APPLICATION";
var optSecret = "SECRET_CODE";
var database = FirebaseApp.getDatabaseByUrl(url, secret);
//getData
Logger.log(database.getData("companies/offers")); //give a path 
//pushData
var data = {"companyName" : "Paybackhub", "desc" : "A cashback platform"};
var dataToPush = database.pushData("companies", data); //path, data
Logger.log(dataToPush);
//setData
var data = {"companyName" : "Paybackhub", "desc" : "A cashback platform"};
var dataToSet = database.setData("companies/paybackhub/details", data);
Logger.log(dataToSet); 
//updateData
var data = {"desc" : "A cashback platform for software tools"};
var dataToUpdate = database.updateData("companies/paybackhub/details",data);
Logger.log(dataToUpdate);
//removeData
var dataRemoved = database.removeData("companies/paybackhub");
Logger.log(dataRemoved);

2. getData(path, parameters) : This methods will get all the data given in the path. In the example showed above the variable database hold values of all the database found in the application. The getData method uses this variable to traverse the database and get data from a particular path.

3. pushData (path, data, optQueryParameters) : It generates a new child location and pushes the data at that path (i.e., the generated location). The contents in the variable data gets stored in the new child companies.

4. setData(path, data, optQueryParameters) : This methods writes data at a particular path sent as a parameter. The contents in the variable data gets stored at the path companies/list (i.e., a specific location)

5. updateData(path, data, optQueryParameters) : It is used to update data at a specific path without changing the existing data. Here data represents the child that needs to be overwritten.

6. removeData(path, optQueryParameters) : This method is used to delete all the data present at a particular path in the database. In the code above, all the entries under companies/paybackhub will be removed.

Adding Firebase library to Apps Script

To use above functions, the library which enables to use them must be included. In the script editor, click on Resources > Librairies and add the project key “MYeP8ZEEt1ylVDxS7uyg9plDOcoke7–2l” . After clicking on Select, choose the latest version then you are good to go.

Google Sheets

In the google sheets, fill in all the data that needs to pushed into these rows and columns. To know more about accessing data using Apps Scripts go through this wonderful docs by google.

This has helped paybackhub manage database very easily. Daily updates about companies are filed in many times during the day. So Handling all these updates on Google Sheets help us very much. Just by the press of a button all these changes are updated onto Firebase and all our customers can shop through our website.

For such periodic manual updates, using this method will be very beneficial and works efficiently. More about dealing with Firebase and Google Sheets here.

Hope this article was useful and do try it out sometime.

For cashbacks on software tools checkout Paybackhub. We add 5 awesome tools every week having upto 30% cashback. Cashbacks worth about 3 month’s pay can be obtained from paybackhub. Hope this helps you :)

If you have any feedback regarding the article and also Paybackhub, please reach out on twitter.

Follow paybackhub on Twitter : https://twitter.com/paybackhub

Contact me on twitter : Supriya S