firebase authentication from google app script ? (secret is deprecated)

[Joe P]

Hi,

is there anyway to authenticate to Firebase with a Service Account so that the .gs file can be run from a Google Sheet ?

we can get a token with eg

var token = FirebaseApp.getDatabaseByUrl(firebaseURL).createAuthToken()

but that token can't then be used to authenticate via

FirebaseApp.getDatabaseByUrl(firebaseURL, token)

it seems we can only use our Firebase Database secret but those have been deprecated.

FirebaseApp.getDatabaseByUrl(firebaseURL, secret)

How long is it safe to use the secret for? presumably deprecated features will be removed at some point?

thanks

J.

reference: https://sites.google.com/site/scriptsexamples/new-connectors-to-google-services/firebase

[Jacob Wenger]

Hey Joe,

You are probably looking for the Firebase Admin Node.js SDK, which allows you to authenticate with a service account and get admin access to your Realtime Database. Check out that link and it'll walk you through how to authenticate the Admin SDK.

Cheers,

Jacob

--
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-talk+unsubscribe@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/2ac642d6-4c9c-405e-8ec9-fe0ff7b22a98%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Joe P]

Hi Jacob,

thanks, I've used that node.js sdk for some Google App Engine work, but I'm specifically referring to connecting a Google Sheets spreadsheet to Firebase to read data into the sheet via .gs script (Tools -> Script Editor) without use of a server

so far the only way I can see to connect with that FirebaseApp library is by using the deprecated secret.

here's an example excerpt...

regards

J.

function onOpen() {

  var ui = SpreadsheetApp.getUi();

  ui.createMenu('gAdapt Firebase')

    .addItem('Update Data', 'updateData')

    .addToUi();

}

function updateData() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet();

  var lastRow = sheet.getLastRow();

  var firebaseUrl = "https://********.firebaseio.com/";

  var secret = '********'; // deprecated firebase secret

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

  var data = base.getData('users');

  var keys = Object.keys(data);

  var sheetRow = [];

  var entryKeys;

  

  for (index in keys) {

    sheetRow = [];

    entryKeys = Object.keys(data[keys[index]])

    for (i in entryKeys) {

      sheetRow.push(data[keys[index]][entryKeys[i]]);

    }

    sheet.appendRow(sheetRow);                            

  }

On Thursday, 8 December 2016 18:31:32 UTC, Jacob Wenger wrote:

Hey Joe,

You are probably looking for the Firebase Admin Node.js SDK, which allows you to authenticate with a service account and get admin access to your Realtime Database. Check out that link and it'll walk you through how to authenticate the Admin SDK.

Cheers,

Jacob

On Wed, Dec 7, 2016 at 6:19 PM, Joe P <jmp...@gmail.com> wrote:

Hi,

is there anyway to authenticate to Firebase with a Service Account so that the .gs file can be run from a Google Sheet ?

we can get a token with eg

var token = FirebaseApp.getDatabaseByUrl(firebaseURL).createAuthToken()

but that token can't then be used to authenticate via

FirebaseApp.getDatabaseByUrl(firebaseURL, token)

it seems we can only use our Firebase Database secret but those have been deprecated.

FirebaseApp.getDatabaseByUrl(firebaseURL, secret)

How long is it safe to use the secret for? presumably deprecated features will be removed at some point?

thanks

J.

reference: https://sites.google.com/site/scriptsexamples/new-connectors-to-google-services/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.

[Jacob Wenger]

Hey Joe,

I've never created a Google Sheets spreadsheet, so we will probably have to work together to figure out how to make this happen. I assume you cannot use the Admin Node.js SDK because Sheets is not running in a Node.js environment and you only can add <script> includes for JavaScript files. This means you can only use the regular Firebase client. Is that assumption correct?

I think you have two options (outside of using the deprecated secret):

• If you are just doing a simple read, use the Firebase Realtime Database REST API instead of the JS SDK. You just need to generate a Google OAuth2 access token to authenticate to the REST API (as documented here). Google Sheets may even have an easy way to get a Google OAuth2 access token.
  
