creating a listener in a google sheet

[Peter Driessen]

Hi all, 

I managed to save data to firebase when a cell changes in my Google Sheet, this works great. This is the code:

But the next thing I would like to achieve is the following. On another device / service. I want to edit some values. When a value changes, I want to change it in Google Sheet too. 

function firetest(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var r = sheet.getActiveCell();
  var val = sheet.getRange(r.getRow(), 1).getValue();
  var objectdata = {
      column1: sheet.getRange(r.getRow(), 2).getValue(),
      column2: sheet.getRange(r.getRow(), 3).getValue()
  };
      var data =
      {
        "method" : "put",
        "payload" : JSON.stringify(objectdata)
      };
 UrlFetchApp.fetch("https://glowing-heat-68.firebaseio.com/vensters/" + val + ".json", data );
}

The final thing you need to do is to create a trigger for this function. From the script editor, choose Resources > Current project's triggers and set up a trigger.

I've search the internet around, but somehow I can't create a listener in Google Apps Script for changes in firebase. 

Who can help me? Thanks a lot.

Regards, Peter

[Rob DiMarco]

Hi Peter -

First off, wow! I don't think I've seen Firebase and Google Sheets wired-up before - very neat!

I can't say for sure, but I suspect that this isn't currently possible based upon restrictions imposed by Google Sheets. The Firebase JavaScript client library assumes either a full browser or full Node.js environment, and Sheets likely doesn't conform. That said, there's a couple things we could try:

1. If you manage to get all Firebase client code into Sheets somehow, you can manually play with the transports that we'll use to connect to the server, using either Firebase.INTERNAL.forceLongPolling() or Firebase.INTERNAL.forceWebSockets().
2. I'm not bearish on it working since it is a long-lived socket that periodically emits data, but Firebase REST API Streaming is intended to be used when you cannot use a client but want to be able to create a listener.

My hunch is that you'll have to solve this by periodically polling Firebase for changes via the REST API, just like how you're writing to Firebase in the code snippet above. You could fetch data from Firebase on some regular interval, say every N seconds.

If you go the polling route, you might find that you're constantly downloading the same data which - if large - might be expensive or slower than you'd like. Consider expanding your solution to actually do two writes - write the data to one location, and write a signaling mechanism to another. In this case, your polling-based listener could periodically check the signaling location that only stores some lightweight data (such as the most recent timestamp of a broader change) and only if some criteria is met (such as your local client no longer having the latest version, or most recent timestamp) would you go fetch the "full" data.

Hope that helps! Let us know what you come up with.

Thanks -

Rob DiMarco

Engineer @ Firebase

--
You received this message because you are subscribed to the Google Groups "Firebase Google Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebase-tal...@googlegroups.com.
To post to this group, send email to fireba...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/firebase-talk/2f88856d-3e2f-4b80-b9ed-a1d3dd7d336c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jacob Wenger]

Also, there was a recent Google Groups thread on hooking Firebase up to Blockspring and also Google Sheet. It included a link to a blog post on reading data from Firebase into Google Sheets. It might contain an answer or two for you.

Jacob

To view this discussion on the web visit https://groups.google.com/d/msgid/firebase-talk/CAF-3uRtFcvz%3D%3DaZ%3DJHvn2DxPc%3DzH0jFEhb6qtUXgJRYWkWUfqQ%40mail.gmail.com.