Navigation Menu

Skip to content

Instantly share code, notes, and snippets.

@archeg
Created May 26, 2013 13:14
Show Gist options
  • Star 4 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save archeg/5652752 to your computer and use it in GitHub Desktop.
Save archeg/5652752 to your computer and use it in GitHub Desktop.
Example of working with notes on Google Spreadsheet
__author__ = 'archeg'
import httplib
import urllib
import urllib2
import re
def URLRequest(url, params, headers, method="GET"):
if method == "POST":
return urllib2.Request(url, data=urllib.urlencode(params), headers=headers)
else:
return urllib2.Request(url + "?" + urllib.urlencode(params))
def setNote(row, column, value, tokens, spreadSheetId):
""" Sets a note to the given cell
Args:
row: row coordinate
column: column coordinate
value: new value of the note (will be overriden)
tokens: dictionary of auth tokens, returned by GetAuthTokens
spreadSheetId: id of the spreadsheet. Be aware that this is not the key of the spreadsheet and cannot be got from url directly. Use GetSpreadsheetId function
"""
cookies = "SID=%s; HSID=%s; SSID=%s;" % (tokens['SID'], tokens['HSID'], tokens['SSID'])
print cookies
headers = {
"Content-Type": "application/x-www-form-urlencoded;",
"Cookie": cookies,
"X-Same-Domain": "trix"
}
data = {
"action": 9,
"atyp": 501,
"ecol": column,
"erow": row,
"gid": 0, # TODO: Get Worksheet id
"scol": column,
"srow": row,
"v": value}
url = "https://docs.google.com/a/ciklum.com/spreadsheet/edit/action9?id=" + spreadSheetId
a = urllib2.Request(url, data=urllib.urlencode(data), headers=headers)
res = urllib2.urlopen(a)
b = res.read().decode()
print b
CRLF = '\r\n'
# The following are used for authentication functions.
GAIA_HOST = 'www.google.com'
LOGIN_URI = '/accounts/ServiceLoginAuth'
LOGIN_URL = 'https://www.google.com/accounts/ClientLogin'
SERVICELOGIN_URL = 'https://www.google.com/accounts/ServiceLogin'
HOST = "accounts.google.com"
SERVICE = 'wise'
CLIENT_NAME = 'Example client to put notes on the cells'
# Example taken from https://developers.google.com/cloud-print/docs/pythonCode
def GetCookie(cookie_key, cookie_string):
"""Extract the cookie value from a set-cookie string.
Args:
cookie_key: string, cookie identifier.
cookie_string: string, from a set-cookie command.
Returns:
string, value of cookie.
"""
print('Getting cookie from %s' % cookie_string)
id_string = cookie_key + '='
cookie_crumbs = cookie_string.split(';')
for c in cookie_crumbs:
if id_string in c:
cookie = c.split(id_string)
return cookie[1]
return None
# Example taken from https://developers.google.com/cloud-print/docs/pythonCode
def GaiaLogin(email, password):
"""Login to gaia using HTTP post to the gaia login page.
Args:
email: string,
password: string
Returns:
dictionary of authentication tokens.
"""
tokens = {}
cookie_keys = ['SID', 'LSID', 'HSID', 'SSID']
email = email.replace('+', '%2B')
# Load sign in page to retrieve Galx
opener = urllib2.build_opener(urllib2.HTTPCookieProcessor())
urllib2.install_opener(opener)
login_page_contents = opener.open(SERVICELOGIN_URL).read()
galx_match_obj = re.search(r'name="GALX"\s*value="([^"]+)"', login_page_contents, re.IGNORECASE)
galx_cookie = galx_match_obj.group(1) if galx_match_obj.group(1) is not None else ''
# Forming a PUT request to login and retrieve auth data
form = "GALX=%s&Email=%s&Passwd=%s" % (galx_cookie, email, password)
login = httplib.HTTPS(GAIA_HOST, 443)
login.putrequest('POST', LOGIN_URI)
login.putheader('Host', HOST)
login.putheader('content-type', 'application/x-www-form-urlencoded')
login.putheader('content-length', str(len(form)))
login.putheader('Cookie', 'GALX=%s' % galx_cookie)
print('Sent POST content: %s' % form)
login.endheaders()
print('HTTP POST to https://%s%s' % (GAIA_HOST, LOGIN_URI))
login.send(form)
(errcode, errmsg, headers) = login.getreply()
login_output = login.getfile()
login_output.close()
login.close()
print('Login complete.')
if errcode != 302:
print('Gaia HTTP post returned %d, expected 302' % errcode)
print('Message: %s' % errmsg)
for line in str(headers).split('\r\n'):
if not line: continue
(name, content) = line.split(':', 1)
if name.lower() == 'set-cookie':
for k in cookie_keys:
if content.strip().startswith(k):
tokens[k] = GetCookie(k, content)
if not tokens:
print('No cookies received, check post parameters.')
return None
else:
print('Received the following authorization tokens.')
for t in tokens:
print(t)
return tokens
# Example taken from https://developers.google.com/cloud-print/docs/pythonCode
# Auth token is not needed for this particular script, but could be useful overall, so I kept it.
def GetAuthTokens(email, password):
"""Assign login credentials from GAIA accounts service.
Args:
email: Email address of the Google account to use.
password: Cleartext password of the email account.
Returns:
dictionary containing Auth token.
"""
# First get GAIA login credentials using our GaiaLogin method.
tokens = GaiaLogin(email, password)
# We still need to get the Auth token.
params = {'accountType': 'GOOGLE',
'Email': email,
'Passwd': password,
'service': SERVICE,
'source': CLIENT_NAME}
stream = urllib.urlopen(LOGIN_URL, urllib.urlencode(params))
for line in stream:
if line.strip().startswith('Auth='):
tokens['Auth'] = line.strip().replace('Auth=', '')
return tokens
# Definitely not the best way to get spreadsheet id, it should be possible to do that simpler.
def GetSpreadsheetId(key, tokens):
""" Opens the spreadshet page and parses it's id from it's content
Args:
key: spreadsheet key, got from url
tokens: dictionary of authentication tokens
Returns:
real id of the spreadsheet
"""
print("Getting spreadsheet id")
opener = urllib2.build_opener(urllib2.HTTPCookieProcessor())
urllib2.install_opener(opener)
cookies = "SID=%s; HSID=%s; SSID=%s;" % (tokens['SID'], tokens['HSID'], tokens['SSID'])
opener.addheaders = [
("Cookie", cookies),
("user-agent", "Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.94 Safari/537.36")
]
pageContents = opener.open("https://docs.google.com/spreadsheet/ccc?&key=" + key).read()
match_obj = re.search(r'"id":"(\w*\.\w*\.\w*)"', pageContents)
spreadSheetId = match_obj.group(1) if match_obj.group(1) is not None else ''
return spreadSheetId
# Here it goes:
email = "example@gmail.com"
pwd = "qwerty"
column = 2
row = 10
noteValue = 'Hello notes!'
spreadSheetKey = "SpreadSheet key" # Can be got from spreadsheet URL by looking at &key= parameter
tokens = GetAuthTokens(email, pwd)
setNote(row, column, noteValue, tokens, GetSpreadsheetId(spreadSheetKey, tokens))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment