OAuth2 token fails with the Auth

[thevinci]

I went through the whole Google OAuth2 process to get a refresh and access token. I used the SetAuthKey method and every token seems to fail. I keep getting an invalid token error.

[svanderw]

For what it's worth, my working app started getting authorization failures yesterday. I haven't had a chance to debug the failure yet though.

[gurdotan]

+1, same problem here.

[gurdotan]

Actually, I was using regular login credentials and it stopped working today after steadily running for almost a whole year

[svanderw]

We're not the only ones; another node spreadsheet editing package has run into the same failures, so I think something on Google's side has changed.
jpillora/node-edit-google-spreadsheet#72

[theoephraim]

uh oh.
Looks like we'll need to figure out the easiest way to continue and update the docs.

I really hate making users do something complex to access their docs. Some options:

• a command line tool that comes with this module to generate the token?
• a little 1 page website that lets you go through the oauth flow to get a token?

thoughts?

[jandujar]

same problem here.

[sammorrisdesign-zz]

Yeah just ran into the same issue...

[abiskop]

Can confirm this as well; suddenly does not work anymore with user/pass auth via setAuth("user", "pass", callback).

[ondrae]

Google finally turned off support for ClientLogin, which this library depended on.

[svanderw]

Good news: without code changes, I've used an oauth2 token with the library (via the setAuthToken authorization)
Steps;
Go to the oauth playground: https://developers.google.com/oauthplayground/
input a 'scope' of: https://spreadsheets.google.com/feeds/
Authorize the keys;
Choose a user to use when the google authentication comes up; it should be requesting 'offline access'
Exchange the authorization code for tokens
Use the returned json object renaming the keys as:
token_type -> type
access_token -> value
pass that object into the setAuthToken method.
This new object contains a refresh token, which can be used to re-request access tokens after the current token expires from what I understand.

You should be able to access your authenticated sheets now.

Next: do this all in code :)

[theoephraim]

I had found the oauth playground, but I didnt see how to request offline access.
Also if we can request offline access, how long does that token last for?

[svanderw]

looks like the token is good for 3600 sec or 1 hour.
For some reason, it automatically requested offline access when I was doing it; I don't remember doing anything special.

[yogiben]

Anyone have a good idea how to refresh the token automatically?

I found this, but don't know what the client id and client secret would be

https://www.npmjs.com/package/refresh-token

[theoephraim]

Digging around a little bit, it seems like this may be helpful: https://developers.google.com/identity/protocols/OAuth2ServiceAccount

Although in many situations, for someone just wanting to play around with google spreadsheets, it may be overly complex or not an option.

Im thinking the best way forward may be to set up a little website/service (similar to the oauthplayground) that lets users quickly and easily generate tokens to use. Just need to figure out how to request offline access and deal with expiring tokens. It would also mean people would need to be comfortable authorizing this service instead of owning the whole process, but if they are uncomfortable with that, they can set up a service account (see link above).

It's not a small project though, so not sure when I'll be able to get to it.

[bassarisse]

The "trick" explained by @svanderw didn't work for me =/

[svanderw]

I'm wondering if it would work out nicely to switch passing an auth token into the authentication method to passing an Oauth2Client object. Then calling a method on that client would handle the refreshing of the token as necessary (from what I'm reading). The biggest part is going to be setting up the Oauth2 support outside of this library.
If users want to use a canned token, they can just use a mock object to wrap their token and it should work, otherwise, using the google oauth2Client is probably the way to go (IMHO).

I'll play a little over the weekend, hopefully I'll get something workable / repeatable.

[svanderw]

Using a configured service account (someone else will need to figure out how to deal with non-service accounts)
In order to use the impersonateAccount, you need to have the security settings for your domain updated
This is not complete (missing some code here, but it should just be missing declarations)

