EzDevInfo.com

python gspread google spreadsheet keeping connection alive

I'm updating my spreadsheets using gspread, the process takes about an hour, i have about 200 spreadsheets. It seems about 30 minutes into the updating the sheets, the connection drops. Is there a way to keep the login alive? I thought I was keeping the connection alive because I'm opening and writing to different sheets about every 30 seconds.

I can use a try statement and if it bombs re-login. I was wondering if anybody had a better way?

I'm used to using the simple example from gspread example of:

gc = gspread.login('thedude@abid.es', 'password')
sht1 = gc.open_by_key('0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE')

How do I turn this into a keep alive connection login to arrive at sht1?

'str' does not support the buffer interface Python 3 error for oauth

import json
import gspread
from oauth2client.client import SignedJwtAssertionCredentials

json_key = json.load(open('Crowds-9569176f5988.json'))
scope = ['https://spreadsheets.google.com/feeds']

credentials = SignedJwtAssertionCredentials(json_key['client_email'], json_key['private_key'], scope)
#gc = gspread.authorize(credentials)

Error:

Traceback (most recent call last):   File "C:\Users\sony\Desktop\Python\new.py", line 8, in <module>
    credentials = SignedJwtAssertionCredentials(json_key['client_email'], json_key['private_key'], scope)   File "C:\Python34\lib\site-packages\oauth2client\util.py", line 137, in positional_wrapper
    return wrapped(*args, **kwargs)   File "C:\Python34\lib\site-packages\oauth2client\client.py", line 1469, in
__init__
    self.private_key = base64.b64encode(private_key)   File "C:\Python34\lib\base64.py", line 62, in b64encode
    encoded = binascii.b2a_base64(s)[:-1] TypeError: 'str' does not support the buffer interface

I tried encode the string using .encode() but gspread.authorize() doesn't support such a type. This is [documentation][1] which is apparently not of much help. I am using Python 3.4. I think the documentation code works only for version before 3.

EDIT:

credentials = SignedJwtAssertionCredentials.from_json(json_key['client_email'], json_key['private_key'], scope)

Traceback (most recent call last): File "C:\Users\sony\Desktop\Python\new.py", line 9, in credentials = SignedJwtAssertionCredentials.from_json(json_key['client_email'], json_key['private_key'], scope) TypeError: from_json() takes 2 positional arguments but 4 were given [Finished in 1.0s with exit code 1]

[1]: http://gspread.readthedocs.org/en/latest/oauth2.html

credentials = SignedJwtAssertionCredentials.from_json(json_key)

Traceback (most recent call last): File "C:\Users\sony\Desktop\Python\new.py", line 8, in credentials = SignedJwtAssertionCredentials.from_json(json_key) File "C:\Python34\lib\site-packages\oauth2client\client.py", line 1479, in from_json data = json.loads(s) File "C:\Python34\lib\json__init__.py", line 312, in loads s.class.name)) TypeError: the JSON object must be str, not 'dict'

Using gspread with proxy

I am trying to access google spreadsheets through gspread api in python. I have imported gspread. I am getting socket. error: [Errno 10061] No connection could be made because the target machine actively refused it at gc = gspread.login('pan*******@gmail.com', '********')

Here is my code:

import urllib2
import urllib
import gspread
from PIL import Image
from PIL import ImageDraw
from PIL import ImageFont

w = 420

gc = gspread.login('pan******@gmail.com', '*******')
wks = gc.open("Spreadsheet").sheet1

I have checked that the username and password is correct. I have added two environment variables as 'http_proxy':'host:port' and 'https_proxy':'host:port' What am I doing wrong? Thank you.

Using OAuth and gdata to copy google spreadsheet

I am working with gspread to modify existing Google spreadsheets and would like to make a copy of an existing one. Unfortunately, gspread doesn't support this, but it can be done with gdata (as described in this thread):

import gdata.docs.client

