Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Python Google spreadsheets v4 API example. Google spreadsheet access management example. Use google drive v3 API for access management
"""Google spreadsheet related.
Packages required: oauth2client, google-api-python-client
* https://gist.github.com/miohtama/f988a5a83a301dd27469
"""
from oauth2client.service_account import ServiceAccountCredentials
from apiclient import discovery
def get_credentials(scopes: list) -> ServiceAccountCredentials:
c = ServiceAccountCredentials.from_json_keyfile_name('auth.json', scopes)
return c
class API:
def __init__(self, credentials):
self._credentials = credentials
def build(self, service, version):
return discovery.build(service, version, credentials=self._credentials)
def get_api_kwargs(self):
return {
'credentials': self._credentials,
}
class Spreadsheet(API):
def __init__(self, info, **kwargs):
self._info = info
super().__init__(**kwargs)
@property
def id(self):
return self._info['spreadsheetId']
@property
def permissions(self):
permissions = self._get_permissions()
anyone = None
writers = []
readers = []
for x in permissions:
type = x['type'] #: 'user', 'anyone', 'group', 'domain'
role = x['role'] #: 'owner', 'commenter', 'reader', 'writer'
if type == 'anyone':
if role in ['owner', 'writer']:
anyone = 'write'
elif role in ['reader']:
anyone = 'read'
elif role in ['commenter']:
anyone = 'comment'
elif type == 'user':
x = self._get_permission(x['id'])
if role in ['owner', 'writer']:
lst = writers
elif role in ['reader', 'commenter']:
lst = readers
else:
raise RuntimeError('Protocol role unknown!')
lst.append(x['emailAddress'])
return {
'anyone': anyone,
'writers': writers,
'readers': readers,
}
def set_permissions(self, anyone=None, writers=None, readers=None):
assert anyone in [None, 'writer', 'write',
'reader', 'read', 'w', 'r'], 'not in ["r", "w"]'
drive = self.build('drive', 'v3')
if anyone:
role = {
'r': 'reader',
'read': 'reader',
'reader': 'reader',
'w': 'writer',
'write': 'writer',
'writer': 'writer',
}
body = {
'role': role[anyone],
'type': 'anyone',
}
drive.permissions().create(fileId=self.id, body=body).execute()
if writers:
body = {
'role': 'writer',
'type': 'user',
'emailAddress': writers,
}
drive.permissions().create(fileId=self.id, body=body).execute()
if readers:
body = {
'role': 'reader',
'type': 'user',
'emailAddress': readers,
}
drive.permissions().create(fileId=self.id, body=body).execute()
def _get_permissions(self):
drive = self.build('drive', 'v3')
r = drive.permissions().list(fileId=self.id).execute()
permissions = r.get('permissions', [])
return permissions
def _get_permission(self, id):
drive = self.build('drive', 'v3')
fields = 'allowFileDiscovery,displayName,domain,emailAddress,' \
'expirationTime,id,kind,photoLink,role,type'
permission = drive.permissions().get(
fileId=self.id, permissionId=id, fields=fields)
return permission.execute()
def append(self, data, gray=False, sheet=0):
spreadsheets = self.build('sheets', 'v4').spreadsheets()
value = lambda x: { # noqa
"userEnteredValue": {"stringValue": str(x)},
"userEnteredFormat": {"backgroundColor": {
"red": 0.8, "green": 0.8, "blue": 0.8, "alpha": 0.5}
}
} if gray else {
"userEnteredValue": {"stringValue": str(x)}
}
rows = [{"values": [value(cell) for cell in row]} for row in data]
body = {
"requests": [
{
"appendCells": {
"sheetId": sheet,
"rows": rows,
"fields": "*",
}
}
],
}
return spreadsheets.batchUpdate(spreadsheetId=self.id, body=body) \
.execute()
def __str__(self):
return "Sheet(%s)" % self.id
class Spreadsheets(API):
def create(self, title, anyone=None, writers=None, readers=None):
assert anyone in [None, 'write', 'read', 'comment', 'w', 'r', 'c'], \
'not in ["r", "w", "c"]'
sheets = self.build('sheets', 'v4')
body = {'properties': {'title': title}}
res = sheets.spreadsheets().create(body=body).execute()
spreadsheer = Spreadsheet(res, **self.get_api_kwargs())
spreadsheer.set_permissions(anyone=anyone, writers=writers,
readers=readers)
return spreadsheer
def get(self, id):
sheets = self.build('sheets', 'v4')
res = sheets.spreadsheets().get(spreadsheetId=id).execute()
return Spreadsheet(res, **self.get_api_kwargs())
def create_example():
credentials = get_credentials([
'https://www.googleapis.com/auth/drive',
'https://www.googleapis.com/auth/spreadsheets'])
api = Spreadsheets(credentials=credentials)
sheet = api.create('test1', writers=['pahaz.blinov@gmail.com'])
print(sheet)
def append_data_example():
credentials = get_credentials([
'https://www.googleapis.com/auth/drive',
'https://www.googleapis.com/auth/spreadsheets'])
api = Spreadsheets(credentials=credentials)
sheet = api.get('1jSHsmPTOOiPXdYQlrVBwbo3MO1vp64lmi5R_Ld_duKo')
sheet.append([[1, 2], [3, 4]])
if __name__ == '__main__':
append_data_example()
@govindsharma7

This comment has been minimized.

Copy link

commented Dec 6, 2017

Hi,
This code is not working. Please help me out to resolve the problem.

Thanks.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.