var       googleapis = require('googleapis'),
        OAuth2 = googleapis.auth.OAuth2;

        oauth2Client = new OAuth2(config.oauth.clientId, config.oauth.clientSecret, 'postmessage');
        jwt = new googleapis.auth.JWT(
            config.oauth.serviceAcctEmail,
            config.oauth.serviceAcctKeyFile,
            null,
            ['https://spreadsheets.google.com/feeds/'],
            config.oauth.impersonateAccount
        );
        jwt.authorize(function (err, result) {
            if (err) {
                console.error(err);
                process.exit();
            }
            oauth2Client.setCredentials({
                access_token: result.access_token
            });
            oauth2Client.getAccessToken(function (err, tokens) {
                if (err) {
                    console.error(err);
                    process.exit();
                }
                my_sheet.setAuthToken({
                    "value": tokens,
                    "type": "Bearer"
                });
                // start using the sheet here
            });
        });

[pavelkuchin]

There is another way, probably it will be interested for someone (access without token expiration).
The script and instructions for generation <client id>, <client secret>, <access token>, <refresh token>: https://github.com/pavelkuchin/oauth2_tokens_generator

KEEP <client id>, <client secret>, <access token>, <refresh token> IN SECRET, because they can be user for access to your resources from your name without any expiration date. (you can revoke them through google console).

The Code:

var GoogleSpreadsheet = require("google-spreadsheet");
var google = require('googleapis');

var OAuth2Client = google.auth.OAuth2;

var oauth2Client = new OAuth2Client(<client id>, <client secret>);
oauth2Client.setCredentials({
  access_token: <access token>,
  refresh_token: <refresh token>
});

var done = _this.async();

oauth2Client.refreshAccessToken(function(err, tokens) {
  if (err && err.message) {
    grunt.log.error(err.message);
    done(false);
  } else {
    var auth_id = {
      type: tokens.token_type,
      value: tokens.access_token
    };

    var my_sheet = new GoogleSpreadsheet(<Document ID>, auth_id);

    my_sheet.getInfo( function( err, sheet_info ){
        if (err && err.message) {
          grunt.log.error(err.message);
          done(false);
        } else {
          console.log( sheet_info.title + ' is loaded' );
          done();
        }
    });
  }
});

[pavelkuchin]

Actually I'm agree with @theoephraim and @svanderw, OAuth2ServiceAccount is more reliable solution. if it works for me, I will remove oauth2_tokens_generator.

[whilefalse]

I was able to do this using the OAuth2ServiceAccount, but was a bit of a fiddle, for those who are stuck...

1. Create a Service Account as described here
2. Download the P12 key from the Developer Console
3. Convert the P12 to a pem by running:openssl pkcs12 -in key.p12 -nocerts -passin pass:notasecret -nodes -out key.pem
4. Copy the key.pem to your project directory
5. Install google-auth-library:
   npm install google-auth-library
6. Make sure to give the service account email address access to the spreadsheet you want to connect to.
7. Use this code to authenticate with Google and set the authToken:

var googleAuth = require("google-auth-library");
var GoogleSpreadsheet = require("google-spreadsheet");
var sheet = new GoogleSpreadsheet("<spreadsheet-key>");
var authClient = new googleAuth();
var jwtClient = new authClient.JWT("<client-email-shown-in-developers-console>", "./key.pem", null, ["https://spreadsheets.google.com/feeds"], null);
jwtClient.authorize(function (err, token) {
    sheet.setAuthToken({ "type": token.token_type, "value": token.access_token });
    // sheet is now authenticated, and you call get* methods
});

[theoephraim]

thats great! Thanks for getting a working example going :)

I'd like there to be as few steps as possible for end users.
Looks like the minimum would be creating the service account and just
passing in the p12 key?

Then this module should

• convert p12 key to pem
• create JWT token using pem

Seems very doable!

---

Theo Ephraim
http://theoephraim.com
514.998.8436

On Mon, Jun 1, 2015 at 11:45 AM, Steven Anderson [email protected]
wrote:

I was able to do this using the OAuth2ServiceAccount, but was a bit of a
fiddle, for those who are stuck...

1. Create a Service Account as described here
   https://developers.google.com/identity/protocols/OAuth2ServiceAccount
2. Download the P12 key from the Developer Console
3. Convert the P12 to a pem by running:openssl pkcs12 -in key.p12
   -nocerts -passin pass:notasecret -nodes -out key.pem