• If you really want to use the JavaScript SDK, you can create a custom token on a custom server using the Admin SDK and then use signInWithCustomToken() to sign in the client running in Google Sheets.

Let me know if either of those options work for you. It may take a couple messages back and forth between us to figure out exactly how to do this properly.

Cheers,
Jacob

To unsubscribe from this group and stop receiving emails from it, send an email to firebase-talk+unsubscribe@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/96a0ad42-2850-4970-a6c8-56b11856bd81%40googlegroups.com.

[tourb...@gmail.com]

I'm been trying to solve the same problem.  like you gone through the same steps with the firebase  service account to create a token using the private key from the dev console. 

I've all taken at look at Bruce Mcpher site :

        http://ramblings.mcpher.com/Home/excelquirks/goa/walkthroughs

He has another Apps Scripts library cGoa that does authentication to a number of web services including firebase.

However from what I can tell he think he still uses the secret.

        

Bruce is the author of the Apps Script Books,  "Google Apps for Beginners"  and  "Going GAS"

   His google Plus:   https://plus.google.com/u/0/112066118406610145134?prsrc=4

Let me know if you find the answer.

[tourb...@gmail.com]

Jacob , pretty sure you're missing a few important points.

          1. Its more about Apps Script authenticating to Firebase database

           2. the example you gave uses the firerbase Java SDK to generate the Oath2 token. 

                Google Apps Script has no such standard firebase SDK.

I've been trying to use the cGoa Library for  author/developer Bruce Mcpher to create a Oath token without success.

      http://ramblings.mcpher.com/Home/excelquirks/goa

I keeping getting responses like "Unauthorized Access", "Permission denied" and "Could not parse auth token" 

Could you tell us which one of these responses indicated I'm on the right track?

[Joe P]

Here's a version using Service Account rather than secret. 

You're going to have to build your own URLFetch though

1) get your service account json private key from firebase

2) paste the content of the json file into the Google App Script properties (File -> Project Properties -> Script Properties). I've called my Property "service_account"

3) install the GSApp library MJ5317VIFJyKpi9HCkXOfS0MLm9v2IJHf https://github.com/Spencer-Easton/Apps-Script-GSApp-Library

(note unlike the FirebaseApp / cFirebase libraries we are using ?access_token=... not ?auth=...  to use our service account token)

working example...

function onOpen() {

  var ui = SpreadsheetApp.getUi();

  ui.createMenu('gAdapt Firebase')

      .addItem('Update Data', 'updateData')

      .addToUi();

}

function updateData() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet();

  var lastRow = sheet.getLastRow();

  var firebaseUrl = "https://**********.firebaseio.com/";

  var jsonKey = JSON.parse(PropertiesService.getScriptProperties().getProperty("service_account"));

  var key = jsonKey.private_key;

  var clientEmail = jsonKey.client_email;  

  // https://github.com/Spencer-Easton/Apps-Script-GSApp-Library/blob/master/example.gs

  var serverToken = new GSApp.init(key, ['https://www.googleapis.com/auth/firebase.database', 'https://www.googleapis.com/auth/userinfo.email'], clientEmail);

  

  serverToken.addUser(clientEmail).requestToken();

  var token = serverToken.getToken(clientEmail).token;

  

  var dataPath = 'users';

  var options = {};

  var result = UrlFetchApp.fetch( firebaseUrl  + dataPath + '.json' + '?access_token=' + token, options).getContentText();

  var data = Utilities.jsonParse(result);

  var keys = Object.keys(data);

  

  var sheetRow = [];

  var entryKeys;

  

  for (index in keys) {

    sheetRow = [];

    entryKeys = Object.keys(data[keys[index]])

    for (i in entryKeys) {

      sheetRow.push(data[keys[index]][entryKeys[i]]);

      Logger.log(entryKeys[i]);

    }

    sheet.appendRow(sheetRow);                            

  }

  

}

hopefully eventually this well get added to a helper library for doing Firebase queries.

regards

J

