Python

Question

I'm currently making a Discord-bot that connects to a Google-spreadsheet (gspread). But after I've been running it for a while it starts to hand out errors and it can't connect to my gspread anymore (unless i restart it).

The error that I'm receiving: (https://hastebin.com/odutucawuv.tex)

Ignoring exception in command sub
Traceback (most recent call last):
File "C:\Python36\lib\site-packages\discord.py-0.16.10-
py3.6.egg\discord\ext\commands\core.py", line 50, in wrapped
ret = yield from coro(*args, **kwargs)
File "C:\Users\simvid-5\Desktop\Pogomoves\DiscordBot.py", line 65, in sub
val = worksheet.cell(cell_name.row, cell_name.col+4)
File "C:\Python36\lib\site-packages\gspread-0.6.2-
py3.6.egg\gspread\models.py", line 392, in cell
self._cell_addr(row, col))
File "C:\Python36\lib\site-packages\gspread-0.6.2-
py3.6.egg\gspread\client.py", line 210, in get_cells_cell_id_feed
r = self.session.get(url)
File "C:\Python36\lib\site-packages\gspread-0.6.2-
py3.6.egg\gspread\httpsession.py", line 73, in get
return self.request('GET', url, params=params, **kwargs)
File "C:\Python36\lib\site-packages\gspread-0.6.2-
py3.6.egg\gspread\httpsession.py", line 69, in request
response.status_code, response.content))
gspread.exceptions.RequestError: (401, '401: 
b\'<HTML>\n<HEAD>\n<TITLE>Unauthorized</TITLE>\n</HEAD>\n<BODY 
BGCOLOR="#FFFFFF" TEXT="#000000">\n<H1>Unauthorized</H1>\n<H2>Error 
401</H2>\n</BODY>\n</HTML>\n\'')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "C:\Python36\lib\site-packages\discord.py-0.16.10-
py3.6.egg\discord\ext\commands\bot.py", line 846, in process_commands
yield from command.invoke(ctx)
File "C:\Python36\lib\site-packages\discord.py-0.16.10-
py3.6.egg\discord\ext\commands\core.py", line 374, in invoke
yield from injected(*ctx.args, **ctx.kwargs)
File "C:\Python36\lib\site-packages\discord.py-0.16.10-
py3.6.egg\discord\ext\commands\core.py", line 54, in wrapped
raise CommandInvokeError(e) from e
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: 
RequestError: (401, '401: 
b\'<HTML>\n<HEAD>\n<TITLE>Unauthorized</TITLE>\n</HEAD>\n<BODY 
BGCOLOR="#FFFFFF" TEXT="#000000">\n<H1>Unauthorized</H1>\n<H2>Error 
401</H2>\n</BODY>\n</HTML>\n\'')
Ignoring exception in command add
Traceback (most recent call last):
File "C:\Users\simvid-5\Desktop\Pogomoves\DiscordBot.py", line 130, in add
cell_name = worksheet.find(str(member))
File "C:\Python36\lib\site-packages\gspread-0.6.2-
py3.6.egg\gspread\models.py", line 711, in find
return self._finder(finditem, query)
File "C:\Python36\lib\site-packages\gspread-0.6.2-
py3.6.egg\gspread\models.py", line 696, in _finder
cells = self._fetch_cells()
File "C:\Python36\lib\site-packages\gspread-0.6.2-
py3.6.egg\gspread\models.py", line 331, in _fetch_cells
feed = self.client.get_cells_feed(self)
File "C:\Python36\lib\site-packages\gspread-0.6.2-
py3.6.egg\gspread\client.py", line 176, in get_cells_feed
r = self.session.get(url)
File "C:\Python36\lib\site-packages\gspread-0.6.2-
py3.6.egg\gspread\httpsession.py", line 73, in get
return self.request('GET', url, params=params, **kwargs)
File "C:\Python36\lib\site-packages\gspread-0.6.2-
py3.6.egg\gspread\httpsession.py", line 69, in request
response.status_code, response.content))
gspread.exceptions.RequestError: (401, '401: 
b\'<HTML>\n<HEAD>\n<TITLE>Unauthorized</TITLE>\n</HEAD>\n<BODY 
BGCOLOR="#FFFFFF" TEXT="#000000">\n<H1>Unauthorized</H1>\n<H2>Error 
401</H2>\n</BODY>\n</HTML>\n\'')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
File "C:\Python36\lib\site-packages\discord.py-0.16.10-
py3.6.egg\discord\ext\commands\core.py", line 50, in wrapped
ret = yield from coro(*args, **kwargs)
File "C:\Users\simvid-5\Desktop\Pogomoves\DiscordBot.py", line 136, in add
cell_list = worksheet.range('A2:A100')
File "C:\Python36\lib\site-packages\gspread-0.6.2-
py3.6.egg\gspread\models.py", line 72, in wrapper
return method(self, *args, **kwargs)
File "C:\Python36\lib\site-packages\gspread-0.6.2-
py3.6.egg\gspread\models.py", line 412, in range
params={'range': name, 'return-empty': 'true'}
File "C:\Python36\lib\site-packages\gspread-0.6.2-
py3.6.egg\gspread\client.py", line 176, in get_cells_feed
r = self.session.get(url)
File "C:\Python36\lib\site-packages\gspread-0.6.2-
py3.6.egg\gspread\httpsession.py", line 73, in get
return self.request('GET', url, params=params, **kwargs)
File "C:\Python36\lib\site-packages\gspread-0.6.2-
py3.6.egg\gspread\httpsession.py", line 69, in request
response.status_code, response.content))
gspread.exceptions.RequestError: (401, '401: 
b\'<HTML>\n<HEAD>\n<TITLE>Unauthorized</TITLE>\n</HEAD>\n<BODY 
BGCOLOR="#FFFFFF" TEXT="#000000">\n<H1>Unauthorized</H1>\n<H2>Error 
401</H2>\n</BODY>\n</HTML>\n\'')

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "C:\Python36\lib\site-packages\discord.py-0.16.10-
py3.6.egg\discord\ext\commands\bot.py", line 846, in process_commands
yield from command.invoke(ctx)
File "C:\Python36\lib\site-packages\discord.py-0.16.10-
py3.6.egg\discord\ext\commands\core.py", line 374, in invoke
yield from injected(*ctx.args, **ctx.kwargs)
File "C:\Python36\lib\site-packages\discord.py-0.16.10-
py3.6.egg\discord\ext\commands\core.py", line 54, in wrapped
raise CommandInvokeError(e) from e
discord.ext.commands.errors.CommandInvokeError: Command raised an exception: 
RequestError: (401, '401: 
b\'<HTML>\n<HEAD>\n<TITLE>Unauthorized</TITLE>\n</HEAD>\n<BODY 
BGCOLOR="#FFFFFF" TEXT="#000000">\n<H1>Unauthorized</H1>\n<H2>Error 
401</H2>\n</BODY>\n</HTML>\n\'')