4. Copy the key.pem to your project directory
5. Install google-auth-library
   https://github.com/google/google-auth-library-nodejs: npm install
   google-auth-library
6. Use this code to authenticate with Google and set the authToken:

var googleAuth = require("google-auth-library");
var GoogleSpreadsheet = require("google-spreadsheet");
var sheet = new GoogleSpreadsheet("");
var authClient = new googleAuth();
var jwtClient = new authClient.JWT("", "./key.pem", null, ["https://spreadsheets.google.com/feeds"], null);
jwtClient.authorize(function (err, token) {
sheet.setAuthToken({ "type": token.token_type, "value": token.access_token });
// sheet is now authenticated, and you call get* methods
});

—
Reply to this email directly or view it on GitHub
#38 (comment)
.

[mlconnor]

following the steps from @whitefalse I'm able to actually get a token but when I make the call to doc info it fails with a 403. the issue is that once I had created a service account, i had to go to google drive and then give that account access to the spreadsheet i wanted to share.

[theoephraim]

Thanks for sharing. That's somewhat annoying, but also kind of makes sense.

[whilefalse]

@mlconnor Yeh I had to do the same thing - took me ages to figure that out! I've updated my instructions, cheers.

[ondrae]

Thanks for that code snippet @whilefalse it helped a ton.

My service is on Heroku, so instead of pem file I set the private key as an environment variable and passed it in as the third argument in the JWT call.

Backend.prototype.login = function(cb) {
  var sheet = this.sheet;
  var authClient = new googleAuth();
  var jwtClient = new authClient.JWT(config.get("googleServiceAccountEmail"), null, config.get("googleServiceAccountPrivateKey"), ["https://spreadsheets.google.com/feeds"], null);
  jwtClient.authorize(function (err, token) {
      sheet.setAuthToken({ "type": token.token_type, "value": token.access_token });
      // sheet is now authenticated
      cb();
  });
};

[mathurs]

Thanks @whilefalse for figuring this out!

[theoephraim]

I'll have this all wrapped up this week!

My solution involves just setting up the service account and passing in the JSON file that google gives you directly. No need to run any openssl commands.

[svanderw]

                                                                                  Were you thinking to keep the ability to pass in an existing token, because I'm currently using a token which has been authorized to impersonate anyone in the domain. Personally I would suggest passing in an oauth2client object, ‎and give instructions on how to set one up. It decouples the 2 responsibilities (editing of a spreadsheet and authentication). It would also allow multiple authentication techniques (web based auth, service account, etc.); and that same oauth2client could be used to access other Google services like drive or anything under the google apis...Scott V                                                                                                                                                                                                                                                                                                                                        Sent from my BlackBerry 10 smartphone on the Bell network.                                                                                                                                                                                                                From: Theo EphraimSent: Monday, June 8, 2015 4:50 PMTo: theoephraim/node-google-spreadsheetReply To: theoephraim/node-google-spreadsheetCc: svanderwSubject: Re: [node-google-spreadsheet] OAuth2 token fails with the Auth (#38)I'll have this all wrapped up this week!

My solution involves just setting up the service account and passing in the JSON file that google gives you directly. No need to run any openssl commands.

—Reply to this email directly or view it on GitHub.

[theoephraim]

Yes, I'd like to still support passing in existing tokens and like you mention, passing in something that will automatically renew the tokens would definitely be helpful.

But I still like the idea of providing some helper methods to get going with the least amount of work and without having to install any additional modules if you don't want to. Making authenticated requests is definitely a core part of this module in my mind, so it makes sense to me to require those auth related modules and provide the wrapper methods.

[Michal--M]

I have used the whilefalse's method that works great initially, but after some time (usually one hour according to token.expiry_date) calling addRow returns Invalid authorization key error and probably the only solution is to obtain a new token via jwtClient.authorize, am I right? It might be possible to play around with token.refresh_token, but I am getting value jwt-placeholder there.

[theoephraim]

You are correct! The JWT token generated is good for 1 hour. A quick fix would be set a 45 minute interval to renew and reset the token. I'll be releasing something this week and it will handle renewing the tokens.

