CODEWITHSUNDEEP
firebasefirebase-realtime-databasegoogle-apps-script
Jim Clermonts
Jim Clermonts

Reputation: 2640

Saving google sheets data into Firebase using Google Apps script

I'm trying to have my google sheets synced with my firebase database. I'm not very experienced with javaScript, so is it possible using the below method? The idea is that it would automatically sync every time a new row gets created/updated/deleted. I know that I need the script files but not sure how to import them in the .gs file, so that's why it's in the html. Many thanks!

enter image description here

enter image description here

translate.gs

function saveToFirebase() {
  var config = {
    apiKey: "MY_API_KEY",
    authDomain: "MY_DOMAIN.firebaseapp.com",
    databaseURL: "MY_DOMAIN.firebaseio.com",
    projectId: "MY_DOMAIN",
    storageBucket: "MY_DOMAIN.appspot.com",
    messagingSenderId: "MESSAGE_ID"
  };
  firebase.initializeApp(config);
  var database = firebase.database();

  database.ref('food/' + MY_USER_UID).set({
    name: "pizza funghi",
  }); 
}

sidebar.html

<!DOCTYPE html>
<html>
  <head>
<script src="https://www.gstatic.com/firebasejs/4.12.0/firebase-app.js"></script>
<script src="https://www.gstatic.com/firebasejs/4.12.0/firebase-auth.js"></script>
<script src="https://www.gstatic.com/firebasejs/4.12.0/firebase-database.js"></script>

  </head>
  <body>

  </body>
</html>

Upvotes: 2

Views: 3112

Answers (1)

Jesse Scherer
Jesse Scherer

Reputation: 1512

There is a third-party libarary which integrates with Firebase's REST API. If you're comfortable using it, this becomes pretty straightforward.

First we'll need to create a tab to track changes. We need the identity of those who make changes, so we have to break this into two parts - a simple onEdit trigger which runs as the modifying user, and an installable trigger which I'll call uploadChanges. The latter is what talks to Firebase.

  1. Create a tab called changes
  2. Add a frozen row with the following headers:
    • Uploaded
    • User
    • Value

Install the third party Firebase library

  1. Begin by clicking Resources > Libraries in the script editor, then pasting MYeP8ZEEt1ylVDxS7uyg9plDOcoke7-2l in the "Find a Library" box. Hit Save.
  2. Opt for stability by choosing the latest public release, or choose the latest release (I chose latest while writing this).
  3. Click OK
  4. Now would be a good time to peruse the reference docs so you know what I'm up to in the below instructions :-)

Set up security (I'm assuming you want this script to run as you)

  1. Make your Google account (which runs the script) be at least an Editor for your Firebase project.
  2. Set the appropriate authorization scopes for your App Script project:
    1. Go to File > Project Properties > Scopes in the App Script editor
    2. Select View > Show manifest file (the manifest file is usually hidden by default)
    3. Add https://www.googleapis.com/auth/userinfo.email and https://www.googleapis.com/auth/firebase.database to the oauthScopes array (add it if it's not already there)
    4. Save the manifest file. Next time you run the script you'll get a pop-up asking about permissions.

The equivalent of your translate.gs above, which always just sets your food to 'pizza funghi`, would look like this:

function saveToFirebase() {
  var dbUrl = "MY_DOMAIN.firebaseapp.com";  // Set appropriately
  var token = ScriptApp.getOAuthToken();    // Depends on security setup above
  var firebase = FirebaseApp.getDatabaseByUrl(dbUrl, token);
  newData = {
    name: "pizza funghi",
  };
  firebase.setData('food/' + MY_USER_UID, newData);
}

But you said you wanted to update Firebase on every save. To do this you really just want to rip off one of the various onEdit tutorials floating around the net. The resulting onEdit should look something like this:

function onEdit(e) {
  // First get stuff about the edit. 
  // This approach only gets the top left cell of a multi-cell edit.
  var editRange = e.range;                  // The edited range
  var newValue = editRange.getValue();      

  // Next, who is the editor? Remove the `split` for full email.
  var username = Session.getActiveUser().getEmail().split('@')[0];
  if (username == '') {
    username = SOME_REASONABLE_DEFAULT;     // Or give up if you wish
  }

  // Finally save the change
  SpreadsheetApp.getActiveSpreadsheet()
                .getSheetByName('changes')
                .appendRow([false, username, newValue]);
}

function uploadChanges() {
  // Attach to Firebase
  var dbUrl = "MY_DOMAIN.firebaseapp.com";  // Set appropriately
  var token = ScriptApp.getOAuthToken();    // Depends on security setup above
  var firebase = FirebaseApp.getDatabaseByUrl(dbUrl, token);

  // Get content of changes tab
  var changeSheet = SpreadsheetApp.getActiveSpreadsheet()
                                  .getSheetByName('changes');
  var changeData = changeSheet.getDataRange()
                              .getValues();
  // Upload all new-to-us changes
  for (var i = 1; i < changeData.length; i++) {
    if (changeData[i][0]) {
      continue;               // We already uploaded this one
    }
    changeData[i][0] = true;  // Optimistically assume we'll succeed       
    var newData = {
     name: changeData[i][2]
    };
    var username =  changeData[i][1];
    firebase.setData('food/' + username, newData);
  }
  // Blanket update of change-data sheet to update upload status
  changeSheet.getRange(1, 1, changeData.length, changeData[0].length)
             .setValues(changeData);

}

Lastly, set up some triggers.

  1. Choose Edit > Current Project's Triggers in the script editor
  2. Add a new trigger for onEdit
    • Choose onEdit from the leftmost Run dropdown
    • Choose From spreadsheet in the Events dropdown
    • Then choose On edit in the rightmost dropdown
  3. Add a new trigger for uploadChanges
    • Choose uploadChanges from the leftmost Run dropdown
    • Choose Time-driven from the Run dropdown
    • Set up a schedule that's appropriate to your needs

EDIT: My original script had you doing everything in onEdit, which tehhowch correctly points out won't work since we're talking to another service. I've updated to stage to a "changes" tab which I include in setup. My new approach maintains a perpetual record of old uploads; for performance you might instead choose to just clear the changes sheet once you've done the upload.

Upvotes: 3

Related Questions