Reading Data from Firebase…with Google Apps Script

问题:

So, as the title suggests, I am currently working on a rather troublesome problem. Here's the scenario.

I have a Google Spreadsheet containing a template with name, email, and expiration date. However, it contains no actual data. The data itself is located in a Firebase and is constantly changing. My goal, then, is to allow the script.gs file attached to my spreadsheet to read from Firebase, either through a timed interval or by using Firebase's typical dataRef.on('value',function(){});.

I have already tried using a web app, but Caja makes mincemeat of Firebase's code, which you have to have in order to use the Firebase object. I tried including the Firebase script as a library, and later copied it directly into script.gs. Nothing worked.

I believe my problem is largely stemming from the gs environment, that is, the server. However, cURL does not seem to work when placed in script.gs. Anyway, hopefully one of you has an answer. If you require further clarification, let me know.

回答1:

I use Firebase with Google Apps Script. You need to use Google's urlFetch. Google Documentation

CODE EXAMPLE: Get Data

var optList = {"method" : "get"};
var recordName = "theUserID";

var rsltList = UrlFetchApp.fetch("https://yourFireBaseDB_name.firebaseio.com/" +
recordName + "/.json", optList );

In the above example, a GET request is made to firebase. The database you want data from must be included in the URL. The URL must end with /.json. Your database structure is reflected in the URL. In the above example, the database has a different record for each user ID, and the user ID is put into the URL.

UrlFetchApp.fetch() method can have one or two parameters.

fetch(url, params)

fetch Documentation

You need to use the advanced parameters to designate what type of request is being sent: For example: PUT, GET. Note: You can't designate a PATCH request directly with UrlFetchApp.fetch, but you can put an override in the header to send a PATCH request.

It's a lot safer using a PATCH request.

If you decide to write data to Firebase using UrlFetchApp.fetch, it's good to know that using a PUT request can overwrite and wipe out all your data if the URL is not configured correctly.

In the above example, the returned data gets put into the variable: rsltList

The second parameter of the fetch method is an object. {"method" : "get"}

The object is a key:value pair.

回答2:

Ok, I figured out a rather hacky way to do this, but it does the job none the less. I set up a local script to read from the Firebase. Every time a value is added, the script sends an ajax call to a Google form, which has a spreadsheet full of responses. The ajax call simulates a form submission, and viola! I'm able to send information from a client via Firebase to a second client via ajax to a googlesheet where script.gs can access the information and act upon it. The best part of this solution IMHO is that I don't have to mess around with Google's paranoid code parsers/mutilators :)