docs_client = gdata.docs.client.DocsClient()
docs_client.ClientLogin('ashe@pokemon.com', 'Pikachu', 'Any non empty string')
base_resource = docs_client.GetResourceById(resource_id)
new_resource = docs_client.copy_resource(base_resource, 'pokedex')

I want to achieve this with OAuth rather than a separate e-mail/password combination for the ClientLogin (or any method that can get the desired results; documentation seems nightmarishly poor for the Google APIs). Is there a simple way to do this?

Using gspread with OAuth2 SignedJwtAssertionCredentials

I am trying to access my google Spreadsheet using oauth2 using the gspread Python Library.I am new to Oauth2 and understand its benefits.But I am unable to use it.So far I have visited https://code.google.com/apis/console/ and generated CLIENT ID,SECRET and REDIRECT URI.

credentials = SignedJwtAssertionCredentials('developer@example.com', SIGNED_KEY, scope)

According to the Gspread Docs I will need a SIGNED_KEY object.How do I get that?

An example will be very helpful.

gspread writing data on google spreadsheet gives error on live database

I have used the gspread for writing the data on google spreadsheet. Its working fine locally but when I was trying at live server it gives me a following error .

File "/home/openerp10/openerp_7.0/addons/spreadsheet_calculate_all/spreadsheet_calculate_all.py", line 53, in insert
a = c.open(title).sheet1
File "/usr/local/lib/python2.7/dist-packages/gspread-0.1.0-py2.7.egg/gspread/client.py", line 130, in open
raise SpreadsheetNotFound
SpreadsheetNotFound

The code is as follow,

res_user = self.pool.get('res.users')
sale = self.browse(cr,uid,ids)[0]
username = res_user.browse(cr, uid, uid, context=context).gmail_user
passwd = res_user.browse(cr, uid, uid, context=context).gmail_password
title = sale.document
if title and username and passwd:
    try:
        c = gspread.Client(auth=(username,passwd))
        c.login()
        a = c.open(title).sheet1
    except Exception, e:
        raise osv.except_osv(_('User Error!'), _('Please give correct google username,password and document title.'))

Please give me any solution on it. Thanks in advance for your valuable time.

Get_all_values from Google Spreadsheet

I am having an issue with Gspread's get_all_values()

import json
import gspread
from oauth2client.client import SignedJwtAssertionCredentials
from gmail_variables import *

json_key = json.load(open('key7367.json'))
scope = ['https://spreadsheets.google.com/feeds']

credentials = SignedJwtAssertionCredentials(json_key['client_email'], json_key['private_key'], scope)

gc = gspread.authorize(credentials)


wks = gc.open_by_url('https://docs.google.com/a/test.com/spreadsheets/d/1-cAg..sLt5Ho/edit?usp=sharing')

recipients = wks.get_all_values()

I am trying to write an email program to pull from a Google Spreadsheet. When I try to run it; I get the error.

'Spreadsheet' object has no attribute 'get_all_values'

Any help is appreciated.

Gspread httperror when updating cell

For some reason I get: gspread.httpsession.HTTPError when trying to update a cell.

spreadsheet_name = raw_input("Please enter a spreadsheet url: ")
json_keys = json.load(open('keys.json'))
scopes = ['https://spreadsheets.google.com/feeds']
credentialss = SignedJwtAssertionCredentials(json_keys['client_email'], 
json_keys['private_key'], scopes)
gcs = gspread.authorize(credentialss)
wkss = gcs.open_by_url(spreadsheet_name).sheet1

in one function returning wkss and then

time.sleep(3)
all_data = wkss.get_all_values()
row_track = 0
for rows in all_data:
    print str(row_track)
    for dd in data:
        if str(rows[5]) == str(dd[1]):
            pdb.set_trace()
            wkss.update_cell(row_track, 29, dd[2].strip())
            print str(row_track) + ", 24" 
            print "MATCH"
    row_track +=1

