%load_ext autoreload
%autoreload 2
import json
import requests as rq
from urllib.parse import quote
from ipyauth import Auth, ParamsGoogle
%cat ipyauth-google-demo.env
client_id=813774364274-m23lt715n2pb3ov5cnb3no543gappv8g.apps.googleusercontent.com redirect_uri=http://localhost:8888/callback/ response_type=token
scope = ' '.join(['profile', 'openid',
'https://www.googleapis.com/auth/drive',
'https://www.googleapis.com/auth/spreadsheets'])
p = ParamsGoogle(dotenv_file='ipyauth-google-demo.env', scope=scope)
p
{ "name": "google", "url_params": { "response_type": "token", "client_id": "813774364274-m23lt715n2pb3ov5cnb3no543gappv8g.apps.googleusercontent.com", "redirect_uri": "http://localhost:8888/callback/", "scope": "profile openid https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/spreadsheets", "include_granted_scopes": "false" } }
a = Auth(params=p)
a
Auth(children=(HBox(children=(Image(value=b'\x89PNG\r\n\x1a\n\x00\x00\x00\rIHDR\x00\x00\x02\x12\x00\x00\x00\xc…
a.show()
name = google _id = google-krycab params = {'name': 'google', 'url_params': {'response_type': 'token', 'client_id': '813774364274-m23lt715n2pb3ov5cnb3no543gappv8g.apps.googleusercontent.com', 'redirect_uri': 'http://localhost:8888/callback/', 'scope': 'profile openid https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/spreadsheets', 'include_granted_scopes': 'false'}} logged_as = Olivier Borderies time_to_exp = 00:59:54 expires_at = Mon Jul 02 2018 23:03:54 GMT+0200 (Central European Summer Time) scope = https://www.googleapis.com/auth/userinfo.profile https://www.googleapis.com/auth/plus.me https://www.googleapis.com/auth/drive https://www.googleapis.com/auth/spreadsheets access_token = ya29.GlvsBckRvyuV0R0s9-GYV3VeYXwpP9TQkCJChwZehtgwcdmLmAe0UzPSsc2Y1yOkeUi5SMZoTy7z6K9-MveSkDwrkLwEGRgay4mX2l11PA9zkcJWKAqfqdfDxgcr _incr_signout = 0
# a.clear()
headers = {
'Authorization': 'Bearer {}'.format(a.access_token),
'Accept': 'application/json',
'Content-Type': 'application/json',
}
headers
{'Authorization': 'Bearer ya29.GlzDBcYZBG4BNN0d9E_IrdEc1PJyoosw5tBOSzii2sDU7PelR_S-6Ra4f4rPBUcYH-7oujJYA55bJDHE7PU8najt3QnpoNlGIoKAK4SjuQ9Cz0FxSj4bOyakjfvWzg', 'Accept': 'application/json', 'Content-Type': 'application/json'}
url = 'https://www.googleapis.com/drive/v3/files'
print(url)
params = {'q': 'name = "WIP"'}
r = rq.get(url, headers=headers, params=params)
print(r.status_code)
data = json.loads(r.content.decode('utf-8'))
data
https://www.googleapis.com/drive/v3/files 200
{'kind': 'drive#fileList', 'incompleteSearch': False, 'files': [{'kind': 'drive#file', 'id': '1jnjv-FwuYqOpl19pqvBMMU7kzEY5S7uu', 'name': 'WIP', 'mimeType': 'application/vnd.google-apps.folder'}]}
folder_id = data['files'][0]['id']
folder_id
'1jnjv-FwuYqOpl19pqvBMMU7kzEY5S7uu'
url = 'https://www.googleapis.com/drive/v3/files'
print(url)
data = json.dumps({
'name': 'demo-sheet',
'mimeType': 'application/vnd.google-apps.spreadsheet',
'parents': [folder_id]
})
r = rq.post(url, headers=headers, data=data)
print(r.status_code)
data = json.loads(r.content.decode('utf-8'))
data
https://www.googleapis.com/drive/v3/files 200
{'kind': 'drive#file', 'id': '1CojkeuO4LC0oKXKWeRZ7WEzQrSbX8s3Azht0eAcBj40', 'name': 'demo-sheet', 'mimeType': 'application/vnd.google-apps.spreadsheet'}
sheet_id = data['id']
sheet_id
'1CojkeuO4LC0oKXKWeRZ7WEzQrSbX8s3Azht0eAcBj40'
sheet_range = 'A2:D3'
url = 'https://sheets.googleapis.com/v4/spreadsheets/{}/values/{}'.format(sheet_id,
quote(sheet_range))
print(url)
params = {'valueInputOption': 'RAW'}
data = json.dumps({'values': [[1, 2, 3, 4], [5, 6, 7, 8]]})
r = rq.put(url, headers=headers, params=params, data=data)
print(r.status_code)
data = json.loads(r.content.decode('utf-8'))
data
https://sheets.googleapis.com/v4/spreadsheets/1CojkeuO4LC0oKXKWeRZ7WEzQrSbX8s3Azht0eAcBj40/values/A2%3AD3 200
{'spreadsheetId': '1CojkeuO4LC0oKXKWeRZ7WEzQrSbX8s3Azht0eAcBj40', 'updatedRange': 'Sheet1!A2:D3', 'updatedRows': 2, 'updatedColumns': 4, 'updatedCells': 8}
sheet_range = 'D2:D4'
url = 'https://sheets.googleapis.com/v4/spreadsheets/{}/values/{}'.format(sheet_id,
quote(sheet_range))
print(url)
params = {'valueInputOption': 'RAW'}
data = json.dumps({'values': [[11], [12], [13]]})
r = rq.put(url, headers=headers, params=params, data=data)
print(r.status_code)
data = json.loads(r.content.decode('utf-8'))
data
https://sheets.googleapis.com/v4/spreadsheets/1CojkeuO4LC0oKXKWeRZ7WEzQrSbX8s3Azht0eAcBj40/values/D2%3AD4 200
{'spreadsheetId': '1CojkeuO4LC0oKXKWeRZ7WEzQrSbX8s3Azht0eAcBj40', 'updatedRange': 'Sheet1!D2:D4', 'updatedRows': 3, 'updatedColumns': 1, 'updatedCells': 3}
sheet_range = 'C2:C20'
url = 'https://sheets.googleapis.com/v4/spreadsheets/{}/values/{}:clear'.format(sheet_id,
quote(sheet_range))
print(url)
r = rq.post(url, headers=headers)
print(r.status_code)
data = json.loads(r.content.decode('utf-8'))
data
https://sheets.googleapis.com/v4/spreadsheets/1CojkeuO4LC0oKXKWeRZ7WEzQrSbX8s3Azht0eAcBj40/values/C2%3AC20:clear 200
{'spreadsheetId': '1CojkeuO4LC0oKXKWeRZ7WEzQrSbX8s3Azht0eAcBj40', 'clearedRange': 'Sheet1!C2:C20'}
url = 'https://www.googleapis.com/drive/v3/files/{}/permissions'.format(sheet_id)
print(url)
params = {
'emailMessage': 'Please have a look at the demo-sheet - You can read it but not modify it.',
'sendNotificationEmail': 'true',
}
data = json.dumps({
'role': 'reader',
'type': 'user',
'emailAddress': 'oscar6echo@gmail.com' # please do not spam me !
})
r = rq.post(url, headers=headers, params=params, data=data)
print(r.status_code)
data = json.loads(r.content.decode('utf-8'))
data
https://www.googleapis.com/drive/v3/files/1CojkeuO4LC0oKXKWeRZ7WEzQrSbX8s3Azht0eAcBj40/permissions 200
{'kind': 'drive#permission', 'id': '16685572286343058353', 'type': 'user', 'role': 'reader'}
That's it folks !
You just created a Sheet, put in some data, and shared it to other people - from the comfort of your Jupyter notebook.
Naturally these API calls can easily be wrapped for a more concise interface.