And one of the functions that I'm using if it's to any help to understand the problem above, I can provide more of my code if it's necessary to fix it:

import discord
import asyncio
import random
import pickle
import os
import gspread
import time

from oauth2client.service_account import ServiceAccountCredentials
from discord.ext import commands

prefix = '!'

def returnPrefix():
    global prefix
    return prefix

bot = commands.Bot(returnPrefix())

scope = ['https://spreadsheets.google.com/feeds']
credentials = 
ServiceAccountCredentials.from_json_keyfile_name('GoogleSpreadsheetCreds.json', scope)
gc = gspread.authorize(credentials)
sh = gc.open("MyWorksheet")
worksheet = sh.sheet1


@bot.event
async def on_ready():
        print('Logged in as')
        print(bot.user.name)
        print(bot.user.id)
        print('-----')
        await bot.change_presence(game=discord.Game(name='Stackoverflow'))



@bot.command(pass_context=True)
@commands.has_role("Premium")
async def sub(ctx, member: discord.Member = None):
    global cell_name
    if member is None:
        member = ctx.message.author

    #Delete user_command.
    await bot.delete_message(ctx.message)

    #Retrieve user from commander.
    Username = '{0}'.format(member)

    try:
        #Try to find the username in spreadsheet.
        cell_name = worksheet.find(Username)
    except: #If we dont find the username.
        await bot.say("Your name ("+Username+") was not found, please contact someone for help.")

    if cell_name.value == Username: #If we find the username.
        #Retrieve some values.
        val = worksheet.cell(cell_name.row, cell_name.col+4)
        val_date = worksheet.cell(cell_name.row, cell_name.col+3)
        remaining_days = val.value
        remaining_date = val_date.value

        #Send a message to a member.
        await bot.send_message(member,
                            "```Username: "+ Username+
                            "
Remaining days: "+remaining_days+
                            "
Date for expiration: "+remaining_date+"```")

Jeff · Accepted Answer

Your access token expires after some period of time. From the OAuth 2.0 docs:

Refresh the access token, if necessary.

Access tokens have limited lifetimes. If your application needs access to a Google API beyond the lifetime of a single access token, it can obtain a refresh token. A refresh token allows your application to obtain new access tokens.

Note: Save refresh tokens in secure long-term storage and continue to use them as long as they remain valid. Limits apply to the number of refresh tokens that are issued per client-user combination, and per user across all clients, and these limits are different. If your application requests enough refresh tokens to go over one of the limits, older refresh tokens stop working.

I don't believe that gspread is equipped to deal with refresh tokens. You may just be able to catch this exception and reauthenticate as needed.

EDIT:

After looking at the gspread source I think you may be able to refresh your token by simply calling gc.login()

EDIT:

This issue was filed with gspread a couple of years ago but it was closed for no apparent reason. Here is one approach to solving it:

def add_row(row):
    try:
        gs_client = gspread.authorize(creds)
        gs_worksheet = gs_client.open("foo").sheet1
        if creds.access_token_expired:
            gs_client.login()  # refreshes the token
        gs_worksheet.append_row(row)
    except Exception, e:
        traceback.print_exc()

but I think that's overkill. You should be able to just call gc.login() right before your try: block:

gc.login()
try:
    #Try to find the username in spreadsheet.
    cell_name = worksheet.find(Username)
except: #If we dont find the username.

Python - Gspread Request Error 401

Tags:

python-3.x

google-sheets

discord.py

gspread

Simvid

1 Answers

Jeff

Recent Activity

Donate For Us