python gspread google spreadsheet keeping connection alive

I found that but since I am accessing the worksheet by way where should I include the headers? The client does not seem to like it if I follow that.

Error importing gspread in iPython Notebook

So I have an iPython notebook setup in Pycharm and am getting the "ImportError: No module named gspread" error thrown when I run:

import gspread

I'm running this inside a virtualenv that I have verified in PyCharm is the one being used. I pip installed gspread. I tested that I can import other modules (i.e. numpy) without issue. From the iPython command line I do not get the error when importing gspread, it is only when running it inside the iPython notebook on Pycharm.

Any idea what might be the issue? I'm stumped.

Python/gspread - how can I update multiple cells with DIFFERENT VALUES at once?

To update a range of cells, you use the following command.

## Select a range
cell_list = worksheet.range('A1:A7')

for cell in cell_list:
    cell.value = 'O_o'

## Update in batch
worksheet.update_cells(cell_list)

For my application, I would like it to update an entire range, but I am trying to set a different value for each individual cell. The problem with this example is that every cell ends up with the same value. Updating each cell individually is inefficient and takes way too long. How can I do this efficiently?

gspread update cells in batch, 50k limit?

I have been using gspread with no problem for a while. I recently went over 50000 cells when updating to Google sheets. I'm getting a

File "build\bdist.win32\egg\gspread\httpsession.py", line 81, in request raise HTTPError(response) HTTPError

Did it take so long to update that the connection timed out?

My cell range to update is A1:CL560. I sliced the output to just under 50000 cells and it the code worked fine.

the code I'm using is just what is referenced on github:

cell_list = worksheet.range('A1:C7')

for cell in cell_list:
    cell.value = 'O_o'

# Update in batch
worksheet.update_cells(cell_list)

everything seems to be fine under 50000 cells. Not sure what to do.

This is the error: error: [Errno 10054] An existing connection was forcibly closed by the remote host

I've tried to keep the connection alive, still doesn't work.

gspread gives TypeError: expected bytes when trying to log in

I'm trying to use Google Spreadsheets OAuth with the gspread library. I get a TypeError: expected bytes, not str exception when sending credentials. How do I fix this?

import gspread
import json
from oauth2client.client import SignedJwtAssertionCredentials

json_key = json.load(open('Test for gspread-78c678a7fb15.json'))
scope = ['https://spreadsheets.google.com/feeds']
credentials = SignedJwtAssertionCredentials(json_key['client_email'], json_key['private_key'], scope)
gc = gspread.authorize(credentials)

Traceback (most recent call last):
  File "<pyshell#27>", line 1, in <module>
    credentials = SignedJwtAssertionCredentials(json_key['client_email'], json_key['private_key'], scope)
  File "C:\Python33\lib\site-packages\oauth2client\util.py", line 137, in positional_wrapper
    return wrapped(*args, **kwargs)
  File "C:\Python33\lib\site-packages\oauth2client\client.py", line 1515, in __init__
    self.private_key = base64.b64encode(private_key)
  File "C:\Python33\lib\base64.py", line 58, in b64encode
    raise TypeError("expected bytes, not %s" % s.__class__.__name__)
TypeError: expected bytes, not str

Modified tweepy stream class

I have a project to practice my Python skills:

1. To extract some tweets coordinates with Tweepy Stream
2. To put them into a Google spreadsheet
3. Then use the Google spreadsheet to create a map in CartoDB

I am already able to do all these things independently. Now, the challenge is to make everything work together! :)

To update my Google Spreadsheet, I am using gspread.

However, to update a cell, I need to indicate the row and column of the cell like this:

worksheet.update_acell('B1', 'Bingo!')

I am trying to have a counter in my script extracting the tweets. The goal is to have B1 to change to B2, then B3, then B4, each time a tweet is found.

But it's not working... The coordinates are printed out on my Terminal, but that's it.

I guess I am not using the class as I am supposed to. But I don't understand where is my error!

