I'm wondering if anyone has used the gspread python library(https://github.com/burnash/gspread).

I'm using it for the Clan League CLOT and have started to run into this error. The trace is:

2017-04-26 21:16:26 - utilities.clan_league_logging 
- ERROR 
- Failed with - 401: Token invalid - Invalid token: Crypto credential expired.

This started occurring after 3 months of smooth operation. It also does not have a consistent repro. I have my CLOT access a google doc on a schedule. The first time I kick it off, I am able to get valid credentials to access the doc. The CLOT tries accessing the doc once in every 3 hours. After the second or third attempt, this issue pops up.

If I reboot my process, it is immediately successful. I'm wondering if this is something to do with the connection being persistent for too long. I've tried most of the stuff suggested online with no success.

If anyone has run into this or has experience, any help would be much appreciated.

Edited 4/27/2017 17:43:23

I haven't used it but it does sound like the credentials are expiring after some hours of non-use.

Googling finds this thread: https://github.com/burnash/gspread/issues/405 where they get the same error and said they were able to fix it by issuing the same request over and over while idle. That's probably not a good solution though.

You say it always works the first time, I guess it's getting credentials and saving them for future requests? Perhaps there's a way to make it not save the credentials and make a "first time" request every time. You can try sniffing its traffic to see what it's sending differently between the first and later requests.

Check your client's (the one from gspread.authorize()) auth.access_token_expired property. If it's False, just call your client's login() method and it'll refresh the access token. It expires either an hour or two after you create a client or login.

I'm not 100% sure from your stack trace whether this is the issue, but if it is one way to resolve this would be to wrap something around your client and periodically renew the client. Something like this (or some more efficient version you can come up with that doesn't check as frequently):

@property
def client(self):
    if (self._client.auth.access_token_expired): self._client.login()
    return self._client

(self._client here stores the actual gspread Client)

---

If that's not it, are you able to get a more detailed stack trace or the exact exception raised by gspread? Those would likely be real helpful here since I can't find "Crypto credential expired" (or any Github pseudo-regex version of that I can easily think of) in the gspread source code for the latest version, so either the string is generated in some way (or in some place) that the GitHub search doesn't find easily or you're using an older version since which things have changed. It would probably also help if you told us the gspread version you're working with.

Oh and if you haven't already, posting to StackOverflow as well might be helpful. (Just reminding you, just in case).

Edited 4/27/2017 19:00:24

not having touched that, this sounds like an authenticated connection expiring, try re-establishing the connection every time, or every so often, or whenever you get that error, instead of using the same open connection.

Edited 4/28/2017 12:48:19

@Fizzer/ps,
I've tried those things and had no success. I'll try sniffing the network traffic to look for more leads.

@knyte,
The login() method kinda does what you're suggesting as a wrapper. I have tried calling the login method every time(to simulate the first run which works). Correct me if I'm wrong,

    def login(self):
        """Authorize client."""
        if not self.auth.access_token or \
                (hasattr(self.auth, 'access_token_expired') and self.auth.access_token_expired):
            import httplib2

            http = httplib2.Http()
            self.auth.refresh(http)

        self.session.add_header('Authorization', "Bearer " + self.auth.access_token)

I'll try to get the exact exception. Since it is hard to get a repro(I'm guessing it needs a few hours for it to expire), I'm still trying to figure out where it is failing.

I'm using gspread 0.4.1. Sorry should have mentioned that. I'll go to SO after some more investigating eventually.

Edited 4/28/2017 15:25:25

I don't know if you've seen it, but the other Google result for the exact string "Crypto credential expired" is this old post: http://wtfruby.com/python/2016/11/01/gspread-token-refresh.html (based on the publication date and the release timeline for gspread, the author was likely also using v0.4.1).

Are you sure you're calling login() not just every time you retrieve a sheet but also when you update/edit/make any API call to Google Sheets? If so, I haven't been able to find where the specific error message ("Crypto credential expired") is coming from but it might be coming from the Google end of things. Apparently there have been issues with some Google APIs in the past with telling you that the token is invalid or expired as a sort of catch-all exception that doesn't necessarily mean the token is actually invalid/expired (this old StackOverflow post: http://stackoverflow.com/questions/17813621/oauth2-0-token-strange-behaviour-invalid-credentials-401)- on some Google APIs, some small changes to your session data can trigger an expiry/invalid token error.

So there could be some arbitrary issue going on in the background (some hard-to-predict mess with the session parameters?) that only occurs some of the time, in which case, if you're running something like a cron job, you can perhaps set up a cron retry for when you hit a 401 error.

If you haven't already, you could also check the Google Developer Console to see if this was an error triggered by Google and if so, what about the request caused it. Might find something weird in your request header, perhaps. Given that gspread seems stable enough for the most part + I can't find any documentation anywhere of anything other than an expired token triggering this issue on the gspread end, my guess would be something funky going on with the session parameters.

Also might not hurt to manually check with Google's token verifier yourself: https://www.googleapis.com/oauth2/v1/tokeninfo. Could be a faster way to seeing how to reproduce the error.

Edited 4/28/2017 18:30:54

So it seems like gspread internally only calls client.login() when the authorize() function is called, but it still has to make API requests using the client and its auth token when you're doing things with worksheets where you don't directly interface with the client. When you call, say, the get_row() method, you still use the client but gspread doesn't internally call the login() method. It seems like this would be a pain point- people would be sure to call login() before any time they use the client themselves, but they might not track down everywhere that the client is used and therefore not totally avert the risk of an expired token.

I might have missed something in my skim of the source code, but I think when I ran into a similar issue a few months ago it was because of that. So if I didn't miss something in the gspread source that calls login() every time you use your client, if you're not calling your client's login() before every Google API request that requires your auth token, you might want to add that in. Maybe as a method that just calls client login() inserted at the beginning of every function, or the slightly less ugly-looking route of doing the same thing in a decorator.

Edited 4/28/2017 22:08:24

I think you're right. The actual issue is something else and is being masked as an invalid creds error. I looked at all of your suggestions and none of them seemed to work for me. My google developer console has no logs either which is quite frustrating as this error is clearly being thrown by Google in my opinion(as it doesn't exist anywhere in the source code).

I actually solved the problem by sidestepping it. The CLOT was running on an infinite loop with a sleep time of 3 hours. Since the problem goes away every time I restart the program, I used the Windows Scheduler instead of a loop. So every 3 hours it spawns a new process(which simulated what I was doing manually for the past week to keep CL alive).

I think I'm going to move away from google docs as my db next season. I wasn't pleased with it for other reasons, and this just convinced me a bit more :p

Thanks for your suggestions and spending time to look into the issue. Much appreciated.

Yeah, I think logging has been broken for the Google Spreadsheets API for some time. I'm never able to get any accurate readings on how much of my quota I've exhausted. I ran a program overnight that made ~100k requests to their API and they never showed up on the console even though the requests (as far as I could tell) went through. I'm not able to find any documentation on what might be wrong there, either, but I'm hoping it's just some missed setup step on my end. Or maybe it's a result of gspread not using Google's own libraries to interact with the API, but it seems like Google's quota tracking would be robust to that.

Glad to hear you solved the problem, though! Still curious as to what could've caused this 3 hours into a process.

I think I'm going to move away from google docs as my db next season.

If you decide to stick with it for some reason, there's always butterdb: https://github.com/terrible-ideas/butterdb (formerly fuckitDB). :P

Np and wish I'd been more helpful.

Edited 5/5/2017 21:47:28