# -*- coding: utf-8 -*-.
import re
import warnings
import os
import logging
from pygsheets.drive import DriveAPIWrapper
from pygsheets.sheet import SheetAPIWrapper
from pygsheets.spreadsheet import Spreadsheet
from pygsheets.exceptions import SpreadsheetNotFound, NoValidUrlKeyFound
from pygsheets.custom_types import ValueRenderOption, DateTimeRenderOption
from google_auth_httplib2 import AuthorizedHttp
GOOGLE_SHEET_CELL_UPDATES_LIMIT = 50000
_url_key_re_v1 = re.compile(r'key=([^&#]+)')
_url_key_re_v2 = re.compile(r"/spreadsheets/d/([a-zA-Z0-9-_]+)")
_email_patttern = re.compile(r"\"?([-a-zA-Z0-9.`?{}]+@[-a-zA-Z0-9.]+\.\w+)\"?")
# _domain_pattern = re.compile("(?!-)[A-Z\d-]{1,63}(?<!-)$", re.IGNORECASE)
_deprecated_keyword_mapping = {
'parent_id': 'folder',
}
[docs]class Client(object):
"""Create or access Google spreadsheets.
Exposes members to create new spreadsheets or open existing ones. Use `authorize` to instantiate an instance of this
class.
>>> import pygsheets
>>> c = pygsheets.authorize()
The sheet API service object is stored in the sheet property and the drive API service object in the drive property.
>>> c.sheet.get('<SPREADSHEET ID>')
>>> c.drive.delete('<FILE ID>')
:param credentials: The credentials object returned by google-auth or google-auth-oauthlib.
:param retries: (Optional) Number of times to retry a connection before raising a TimeOut error.
Default: 3
:param http: The underlying HTTP object to use to make requests. If not specified, a
:class:`httplib2.Http` instance will be constructed.
:param check: Check for quota error and apply rate limiting.
:param seconds_per_quota: Default value is 100 seconds
"""
spreadsheet_cls = Spreadsheet
def __init__(self, credentials, retries=3, http=None, check=True, seconds_per_quota=100):
self.oauth = credentials
self.logger = logging.getLogger(__name__)
http = AuthorizedHttp(credentials, http=http)
data_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), "data")
self.sheet = SheetAPIWrapper(http, data_path, retries=retries, check=check, seconds_per_quota=seconds_per_quota)
self.drive = DriveAPIWrapper(http, data_path)
@property
def teamDriveId(self):
""" Enable team drive support, set None to disable
Deprecated: use client.drive.enable_team_drive(team_drive_id=?)
"""
return self.drive.team_drive_id
@teamDriveId.setter
def teamDriveId(self, value):
warnings.warn("Depricated please use drive.enable_team_drive")
self.drive.enable_team_drive(value)
[docs] def spreadsheet_ids(self, query=None):
"""Get a list of all spreadsheet ids present in the Google Drive or TeamDrive accessed."""
return [x['id'] for x in self.drive.spreadsheet_metadata(query)]
def set_batch_mode(self, value):
"""Set the client in batch mode. If True will batch all custom requests and wil combine them
into single request. setting batchmode will clear all previous cached data. Also note that batch mode
only caches sheetUpdate requests not value updates or clear requests."""
self.sheet.set_batch_mode(value)
def run_batch(self):
"""Run currently batched requests."""
self.sheet.run_batch()
[docs] def spreadsheet_titles(self, query=None):
"""Get a list of all spreadsheet titles present in the Google Drive or TeamDrive accessed."""
return [x['name'] for x in self.drive.spreadsheet_metadata(query)]
[docs] def create(self, title, template=None, folder=None, folder_name=None, **kwargs):
"""Create a new spreadsheet.
The title will always be set to the given value (even overwriting the templates title). The template
can either be a `spreadsheet resource <https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#resource-spreadsheet>`_
or an instance of :class:`~pygsheets.Spreadsheet`. In both cases undefined values will be ignored.
:param title: Title of the new spreadsheet.
:param template: A template to create the new spreadsheet from.
:param folder: The Id of the folder this sheet will be stored in.
:param folder_name: The Name of the folder this sheet will be stored in.
:param kwargs: Standard parameters (see reference for details).
:return: :class:`~pygsheets.Spreadsheet`
"""
if isinstance(template, str):
result = self.drive.copy_file(template, title, folder)
return self.open_by_key(result['id'])
if isinstance(template, Spreadsheet):
result = self.drive.copy_file(template.id, title, folder)
return self.open_by_key(result['id'])
if folder_name and not folder:
folder = self.drive.get_folder_id(folder_name)
result = self.sheet.create(title, template=template, **kwargs)
if folder:
self.drive.move_file(result['spreadsheetId'],
old_folder=self.drive.spreadsheet_metadata(fid=result['spreadsheetId'])[0].get('parents', [None])[0],
new_folder=folder)
return self.spreadsheet_cls(self, jsonsheet=result)
[docs] def open(self, title):
"""Open a spreadsheet by title.
In a case where there are several sheets with the same title, the first one found is returned.
>>> import pygsheets
>>> c = pygsheets.authorize()
>>> c.open('TestSheet')
:param title: A title of a spreadsheet.
:returns: :class:`~pygsheets.Spreadsheet`
:raises pygsheets.SpreadsheetNotFound: No spreadsheet with the given title was found.
"""
try:
spreadsheet = list(filter(lambda x: x['name'] == title, self.drive.spreadsheet_metadata()))[0]
return self.open_by_key(spreadsheet['id'])
except (KeyError, IndexError):
raise SpreadsheetNotFound('Could not find a spreadsheet with title %s.' % title)
[docs] def open_by_key(self, key):
"""Open a spreadsheet by key.
>>> import pygsheets
>>> c = pygsheets.authorize()
>>> c.open_by_key('0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE')
:param key: The key of a spreadsheet. (can be found in the sheet URL)
:returns: :class:`~pygsheets.Spreadsheet`
:raises pygsheets.SpreadsheetNotFound: The given spreadsheet ID was not found.
"""
response = self.sheet.get(key, includeGridData=False)
return self.spreadsheet_cls(self, response)
[docs] def open_by_url(self, url):
"""Open a spreadsheet by URL.
>>> import pygsheets
>>> c = pygsheets.authorize()
>>> c.open_by_url('https://docs.google.com/spreadsheet/ccc?key=0Bm...FE&hl')
:param url: URL of a spreadsheet as it appears in a browser.
:returns: :class:`~pygsheets.Spreadsheet`
:raises pygsheets.SpreadsheetNotFound: No spreadsheet was found with the given URL.
"""
m1 = _url_key_re_v1.search(url)
if m1:
return self.open_by_key(m1.group(1))
else:
m2 = _url_key_re_v2.search(url)
if m2:
return self.open_by_key(m2.group(1))
else:
raise NoValidUrlKeyFound
[docs] def open_all(self, query=''):
"""Opens all available spreadsheets.
Result can be filtered when specifying the query parameter. On the details on how to form the query:
`Reference <https://developers.google.com/drive/v3/web/search-parameters>`_
:param query: (Optional) Can be used to filter the returned metadata.
:returns: A list of :class:`~pygsheets.Spreadsheet`.
"""
return [self.open_by_key(key) for key in self.spreadsheet_ids(query=query)]
[docs] def open_as_json(self, key):
"""Return a json representation of the spreadsheet.
See `Reference <https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#Spreadsheet>`__ for details.
"""
return self.sheet.get(key,
fields='properties,sheets(properties,protectedRanges,merges,conditionalFormats,filterViews),spreadsheetId,namedRanges',
includeGridData=False)
[docs] def get_range(self, spreadsheet_id,
value_range=None,
major_dimension='ROWS',
value_render_option=ValueRenderOption.FORMATTED_VALUE,
date_time_render_option=DateTimeRenderOption.SERIAL_NUMBER,
value_ranges=None):
"""Returns a range of values from a spreadsheet. The caller must specify the spreadsheet ID and a range.
Reference: `request <https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get>`__
:param spreadsheet_id: The ID of the spreadsheet to retrieve data from.
:param value_range: The A1 notation of the values to retrieve.
:param value_ranges: The list of A1 notation of the values to retrieve.
:param major_dimension: The major dimension that results should use.
For example, if the spreadsheet data is: A1=1,B1=2,A2=3,B2=4, then
requesting range=A1:B2,majorDimension=ROWS will return [[1,2],[3,4]],
whereas requesting range=A1:B2,majorDimension=COLUMNS will return
[[1,3],[2,4]].
:param value_render_option: How values should be represented in the output. The default
render option is `ValueRenderOption.FORMATTED_VALUE`.
:param date_time_render_option: How dates, times, and durations should be represented in the output.
This is ignored if `valueRenderOption` is `FORMATTED_VALUE`. The default
dateTime render option is [`DateTimeRenderOption.SERIAL_NUMBER`].
:return: An array of arrays with the values fetched. Returns an empty array if no
values were fetched. Values are dynamically typed as int, float or string.
"""
if value_range:
result = self.sheet.values_get(spreadsheet_id, value_range, major_dimension, value_render_option,
date_time_render_option)
try:
return result['values']
except KeyError:
return [['']]
elif value_ranges:
results = self.sheet.values_batch_get(spreadsheet_id, value_ranges, major_dimension, value_render_option,
date_time_render_option)
values = []
for result in results:
try:
values.append(result['values'])
except KeyError:
values.append([['']])
return values