[mderazon]

@whilefalse your method worked for me, thanks!

[ralyodio]

Any update on a release that fixes the auth problem?

I found this which helped me get @whitefalse solution to work: http://www.nczonline.net/blog/2014/03/04/accessing-google-spreadsheets-from-node-js/

[kaminskypavel]

@whilefalse were actualy "whiletrue" with this , good catch buddy, +1.
@theoephraim any plans to fix this?

[christiaanwesterbeek]

Using @whilefalse solution, I get an invalid_grant error in the jwtClient.authorize callback. This is my code:

var spreadsheet = new GoogleSpreadsheet(config.spreadsheetId);
var authClient  = new googleAuth();
var jwtClient   = new authClient.JWT(
  config.serviceAccount.EmailAddress,
  './key.pem',
  null,
  ['https://spreadsheets.google.com/feeds'],
  null
);

jwtClient.authorize(function (err, token) {
  if (err) {
    console.error('The invalid_grant error is here', err)
    return;
  }          
  spreadsheet.setAuthToken({
    type  : token.token_type,
    value : token.access_token
  });
});

I made sure that the email address configured in config.serviceAccount.EmailAddress has edit rights to the spreadsheet with id defined in config.spreadsheetId.

Ideas?

Update: Thanks to reading http://stackoverflow.com/questions/10025698/authentication-on-google-oauth2-keeps-returning-invalid-grant I tried generating a new P12 key, converted that to a pem file and replaced the one I had in my project directory and the problem is gone. It's working now.

[theoephraim]

I worked on some fixes and got a lot of it done, but there are some significant changes to make because the token generated from the jwt client doesnt last forever, so I need to add some code to regenerate it when it expires. I'll try to get to it this weekend!

[theoephraim]

Good news. Implemented something and it's all working well.
I'll have it out as soon as I finish cleaning up the readme.

Thanks for your patience everyone!

[theoephraim]

I just released v1.0.0 with support (and instructions) for using a service account.

It takes the file generated from google when you create a service account, uses it (via JWT) to generate a token that is good for 1 hour. The token is automatically regenerated when it expires.

Please try it out and let me know if you find any bugs!

Cheers!

[jw-mcgrath]

Trying this out with your built in method and getting an error.

/node_modules/google-spreadsheet/index.js:102
if (google_auth.expires > +new Date()) return step();
TypeError: Cannot read property 'expires' of undefined

[theoephraim]

@JosMcG Thanks for the heads up. I'll try to find the error and fix. Can you please send a full code example?

[jw-mcgrath]

(function(){
    "use strict";
    //===========
    //Express Setup
    //==============
    var express = require('express');
    var app = express();
    //==========
    //Google setup
    //===============
    var GoogleSpreadsheet = require("google-spreadsheet");
    //TODO share the final spreadsheet with the service account
    var account_creds require('./google-cred.json');
//    var twilio_client = require('twilio')('','');
//    var socket = require('socket.io')(app);
    //===============
    //Routes
    //==============
    app.listen(8000);
    var spreadsheet = new GoogleSpreadsheet('1ZKbUxpTEkM64EZ3ERxBBm01i0zJziPHnSjmbLl2MNoc');
    spreadsheet.useServiceAccountAuth(account_creds, function(err){
        console.log("connected");
    });
    spreadsheet.getRows( 1, function(err, row_data){
        if(err){
            console.log(err);
        }else{
            console.log( 'pulled in '+row_data.length + ' rows ');
        }

});
})();

[jw-mcgrath]

Forgot to put in an equals sign when I move my private key out of that copypasta. Should be:

