Goal

This tutorial shows how to read & write data into Firebase from Apps Script.

Prerequisites

Before beginning this tutorial, you should have created a new project on Firebase. You should have also added our FirebaseApp library for Apps Script in a new Apps Script project (see installation).

Overview

This tutorial is divided into the following sections:

• Importing data to Firebase from a Google Sheet

• Updating existing data

• Reading data from Firebase with different queries

• Importing data to Firebase from a Google Sheet

• Edit security rules in Firebase

By default, a new Firebase database is set with security rules preventing anonymous read and write access. This is a good (secure) behaviour but for the purpose of this exercise we will disable this security.

In Firebase, go to Database >> Rules and allow public read & write access by setting values to true.

This means that anyone who has the URL of your database can read and write data into it. To learn more about the security model of Firebase, see the official documentation.

Run your first Apps Script function to import data in Firebase

Our dataset for this tutorial is a contact list stored in a Google Sheet.

As Firebase is a JSON store, we need to retrieve the data from this spreadsheet and convert them to a JSON object. Copy-paste the code below in the script editor and replace the Firebase URL by yours. Then run the code.

function writeDataToFirebase() {

var ss = SpreadsheetApp.openById("1rV2_S2q5rcakOuHs2E1iLeKR2floRIozSytAt2iRXo8");

var sheet = ss.getSheets()[0];

var data = sheet.getDataRange().getValues();

var dataToImport = {};

for(var i = 1; i < data.length; i++) {

var firstName = data[i][0];

var lastName = data[i][1];

dataToImport[firstName + '-' + lastName] = {

firstName:firstName,

lastName:lastName,

emailAddress:data[i][2],

country:data[i][4],

department:data[i][5],

weight:data[i][6],

birthDate:data[i][7]

};

}

var firebaseUrl = "https://script-examples.firebaseio.com/";

var base = FirebaseApp.getDatabaseByUrl(firebaseUrl);

base.setData("", dataToImport);

}

After running the code, you should be able to see your data in Firebase.

Secure your database

We have seen that it's easy to write data to Firebase if you have allowed anonymous write access in your security rules. But that was just for the purpose of the demo, in the real world, you will need to secure accesses. For example you can keep public read access put prevent any unauthenticated write attempt:

{

"rules": {

".read": true,

".write": false

}

}

Once you have changed your security rules, you'll need to use a secret to write data into Firebase. You can get your secret from the Firebase dashboard and use it with the method getDatabaseByUrl(url, secret)

Simply go to Project Settings >> Service Accounts >> Database Secrets

Google warns that this authentication method is deprecated. In fact you can still use this method but standard Service Accounts are more secure and are recommended. As Database secrets are easier to use, far from really deprecated and safe enough for most use cases, that's what we will use here.

var firebaseUrl = "https://script-examples.firebaseio.com/";

var secret = "Vt0nb3HnLOK6wImGbG1PxZnBAK4lLN2ysklZyF5Z";

var base = FirebaseApp.getDatabaseByUrl(firebaseUrl, secret);

Updating existing data

To update a specific record, you can simply indicate the path / location on which you want to edit the data and use the setData() method.

function updateData() {

var firebaseUrl = "https://script-examples.firebaseio.com/";

var secret = "Vt0nb3HnLOK6wImGbG1PxZnBAK4lLN2ysklZyF5Z";

var base = FirebaseApp.getDatabaseByUrl(firebaseUrl, secret);

base.setData("Alex-MARTIN/country", "England");

}

If you want to update several information but not all, best is to use the updateData() method (eg. update Country & Weight but keep the other info: Email Address, First Name,... unchanged)

function updateData() {

var firebaseUrl = "https://script-examples.firebaseio.com/";

var secret = "Vt0nb3HnLOK6wImGbG1PxZnBAK4lLN2ysklZyF5Z";

var base = FirebaseApp.getDatabaseByUrl(firebaseUrl, secret);

base.updateData("Alex-MARTIN", {country:"England", weight:70});

}

Finally, if you want to add a new record (eg. a new contact), you can choose a path / key that doesn't already exist.

function addNewContact() {

var firebaseUrl = "https://script-examples.firebaseio.com/";

var secret = "Vt0nb3HnLOK6wImGbG1PxZnBAK4lLN2ysklZyF5Z";

var base = FirebaseApp.getDatabaseByUrl(firebaseUrl, secret);

base.setData("Romain-VIALARD", {firstName:"Romain", country:"France", weight:70});

}

Reading data from Firebase with different queries

If you want to retrieve all data stored in Firebase, simply perform a getData() with no path / location.

function getAllData() {

var firebaseUrl = "https://script-examples.firebaseio.com/";

var base = FirebaseApp.getDatabaseByUrl(firebaseUrl);

var data = base.getData();

for(var i in data) {

Logger.log(data[i].firstName + ' ' + data[i].lastName);

}

}

To retrieve a specific record, simply indicate the right path / key.

function getContact() {

var firebaseUrl = "https://script-examples.firebaseio.com/";

var base = FirebaseApp.getDatabaseByUrl(firebaseUrl);

var contact = base.getData("Alex-MARTIN");

Logger.log(contact);

}

Or directly get a specific the value (eg. Country for a specific contact)

function getContactCountry() {

var firebaseUrl = "https://script-examples.firebaseio.com/";

var base = FirebaseApp.getDatabaseByUrl(firebaseUrl);

var country = base.getData("Alex-MARTIN/country");

Logger.log(country);

}

If you want to perform queries to retrieve only contacts that match a specific condition (eg. all French people), we first need to tell Firebase the keys we want to index (in this case, "Country"). See official documentation on Indexing data.

This is done in the Security & Rules section where we can add an .indexOn rule.

After that, you can use the following parameters to perform our queries: orderBy, limitToFirst, limitToLast, startAt, endAt, equalTo.

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

}

}