[tourb...@gmail.com]

Thanks, Joe. I'm about to try this now. 

On the firebase side are you doing anything special with the firebase rules?

Should the clientEmail  already be a current user in firebase.

I ask that because my web App is just reading from a database node without any login.

The App Script is the only thing that authenticates to Firebase to read and write data.

Of course some of that data come from a spreadsheet but most from the web.

( I know this is not important.) 

This is the web app, just so you understand: 

https://trianglenews-50797.firebaseapp.com/

[Joe P]

Have a look here

https://firebase.google.com/docs/admin/setup

generally I think the serverside elements (in this case .gs) run under the Admin SDK Service Account. You could potentially use the token generator to create tokens for your client-side users (eg if you were running a browser app etc). In this case though we don't have a client-side as such.

however it may actually be possible to use the uid for the Service Account, I'm just not sure what it is exactly or how you'd set it  (i.e I don't know what our equivalent of setDatabaseAuthVariableOverride(auth) would be

see here

http://stackoverflow.com/questions/38249664/firebase-custom-token-vs-service-account

http://stackoverflow.com/questions/37425903/only-allow-writes-from-service-account-in-firebase

maybe you can use auth.isAdmin

https://firebase.google.com/docs/reference/security/database/

let me know if you find out any more details

thanks

j

[tourb...@gmail.com]

Joe 

I really have you thank for leading to the Spencer 's library Apps-Script-GSApp-Library.

And for the example script. I had come across he github account to never really tried it.

so I adapted the example with some Ideas from another one of the developers from the FirebaseApp script, Romain Vialard.

He has and example where he adds a user to a firebase database.

As it's stands, I've been able to read and write to firebase.

My main goal was writing the the Firebase Service account/ Private key. This is what I have so far:

-----------begin--code-----

var firebaseUrl = "https://trianglefeeds.firebaseio.com";

  var jsonKey = JSON.parse(PropertiesService.getScriptProperties().getProperty("service_account"));

  var key = jsonKey.private_key;

  var clientEmail = jsonKey.client_email;  

  // https://github.com/Spencer-Easton/Apps-Script-GSApp-Library/blob/master/example.gs

  var serverToken = new GSApp.init(key, ['https://www.googleapis.com/auth/firebase.database', 'https://www.googleapis.com/auth/userinfo.email'], clientEmail);

  

  serverToken.addUser(clientEmail).requestToken();

  var token = serverToken.getToken(clientEmail).token;

  

  var dataPath = '/feed/';

  var dataWrite = { 

    0: {

        feedName:"islandStats", 

           feedUrl:"http://www.islandstats.com/islandstats_rss.asp",

      postDate: new Date()

    }

  };

  

  var options = {

    method: "PUT",

    payload: JSON.stringify(dataWrite)

  }

  

  UrlFetchApp.fetch( firebaseUrl  + dataPath + '.json' + '?access_token=' + token, options).getContentText();

    

    //Firebase database Rules

   //    ".read": "auth != null && auth.isAdmin == true",
   //     ".write": "auth != null && auth.isAdmin == true"       

-------end---code------------

[Jacob Wenger]

Hey there,

I can't tell if both of you are unstuck or if there are still outstanding issues. I just now realized that Joe posted a link to a Google Apps Script library for accessing Firebase. That gives me a lot more context. I actually have no idea who made that API (it is not an official Firebase API) which is why I was so unfamiliar with the code in Joe's original question. But looking at the API reference for that library, I noticed the createAuthToken() method at the very bottom. It looks like it takes info from your service account to create a valid token on your behalf:

​
Have you tried just using that?​

If either of you are still having issues, let me know and I'll try to set you straight.

Cheers,
Jacob

--
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-talk+unsubscribe@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/99d681d7-837c-4c9f-a387-0fd6c743a5b1%40googlegroups.com.

[tourb...@gmail.com]

Hi Jacob

That particular function describes how the token then needs to used on the client side with singnInWithCustomToken  from the regular javascript API.

We are access firebase from within Apps scripts directly.

The current Apps Scripts external library we  are looking at is Apps-Script-GSApp.

https://github.com/Spencer-Easton/Apps-Script-GSApp-Library

It would be great if Firebase/Google added standard  support for Apps Script directly.

Have a look at the above Library and let me know what you think. In particular I'm 

concerned about security problems.

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.

[Jozef Pierlejewski]

Hi Jacob,

as @tourbermuda mentions, that createAuthToken function creates a custom token to be used with signInWithCustomToken on client-side https://firebase.google.com/docs/reference/js/firebase.auth.Auth#signInWithCustomToken

it does not work for passing to ?auth_token={token} which the FirebaseApp library uses internally to make REST queries. 

you can see in the source code for _buildRequest it calls this: 

url += "?auth=" + base.secret;

therefore trying to use the token created with createAuthToken will return this error

Error: Expected an ID token, but was given a custom token.

that result from createAuthToken also can't be used to pass as ?access_token={token} with a UrlFetchApp.fetch, since it is the wrong type of token.

this is why we've had to use GSApp library to create an access_token from our service account json file to pass to use with a REST call:

UrlFetchApp.fetch( firebaseUrl  + dataPath + '.json' + '?access_token=' + access_token, options).getContentText();

hope that clarifies the issue

regards

Joe

--
You received this message because you are subscribed to a topic in the Google Groups "Firebase Google Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/firebase-talk/-RKpHaMPTYQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to firebase-talk+unsubscribe@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/760fbcb7-437f-478c-aed8-740396afc369%40googlegroups.com.

[Nick Lalande]

Update:

I was happy that I found the FirebaseApp GAS library, until I ran into the same authentication issue.  Then I found this group, and I was thinking: "not another library...?".  I'm sure the original poster moved on, if not, here is what I found.

1. This post on Stackoverflow (DID NOT WORK)
• It's very detailed with images, and shows where Firebase hid the "Database Secrets".
  
• Click the gear icon to the right of "Overview", and select "Project settings"
• Select the "SERVICE ACCOUNTS" tab, and then click on "Database Secrets".
• "Database Secrets" included the notice of deprecation, and below a link:
  
• Create custom database authentication tokens using a legacy Firebase token generator. At least one secret must exist at all times. Learn more
• That link doesn't do anything quickly!
• Above all that, see Manage all service accounts
• Tip: add "&project=yourfirebaseProjectID"
2. WHAT DID WORK:  Manage all service accounts 
• The links brings you to your IAM & Admin section for your Firebase project.
• In "Service accounts", you should see Service account name "firebase-adminsdk", with no key.
• Click on the triple dot action menu at the right-hand side of the service account, and select "Create key".
• This prompted me to download and save the key.
• Afterward, simply copy & paste the key into the following code:
• function makeToken(){ 

    var firebaseUrl = "https://example.firebaseio.com/"; 

    var secret = "EXAMPLEKEYYEKELPMAXE"; 

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

•   var token = base.createAuthToken(Session.getActiveUser().getEmail()); 

    return token; 

  }
  
  // ALSO WORKED USING THE SAMPLE PROVIDED
  

  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://example.firebaseio.com/";

    var base = FirebaseApp.getDatabaseByUrl(firebaseUrl, "EXAMPLEKEYYEKELPMAXE");

    base.setData("", dataToImport);
• My "Realtime Database" RULES (from the library author's tutorial):
• {

    "rules": {

      "users": {

        "$user_id": {

          ".write": "$user_id === auth.uid",

          ".read": "$user_id === auth.uid"

        }

      }

    }

  }
• Note:  I'm not sure if this will make a difference - I had previously setup Authentication using Google, I already had Web SDK configuration using my own "Web client ID" and "Web client secret".
• I "auto created" the credentials in a previous tutorial, it is an OAuth 2.0 client ID, for web application, using my Firebase domain (https://example.firebaseapp.com), as an Authorized JavaScript Origin, and https://example.firebaseapp.com/__/auth/handler as Authorized redirect URI.

I hope this helps!

- Nick