Help?

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import tweepy
import gspread
import time

CONSUMER_KEY, CONSUMER_SECRET = 'SECRET', 'SECRET'
USER_KEY, USER_SECRET = 'SECRET', 'SECRET'

class MyStream(tweepy.StreamListener):
    def __init__(self):
        tweepy.StreamListener.__init__(self)

        # I added this to have a counter.
        self.n = 2

    def on_status(self, tweet):
        try:
            longitude = str(tweet.coordinates['coordinates'][0])
            latitude =  str(tweet.coordinates['coordinates'][1])
            print longitude
            print latitude

            # I added this to update my google spreadsheet with the coordinates
            self.wks.update_acell(('A' + str(n)), longitude)
            self.wks.update_acell(('B' + str(n)), latitude)
            print "Spreadsheet updated!"

            # This is for my counter
            self.n += 1

        except:
            pass


def main():

    #I added these two lines to connect to my google spreadsheet
    gc = gspread.login('EMAIL', 'PASSWORD')
    wks = gc.open('SPREADSHEET_NAME').sheet1

    auth = tweepy.OAuthHandler(CONSUMER_KEY, CONSUMER_SECRET)
    auth.set_access_token(USER_KEY, USER_SECRET)
    stream = tweepy.Stream(auth, MyStream(), timeout=50)
    stream.filter(locations=[-74.00,45.40,-73.41,45.72])

if __name__ == "__main__":
    main()

python gspread authorize oauth2 credentials

How can i make gspread use my oauth credentials? I having error for http response gspread.httpsession.HTTPError :( can anyone tell me what's wrong with my code?

import datetime
import gspread
import json
import MySQLdb
from oauth2client.client import OAuth2Credentials


# Get access token from database
data = json.loads(row['access_token'])
credentials = OAuth2Credentials(
    data['access_token'],
    "1*************************k.apps.googleusercontent.com",
    "9t*****************W",
    data['refresh_token'],
    datetime.datetime.now(),
    "https://accounts.google.com/o/oauth2/token",
    'user-agent'
)

gc = gspread.authorize(credentials)
wks = gc.open_by_key('1*****************W')

Where row contains data from database (It has access token returned by google oauth)? m(_ _)m

Populating google spreadsheet by row, not by cell

I have a spreadsheet whose values I want to populate with values from dictionaries within a list. I wrote a for loop that updates cell by cell, but it is too slow and I get the gspread.httpsession.HTTPError often. I am trying to write a loop to update row by row. Thats what I have:

lstdic=[
{'Amount': 583.33, 'Notes': '', 'Name': 'Jone', 'isTrue': False,},
{'Amount': 58.4, 'Notes': '', 'Name': 'Kit', 'isTrue': False,},
{'Amount': 1083.27, 'Notes': 'Nothing', 'Name': 'Jordan', 'isTrue': True,}
]

Here is my cell by cell loop:

headers = wks.row_values(1)

    for k in range(len(lstdic)):
        for key in headers:
            cell = wks.find(key)
            cell_value = lstdic[k][key]
            wks.update_cell(cell.row + 1 + k, cell.col, cell_value)

What it does is it finds a header that corresponds to the key in the list of dictionaries and updates the cell under it. The next iteration the row is increased by one, so it updates cells in the same columns, but next row. This is too slow and I want to update by row. My attempt:

headers = wks.row_values(1)

row=2
for k in range(len(lsdic)):
    cell_list=wks.range('B%s:AA%s' % (row,row))
    for key in headers: 
        for cell in cell_list:
            cell.value = lsdic[k][key]
    row+=1
    wks.update_cells(cell_list)

This one updates each row quickly, but with the same value. So, the third nested for loop assigns the the same value for each cell. I am breaking my head trying to figure out how to assign right values to the cells. Help appreciated.

P.S. by the way I am using headers because I want a certain order in which values in the google spreadsheet should appear.