google-apps-script Connecting to a Firebase project in GAS and transferring data from Google Spreadsheet to Firebase


Example

Install Firebase resource in the the AppScript

  • To do that click on Resources and then on Libraries.
  • Firebase has a unique project library key that need to be installed in the AppScript.
  • Click on Libraries The following pop-up appears. Enter the following project key in the textbox. MYeP8ZEEt1ylVDxS7uyg9plDOcoke7-2l This is the project library key for Firebase. enter image description here
  • Now in the version choose the stable public release version. enter image description here
  • Click on Save. Now Firebase is successfully installed in your AppScript for you to work.

Now let's take an example for reading and writing data from Firebase.

  • Now we take a sample table designed in Google Sheets. enter image description here
  • Now to build the database in the Firebase using this table in the sheets. Add the following code in the AppScript.
    function writeDataToFirebase() {
      var ss = SpreadsheetApp.openById("1LACsj0s3syAa9gvORdRWBhJ_YcXHybjQfHPgw3TLQ6g");
      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],
          semester:data[i][4],
          department:data[i][5],
        };
      }
      var firebaseUrl = "https://example-app.firebaseio.com/";
      var secret = "secret-key";
      var base = FirebaseApp.getDatabaseByUrl(firebaseUrl, secret);
      base.setData("", dataToImport);
    }

Replace the spreadsheet ID and the firebaseURL and the secret key.

How to find the firebaseURL and the secret key?

  • Go to your Firebase Dashboard and click on settings gear at top left corner. Click on Project Settings. enter image description here
  • Go to Service Accounts section you can find the databaseURL. This serves as the firebaseURL.
  • Now click on Database Secrets tab and you can find the secret key.

Now you have inserted the firebaseURL and the secret key. Now you are all set to go. Click on run code in the AppScript engine.

  • It will ask to review Permissions first time when you run.
  • Click Review Permissions and Allow.
  • Now you run your function and you can see the table created in Firebase Database.

To see the database go to the Firebase dashboard and Click on the Database you can view the database.