(function(){
    "use strict";
    //===========
    //Express Setup
    //==============
    var express = require('express');
    var app = express();
    //==========
    //Google setup
    //===============
    var GoogleSpreadsheet = require("google-spreadsheet");
    //TODO share the final spreadsheet with the service account
    var account_creds = require('./google-cred.json');
//    var twilio_client = require('twilio')('','');
//    var socket = require('socket.io')(app);
    //===============
    //Routes
    //==============
    app.listen(8000);
    var spreadsheet = new GoogleSpreadsheet('1ZKbUxpTEkM64EZ3ERxBBm01i0zJziPHnSjmbLl2MNoc');
    spreadsheet.useServiceAccountAuth(account_creds, function(err){
        console.log("connected");
    });
    spreadsheet.getRows( 1, function(err, row_data){
        if(err){
            console.log(err);
        }else{
            console.log( 'pulled in '+row_data.length + ' rows ');
        }

});
})();

[theoephraim]

I will work on making sure the error messaging is better, but from what I can tell, you are trying to read from the rows before the auth is initialized.

If you put the getRows call inside the callback of useServiceAccountAuth then it will wait until the auth token has been generated to make the next call, which is what you want.

[wizonesolutions]

@theoephraim Is it currently possible to use an existing OAuth2 token? I can't see where to specify my Client ID and Secret. I tried using setAuthToken(), but I'm guessing that's intended for something else.

[wizonesolutions]

Nevermind, figured it out. I was able to set the auth token from an OAuth2 token I already had. Like this:

    newngs.setAuthToken({
      type: 'Bearer',
      value: user.services.google.accessToken,
      expires: user.services.google.expiresAt
    });

[jtarre]

Hi @theoephraim, I'm having difficulty starting up your basic example from the readme.

 var GoogleSpreadsheet = require("google-spreadsheet");

// spreadsheet key is the long id in the sheets URL
var my_sheet = new GoogleSpreadsheet('1nTXIS1dEZ2__J82F3L_9_YOaHvIyKLhwYVweKvhEzaU');

// With auth -- read + write
// see below for authentication instructions
var creds = require('./node-sheets-eb6c52d15956.json');
// OR, if you cannot save the file locally (like on heroku)

my_sheet.useServiceAccountAuth(creds, function(err){

    // getInfo returns info about the sheet and an array or "worksheet" objects
    my_sheet.getInfo( function( err, sheet_info ){
        console.log( sheet_info + ' is loaded' );
});
})

'console.log( sheet_info + ' is loaded' );' outputs "undefined is loaded."

I think my Google Spreadsheet ID key may be incorrect, although all I did was copy and paste from the url.

Thanks for any advice. Love this module and love Breather!

[christiaanwesterbeek]

Try to console.log the err too, so that you can see what the error is:

console.log( sheet_info + ' is loaded or not because of this error', err );

[krasserp]

Hi,

I am also having issues when trying to use oauth.
keep on getting

    if (!this.key && !this.keyFile) {
      callback(new Error('No key or keyFile set.'));
      return;
    }

When I load the google generated json I think the issue is also that the keyNames are different in the generated file and in the examples?

google.json

{"installed":{
    "client_id":"8XXXXXXXXXXXXXXXXXXXXXXXXcontent.com",
    "auth_uri":"https://accounts.google.com/o/oauth2/auth",
    "token_uri":"https://accounts.google.com/o/oauth2/token",
    "auth_provider_x509_cert_url":"https://www.googleapis.com/oauth2/v1/certs",
    "client_secret":"kTXXXXXXXXXXXXXXXXXXXXXXXX",
    "redirect_uris":["urn:ietf:wg:oauth:2.0:oob","http://localhost"]
    }}

in your example:

var creds = require('./google-generated-creds.json');
// OR, if you cannot save the file locally (like on heroku)
var creds = {
  client_email: '[email protected]',
  private_key: 'your long private key stuff here'
}

So I tried both versions loading the creds via require and then via setting them in the js file itself but keep on running into No key or keyFile set.'

Any help appreciated.

Thanks,P

[theoephraim]

looks like maybe they changed the format of the file they generate?
I'll take a look and update the code (and instructions).

In the meantime, if you just pass in an object with the email and key as described in the example, I think it should work

[kevin1193]

Hi,

I've run some problems when using this package. I've done exactly what was in the sample then replace the key and service email but I keep getting this error.
if (google_auth.expires > +new Date()) return step();
TypeError: Cannot read property 'expires' of undefined

Hope to hear from you.

Best Regards,
Kevin Abestilla