pygsheets

https://pygsheets.readthedocs.io/en/stable/
Admin
pygsheets is a simple, intuitive Python library to access Google Spreadsheets through the Google...
Tokens:58,237
Snippets:376
Trust Score:9.6
Update:2 months ago
Show doc for...
Tokens:
Raw
### Install pygsheets

Source: https://pygsheets.readthedocs.io/en/stable/index.html

Commands to install the library from the master branch or the stable release.

```bash
pip install https://github.com/nithinmurali/pygsheets/archive/master.zip
```

```bash
pip install pygsheets
```

--------------------------------

### get

Source: https://pygsheets.readthedocs.io/en/stable/sheet_api.html

Retrieves a full spreadsheet with all data.

```APIDOC
## get(spreadsheet_id, **kwargs)

### Description
Returns a full spreadsheet with the entire data.

### Parameters
- **spreadsheet_id** (str) - Required - The Id of the spreadsheet to return.
- **kwargs** (dict) - Optional - Standard parameters.
```

--------------------------------

### Access and set GridRange start

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/address.html

Property and setter for the start address of a GridRange, ensuring index constraints are applied.

```python
    @property
    def start(self):
        """ address of top left cell (index). """
        if not self._start and self._end:
            top_index = [1, 1]
            if not self._end[0]:
                top_index[0] = None
            if not self._end[1]:
                top_index[1] = None
            return Address(tuple(top_index), True)
        return self._start

    @start.setter
    def start(self, value):
        # prev = self._start, self._end
        self._start = Address(value, allow_non_single=True)
        self._apply_index_constraints()
        self._calculate_label()
```

--------------------------------

### Share a Spreadsheet

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/spreadsheet.html

Examples for sharing a spreadsheet with specific users or making it public.

```python
>>> spreadsheet.share('example@gmail.com', role='commenter', type='user', emailMessage='Here is the spreadsheet we talked about!')
```

```python
>>> spreadsheet.share('', role='reader', type='anyone')
```

--------------------------------

### Service Account JSON Structure

Source: https://pygsheets.readthedocs.io/en/stable/authorization.html

Example of the JSON file structure downloaded from the Google Cloud Console for service account authentication.

```json
{
    "type": "service_account",
    "project_id": "p....sdf",
    "private_key_id": "48.....",
    "private_key": "-----BEGIN PRIVATE KEY-----\nNrDyLw … jINQh/9\n-----END PRIVATE KEY-----\n",
    "client_email": "p.....@appspot.gserviceaccount.com",
    "client_id": "10.....454",
}
```

--------------------------------

### Interact with GridRange

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/address.html

Examples demonstrating how to define and manipulate rectangular ranges in a worksheet.

```python
    >>> grange = GridRange(worksheet=wks, start='A1', end='D4')
    >>> grange
    <GridRange Sheet1!A1:D4>
    >>> grange.start = 'A' # will remove bounding in rows
    <GridRange Sheet1!A:D>
    >>> grange.start = 'A1' # cannot add bounding at just start
    <GridRange Sheet1!A:D>
    >>> grange.indexes = ('A1', 'D4') # cannot add bounding at just start
    <GridRange Sheet1!A1:D4>
    >>> grange.end = (3, 5) # tuples will also work
    <GridRange Sheet1!A1:C5>
    >>> grange.end = (None, 5) # make unbounded on rows
    <GridRange Sheet1!1:5>
    >>> grange.end = (None, None) # make it unbounded on one index
    <GridRange Sheet1!1:1>
    >>> grange.start = None # make it unbounded on both indexes
    <GridRange Sheet1>
    >>> grange.start = 'A1' # make it unbounded on single index,now AZ100 is bottom right cell of worksheet
    <GridRange Sheet1:A1:AZ100>
    >>> 'A1' in grange
    True
    >>> (100,100) in grange
    False
    >>> for address in grange:
    >>>     print(address)
    Address((1,1))
    Address((1,2))
    ...
```

--------------------------------

### adjust_column_width(start, end=None, pixel_size=None)

Source: https://pygsheets.readthedocs.io/en/stable/worksheet.html

Set the width of one or more columns.

```APIDOC
## adjust_column_width(start, end=None, pixel_size=None)

### Description
Set the width of one or more columns.

### Parameters
- **start** (int) - Required - Index of the first column to be widened.
- **end** (int) - Optional - Index of the last column to be widened.
- **pixel_size** (int) - Optional - New width in pixels or None to set width automatically.
```

--------------------------------

### show_dimensions(start, end, dimension)

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/worksheet.html

Shows one or more rows or columns.

```APIDOC
## show_dimensions(start, end, dimension)

### Description
Shows the specified range of rows or columns.

### Parameters
- **start** (int) - Required - Index of the first row or column.
- **end** (int) - Optional - Index of the last row or column.
- **dimension** (str) - Optional - 'ROWS' or 'COLUMNS'.
```

--------------------------------

### get_values(start, end, ...)

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/worksheet.html

Returns a range of values from a start cell to an end cell.

```APIDOC
## get_values(start, end, returnas='matrix', majdim='ROWS', ...)

### Description
Returns a range of values from the start cell to the end cell, processed into the requested format.

### Parameters
- **start** (tuple or str) - Required - Top left position as tuple or label.
- **end** (tuple or str) - Required - Bottom right position as tuple or label.
- **returnas** (str) - Optional - The type to return: 'matrix', 'cell', or 'range'.
- **majdim** (str) - Optional - The major dimension of the matrix (default 'ROWS').
- **value_render** (ValueRenderOption) - Optional - How output values should be rendered.
```

--------------------------------

### Wrap Strategy Property

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/cell.html

Gets or sets the text wrapping strategy for a cell.

```python
@property
def wrap_strategy(self):
    """
    How to wrap text in this cell.
    Possible wrap strategies: 'OVERFLOW_CELL', 'LEGACY_WRAP', 'CLIP', 'WRAP'.
    `Reference: api docs <https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#wrapstrategy>`__
    """  
    return self._wrap_strategy

@wrap_strategy.setter
def wrap_strategy(self, wrap_strategy):
    self._wrap_strategy = wrap_strategy
    self.update()
```

--------------------------------

### Get Range

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/worksheet.html

Retrieves a range in A1 notation or as a JSON object.

```python
def _get_range(self, start_label, end_label=None, rformat='A1'):
    """get range in A1 notation, given start and end labels

    :param start_label: range start label
    :param end_label: range end label
    :param rformat: can be A1 or GridRange

    """
    grange = GridRange(worksheet=self, start=start_label, end=end_label)
    if rformat == "A1":
        return grange.label
    else:
        return grange.to_json()
```

--------------------------------

### hide_dimensions(start, end, dimension)

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/worksheet.html

Hides one or more rows or columns.

```APIDOC
## hide_dimensions(start, end, dimension)

### Description
Hides the specified range of rows or columns.

### Parameters
- **start** (int) - Required - Index of the first row or column.
- **end** (int) - Optional - Index of the last row or column.
- **dimension** (str) - Optional - 'ROWS' or 'COLUMNS'.
```

--------------------------------

### set_dataframe(df, start, copy_index=False, copy_head=True, extend=False, fit=False, escape_formulae=False, **kwargs)

Source: https://pygsheets.readthedocs.io/en/stable/worksheet.html

Loads data from a Pandas DataFrame into the worksheet.

```APIDOC
## set_dataframe(df, start, ...)

### Description
Load sheet from Pandas Dataframe. Will load all data contained within the Pandas data frame into this worksheet starting at the specified cell.

### Parameters
- **df** (DataFrame) - Required - Pandas data frame.
- **start** (string) - Required - Address of the top left corner where the data should be added.
- **copy_index** (bool) - Optional - Copy data frame index.
- **copy_head** (bool) - Optional - Copy header data into first row.
- **extend** (bool) - Optional - Add columns and rows to the worksheet if necessary.
- **fit** (bool) - Optional - Resize the worksheet to fit all data inside if necessary.
- **escape_formulae** (bool) - Optional - Prefix values starting with =/+ with an apostrophe.
- **nan** (string) - Optional - Value with which NaN values are replaced.
```

--------------------------------

### Delete Columns and Rows

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/worksheet.html

Methods to remove a specified number of columns or rows starting from a given index.

```python
@batchable
def delete_cols(self, index, number=1):
    """Delete 'number' of columns from index.

    :param index:   Index of first column to delete
    :param number:  Number of columns to delete

    """
    if not self._linked: return False

    index -= 1
    if number < 1:
        raise InvalidArgumentValue('number')
    request = {'deleteDimension': {'range': {'sheetId': self.id, 'dimension': 'COLUMNS',
                                             'endIndex': (index+number), 'startIndex': index}}}
    self.client.sheet.batch_update(self.spreadsheet.id, request)
    self.jsonSheet['properties']['gridProperties']['columnCount'] = self.cols-number
```

```python
@batchable
def delete_rows(self, index, number=1):
    """Delete 'number' of rows from index.

    :param index:   Index of first row to delete
    :param number:  Number of rows to delete
    """
    if not self._linked: return False

    index -= 1
    if number < 1:
        raise InvalidArgumentValue
    request = {'deleteDimension': {'range': {'sheetId': self.id, 'dimension': 'ROWS',
                                             'endIndex': (index+number), 'startIndex': index}}}
    self.client.sheet.batch_update(self.spreadsheet.id, request)
    self.jsonSheet['properties']['gridProperties']['rowCount'] = self.rows-number
```

--------------------------------

### Worksheet __getitem__ implementation

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/worksheet.html

Retrieves a row from the worksheet by index, starting at 1. Raises CellNotFound if the index is out of bounds.

```python
# @TODO optimize (use datagrid)
def __getitem__(self, item):
    if type(item) == int:
        if item < 1:
            raise CellNotFound("index start at 1")
        if item >= self.rows:
            raise CellNotFound
        try:
            row = self.get_row(item, include_tailing_empty=True)
        except IndexError:
            raise CellNotFound
        return row
```

--------------------------------

### Worksheet Property Accessors

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/worksheet.html

Examples of property getters and setters for managing worksheet attributes like title, index, and dimensions.

```python
    @property
    def id(self):
        """The ID of this worksheet."""
        return self.jsonSheet['properties']['sheetId']

    @property
    def index(self):
        """The index of this worksheet"""
        return self.jsonSheet['properties']['index']

    @index.setter
    def index(self, index):
        self.jsonSheet['properties']['index'] = index
        if self._linked:
            self.client.sheet.update_sheet_properties_request(self.spreadsheet.id, self.jsonSheet['properties'], 'index')

    @property
    def title(self):
        """The title of this worksheet."""
        return self.jsonSheet['properties']['title']

    @title.setter
    def title(self, title):
        self.jsonSheet['properties']['title'] = title
        if self._linked:
            self.client.sheet.update_sheet_properties_request(self.spreadsheet.id, self.jsonSheet['properties'], 'title')

    @property
    def hidden(self):
        """Mark the worksheet as hidden."""
        return self.jsonSheet['properties'].get('hidden', False)

    @hidden.setter
    def hidden(self, hidden):
        self.jsonSheet['properties']['hidden'] = hidden
        if self._linked:
            self.client.sheet.update_sheet_properties_request(self.spreadsheet.id, self.jsonSheet['properties'], 'hidden')

    @property
    def url(self):
        """The url of this worksheet."""
        return self.spreadsheet.url+"/edit#gid="+str(self.id)

    @property
    def rows(self):
        """Number of rows active within the sheet. A new sheet contains 1000 rows."""
        return int(self.jsonSheet['properties']['gridProperties']['rowCount'])

    @rows.setter
    def rows(self, row_count):
        if row_count == self.rows:
            return
        self.jsonSheet['properties']['gridProperties']['rowCount'] = int(row_count)
        if self._linked:
            self.client.sheet.update_sheet_properties_request(self.spreadsheet.id, self.jsonSheet['properties'],
                                                              'gridProperties/rowCount')

    @property
    def cols(self):
        """Number of columns active within the sheet."""
        return int(self.jsonSheet['properties']['gridProperties']['columnCount'])

    @cols.setter
    def cols(self, col_count):
        if col_count == self.cols:
            return
        self.jsonSheet['properties']['gridProperties']['columnCount'] = int(col_count)
        if self._linked:
            self.client.sheet.update_sheet_properties_request(self.spreadsheet.id, self.jsonSheet['properties'],
                                                              'gridProperties/columnCount')

    @property
    def frozen_rows(self):
        """Number of frozen rows."""
        return self.jsonSheet['properties']['gridProperties'].get('frozenRowCount', 0)
```

--------------------------------

### Get named ranges from a worksheet

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/worksheet.html

Retrieves all named ranges associated with the worksheet, or a specific one if a name is provided.

```python
def get_named_ranges(self, name=''):
    """Get named ranges from this worksheet.

    Reference: `Named range Api object <https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#namedrange>`_

    :param name:    Name of the named range to be retrieved, if omitted all ranges are retrieved.
    :return: :class:`DataRange`
    """
    if not self._linked: return False

    if name == '':
        self.spreadsheet.fetch_properties()
        nrange = [x for x in self.spreadsheet.named_ranges if x.worksheet.id == self.id]
        return nrange
    else:
        return self.get_named_range(name)
```

--------------------------------

### Get Worksheet as Pandas DataFrame

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/worksheet.html

Retrieves worksheet data and converts it into a pandas DataFrame, supporting custom headers and indexing.

```python
    def get_as_df(self, has_header=True, index_column=None, start=None, end=None, numerize=True,
                  empty_value='', value_render=ValueRenderOption.FORMATTED_VALUE, **kwargs):
        """
        Get the content of this worksheet as a pandas data frame.

        :param has_header:      Interpret first row as data frame header.
        :param index_column:     Column to use as data frame index (integer).
        :param numerize:        Numerize cell values.
        :param empty_value:     Placeholder value to represent empty cells when numerizing.
        :param start:           Top left cell to load into data frame. (default: A1)
        :param end:             Bottom right cell to load into data frame. (default: (rows, cols))
        :param value_render:    How the output values should returned, `api docs <https://developers.google.com/sheets/api/reference/rest/v4/ValueRenderOption>`__
                                By default, will convert everything to strings. Setting as UNFORMATTED_VALUE will do
                                numerizing, but values will be unformatted.
        :param include_tailing_empty: whether to include empty trailing cells/values after last non-zero value in a row
        :param include_tailing_empty_rows: whether to include tailing rows with no values; if include_tailing_empty is false,
                    will return unfilled list for each empty row, else will return rows filled with empty cells
        :returns: pandas.Dataframe
        """
        if not self._linked: return False

        include_tailing_empty = kwargs.get('include_tailing_empty', False)
        include_tailing_empty_rows = kwargs.get('include_tailing_empty_rows', False)
        index_column = index_column or kwargs.get('index_colum', None)

        if not pd:
            raise ImportError("pandas")
        if start is not None or end is not None:
            if end is None:
                end = (self.rows, self.cols)
            values = self.get_values(start, end, value_render=value_render,
                                     include_tailing_empty=include_tailing_empty,
                                     include_tailing_empty_rows=include_tailing_empty_rows)
        else:
            values = self.get_all_values(returnas='matrix', include_tailing_empty=include_tailing_empty,
                                         value_render=value_render, include_tailing_empty_rows=include_tailing_empty_rows)

        max_row = max(len(row) for row in values)
        values = [row + [empty_value] * (max_row - len(row)) for row in values]

        if numerize:
            values = [numericise_all(row, empty_value) for row in values]

        if has_header:
            keys = values[0]
            values = values[1:]
            if any(key == '' for key in keys):
                warnings.warn('At least one column name in the data frame is an empty string. If this is a concern, please specify include_tailing_empty=False and/or ensure that each column containing data has a name.')
            df = pd.DataFrame(values, columns=keys)
        else:
            df = pd.DataFrame(values)

        if index_column:
            if index_column < 1 or index_column > len(df.columns):
                raise ValueError("index_column %s not found" % index_column)
            else:
                df.index = df[df.columns[index_column - 1]]
                del df[df.columns[index_column - 1]]
        return df
```

--------------------------------

### Get a named range by name

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/worksheet.html

Retrieves a specific named range from the worksheet. Raises RangeNotFound if the name does not exist.

```python
def get_named_range(self, name):
    """Get a named range by name.

    Reference: `Named range Api object <https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#namedrange>`_

    :param name:    Name of the named range to be retrieved.
    :returns:   :class:`DataRange`

    :raises RangeNotFound: if no range matched the name given.
    """
    if not self._linked: return False

    nrange = [x for x in self.spreadsheet.named_ranges if x.name == name and x.worksheet.id == self.id]
    if len(nrange) == 0:
        self.spreadsheet.fetch_properties()
        nrange = [x for x in self.spreadsheet.named_ranges if x.name == name and x.worksheet.id == self.id]
        if len(nrange) == 0:
            raise RangeNotFound(name)
    return nrange[0]
```

--------------------------------

### Set Worksheet Data from DataFrame

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/worksheet.html

Loads a Pandas DataFrame into the worksheet starting at a specified address, with options for index and header handling.

```python
@batchable
    def set_dataframe(self, df, start, copy_index=False, copy_head=True, extend=False, fit=False, escape_formulae=False, **kwargs):
        """Load sheet from Pandas Dataframe.

        Will load all data contained within the Pandas data frame into this worksheet.
        It will begin filling the worksheet at cell start. Supports multi index and multi header
        datarames.

        :param df:              Pandas data frame.
        :param start:           Address of the top left corner where the data should be added.
        :param copy_index:      Copy data frame index (multi index supported).
        :param copy_head:       Copy header data into first row.
        :param extend:          Add columns and rows to the worksheet if necessary, but won't delete any rows or columns.
        :param fit:             Resize the worksheet to fit all data inside if necessary.
        :param escape_formulae: Any value starting with an equal or plus sign (=/+), will be prefixed with an apostrophe (') to
                                avoid value being interpreted as a formula.
        :param nan:             Value with which NaN values are replaced. by default it will be replaced with string 'nan'. for converting nan values to
                                empty cells set nan="".

        """

        if not self._linked:
            return False
        nan = kwargs.get('nan', "NaN")

        start = Address(start)
        for col in df.select_dtypes('Int64'):
            df[col] = df[col].astype('unicode').replace('<NA>', nan)
        df = df.fillna(nan)
        values = df.astype('unicode').values.tolist()
        (df_rows, df_cols) = df.shape
        num_indexes = 1
        index_column_names = None

        if copy_index:
            if isinstance(df.index, pd.MultiIndex):
                num_indexes = len(df.index[0])
                for i, indexes in enumerate(df.index):
                    indexes = map(str, indexes)
                    for index_item in reversed(list(indexes)):
                        values[i].insert(0, index_item)
                df_cols += num_indexes
                index_column_names = list(df.index.names) # creates the column names
            else:
                for i, val in enumerate(df.index.astype(str)):
                    values[i].insert(0, val)
                df_cols += num_indexes
                index_column_names = [df.index.name] # creates the column name

        if copy_head:
            # If multi index, copy indexes in each level to new row
            if isinstance(df.columns, pd.MultiIndex):
                head = [""]*num_indexes if copy_index else []  # skip index columns
                heads = [head[:] for x in df.columns[0]]
                for col_head in df.columns:
                    for i, col_item in enumerate(col_head):
                        heads[i].append(str(col_item))
                # adds in the index names to bottom header row if copy_index is also True
                if copy_index:
                    # to account for multi-index names we will replace all '' in our head list
                    # with the index_column_names
                    heads[-1][:num_indexes] = index_column_names
                values = heads + values
                df_rows += len(df.columns[0])
            else:
                head = [""]*num_indexes if copy_index else []  # skip index columns
                map(str, head)
                head.extend(df.columns.tolist())
                # if copy_index & copy_head, include the index names
                if copy_index:
                    # to account for multi-index names we will replace all '' in our head list
                    # with the index_column_names
                    head[:num_indexes] = index_column_names
                values.insert(0, head)
                df_rows += 1

        end = start + (df_rows, df_cols)

        if fit == extend is not False:
            raise InvalidArgumentValue("fit should not be same with extend")
```

--------------------------------

### sort_range(start, end, basecolumnindex=0, sortorder="ASCENDING")

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/worksheet.html

Sorts a specified range of data in the worksheet based on a column index.

```APIDOC
## sort_range(start, end, basecolumnindex=0, sortorder="ASCENDING")

### Description
Sorts the data in rows within the defined grid range based on the provided column index.

### Parameters
- **start** (string/tuple) - Required - Address of the starting cell of the grid.
- **end** (string/tuple) - Required - Address of the last cell of the grid.
- **basecolumnindex** (int) - Optional - Index of the column to sort by (default: 0).
- **sortorder** (string) - Optional - Sort order, either "ASCENDING" or "DESCENDING" (default: "ASCENDING").
```

--------------------------------

### Client Authorization Logic

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/authorization.html

Handles credential initialization by checking for deprecated arguments and selecting the appropriate authentication method based on provided inputs or environment variables.

```python
    for key in kwargs:
        if key in ['outh_file', 'outh_creds_store', 'service_file', 'credentials']:
            warnings.warn('The argument {} is deprecated. Use {} instead.'.format(key, _deprecated_keyword_mapping[key])
                          , category=DeprecationWarning)
    client_secret = kwargs.pop('outh_file', client_secret)
    service_account_file = kwargs.pop('service_file', service_account_file)
    credentials_directory = kwargs.pop('outh_creds_store', credentials_directory)
    custom_credentials = kwargs.pop('credentials', custom_credentials)

    if custom_credentials is not None:
        credentials = custom_credentials
    elif service_account_env_var is not None:
        service_account_info = json.loads(os.environ[service_account_env_var])
        credentials = service_account.Credentials.from_service_account_info(
            service_account_info, scopes=scopes)
    elif service_account_json is not None:
        service_account_info = json.loads(service_account_json)
        credentials = service_account.Credentials.from_service_account_info(
            service_account_info, scopes=scopes)
    elif service_account_file is not None:
        credentials = service_account.Credentials.from_service_account_file(service_account_file, scopes=scopes)
    else:
        credentials = _get_user_authentication_credentials(client_secret, scopes, credentials_directory, local)

    return Client(credentials, **kwargs)
```

--------------------------------

### Initialize DriveAPIWrapper

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/drive.html

Initializes the wrapper with an HTTP object and a path to the discovery document. It falls back to a standard discovery service if the local file is unavailable.

```python
def __init__(self, http, data_path, retries=3, logger=logging.getLogger(__name__)):

        try:
            with open(os.path.join(data_path, "drive_discovery.json")) as jd:
                self.service = discovery.build_from_document(json.load(jd), http=http)
        except:
            self.service = discovery.build('drive', 'v3', http=http, discoveryServiceUrl=DISCOVERY_SERVICE_URL)
        self.team_drive_id = None
        self.include_team_drive_items = True  # TODO Deprecated remove
        self.include_items_from_all_drive = True
        """Include files from TeamDrive and My Drive when executing requests."""
        self.logger = logger
        self.retries = retries
```

--------------------------------

### Client.create

Source: https://pygsheets.readthedocs.io/en/stable/reference.html

Creates a new Google Spreadsheet.

```APIDOC
## Client.create(title, template=None, folder=None, folder_name=None, **kwargs)

### Description
Creates a new spreadsheet. The title will be set to the provided value, and an optional template can be used.

### Parameters
- **title** (str) - Required - Title of the new spreadsheet.
- **template** (Spreadsheet/Resource) - Optional - A template to create the new spreadsheet from.
- **folder** (str) - Optional - The Id of the folder this sheet will be stored in.
- **folder_name** (str) - Optional - The Name of the folder this sheet will be stored in.
- **kwargs** (dict) - Optional - Standard parameters.

### Returns
- **Spreadsheet** (Object) - The created spreadsheet instance.
```

--------------------------------

### adjust_row_height(start, end=None, pixel_size=None)

Source: https://pygsheets.readthedocs.io/en/stable/worksheet.html

Adjust the height of one or more rows.

```APIDOC
## adjust_row_height(start, end=None, pixel_size=None)

### Description
Adjust the height of one or more rows.

### Parameters
- **start** (int) - Required - Index of first row to be heightened.
- **end** (int) - Optional - Index of last row to be heightened.
- **pixel_size** (int) - Optional - New height in pixels or None to set height automatically.
```

--------------------------------

### Authorize using a Service Account File

Source: https://pygsheets.readthedocs.io/en/stable/authorization.html

Initializes the pygsheets client using a path to the downloaded service account JSON credentials file.

```python
gc = pygsheets.authorize(service_file='path/to/service_account_credentials.json')
```

--------------------------------

### Authorize and Initialize Client

Source: https://pygsheets.readthedocs.io/en/stable/reference.html

Initializes the pygsheets client using the authorize method.

```python
>>> import pygsheets
>>> c = pygsheets.authorize()
```

--------------------------------

### Get developer metadata

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/sheet.html

Retrieves developer metadata for a spreadsheet by its ID.

```python
def developer_metadata_get(self, spreadsheet_id, metadata_id):
    """Returns a dictionary of developer metadata matching the supplied filter

    Reference: `request <https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.developerMetadata/get>`__

    :param spreadsheet_id:  The id of the spreadsheet to search.
    :param data_filter:     The id of the developer metadata item to get
    """
    request = self.service.spreadsheets().developerMetadata().get(spreadsheetId=spreadsheet_id,
                                                                  metadataId=metadata_id)
    return self._execute_requests(request)
```

--------------------------------

### Client.create

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/client.html

Creates a new Google Spreadsheet, optionally using a template or specifying a folder.

```APIDOC
## Method: create

### Description
Creates a new spreadsheet with the specified title. Can optionally copy from a template or place the file in a specific folder.

### Parameters
- **title** (str) - Required - The title for the new spreadsheet.
- **template** (str/Spreadsheet) - Optional - A template to base the new spreadsheet on.
- **folder** (str) - Optional - The ID of the folder to store the spreadsheet in.
- **folder_name** (str) - Optional - The name of the folder to store the spreadsheet in.

### Returns
- **Spreadsheet** - An instance of the created spreadsheet.
```

--------------------------------

### Cell Note Property

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/cell.html

Gets or sets the note associated with a cell.

```python
@property
def note(self):
    """Get/Set note of this cell."""
    if self._simplecell:
        self.fetch()
    return self._note

@note.setter
def note(self, note):
    if self._simplecell:
        self.fetch()
    self._note = note
    self.update()
```

--------------------------------

### create

Source: https://pygsheets.readthedocs.io/en/stable/sheet_api.html

Creates a new spreadsheet, optionally using a template.

```APIDOC
## create(title, template=None, **kwargs)

### Description
Create a spreadsheet. Can be created with just a title, or using a template (JSON representation of a Spreadsheet Resource or Spreadsheet class instance).

### Parameters
- **title** (str) - Required - Title of the new spreadsheet.
- **template** (object) - Optional - Template used to create the new spreadsheet.
- **kwargs** (dict) - Optional - Standard parameters.
```

--------------------------------

### create_folder(name, folder=None, **kwargs)

Source: https://pygsheets.readthedocs.io/en/stable/drive_api.html

Creates a new folder in the specified location.

```APIDOC
## create_folder(name, folder=None, **kwargs)

### Description
Create a new folder.

### Parameters
- **name** (string) - Required - The name to give the new folder.
- **folder** (string) - Optional - The id of the folder this one will be stored in.
- **kwargs** (dict) - Optional - Standard parameters.
```

--------------------------------

### Authorize using Environment Variables

Source: https://pygsheets.readthedocs.io/en/stable/authorization.html

Initializes the pygsheets client by referencing an environment variable that contains the service account JSON content.

```python
gc = pygsheets.authorize(service_account_env_var = 'GDRIVE_API_CREDENTIALS')
```

--------------------------------

### Vertical Alignment Property

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/cell.html

Gets or sets the vertical alignment of the value in a cell.

```python
@property
def vertical_alignment(self):
    """Vertical alignment of the value in this cell.
        possible vlaues: :class:`VerticalAlignment <pygsheets.custom_types.VerticalAlignment>` """
    self.update()
    return self._vertical_alignment

@vertical_alignment.setter
def vertical_alignment(self, value):
    if isinstance(value, VerticalAlignment):
        self._vertical_alignment = value
        self.update()
    else:
        raise InvalidArgumentValue('Use VerticalAlignment for setting the vertical alignment.')
```

--------------------------------

### Initialize and Modify DataRange

Source: https://pygsheets.readthedocs.io/en/stable/datarange.html

Demonstrates creating a DataRange object and modifying its properties such as name, protection status, and address boundaries.

```python
>>> drange = Datarange(start='A1', end='B2', worksheet=wks)
<Datarange Sheet1!A1:B2>
>>> drange.name = "my_named_range" # make this datarange a named range
<Datarange my_named_range Sheet1!A1:B2>
>>> drange.protected = True # make the range protected
<Datarange my_named_range Sheet1!A1:B2 protected>
>>> drange.start_addr = 'B' # make the range unbounded on rows
<Datarange my_named_range Sheet1!A:B protected>
>>> drange.end_addr = None # make the range unbounded on both axes
<Datarange my_named_range Sheet1 protected>
```

--------------------------------

### Create Developer Metadata

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/sheet.html

Creates a new developer metadata entry at a specified location with document-level visibility.

```python
def developer_metadata_create(self, spreadsheet_id, key, value, location):
        """Creates a new developer metadata entry at the specified location

        Reference: `request <https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#createdevelopermetadatarequest>`__

        :param spreadsheet_id:  The id of the spreadsheet where metadata will be created.
        :param key:             They key of the new developer metadata entry to create
        :param value:           They value of the new developer metadata entry to create
        :param location:        A dictionary represeting the location where metadata will be created
        """
        request = {
            "createDeveloperMetadata": {
                "developerMetadata": {
                    "metadataKey": key,
                    "metadataValue": value,
                    "location": location,
                    "visibility": "DOCUMENT"
                }
            }
        }
        response = self.batch_update(spreadsheet_id, [request])
        if response is None:
            # we're in batch mode
            return
        else:
            return response["replies"][0]["createDeveloperMetadata"]["developerMetadata"]["metadataId"]
```

--------------------------------

### update_dimensions_visibility(start, end=None, dimension='ROWS', hidden=True)

Source: https://pygsheets.readthedocs.io/en/stable/worksheet.html

Hide or show one or more rows or columns.

```APIDOC
## update_dimensions_visibility(start, end=None, dimension='ROWS', hidden=True)

### Description
Hide or show one or more rows or columns.

### Parameters
- **start** (int) - Required - Index of the first row or column.
- **end** (int) - Optional - Index of the last row or column.
- **dimension** (string) - Optional - 'ROWS' or 'COLUMNS'.
- **hidden** (bool) - Optional - Hide rows or columns.
```

--------------------------------

### list(**kwargs)

Source: https://pygsheets.readthedocs.io/en/stable/drive_api.html

Fetches metadata for files in the user's drive or Team Drive.

```APIDOC
## list(**kwargs)

### Description
Fetch metadata of spreadsheets. Fetches a list of all files present in the users drive or TeamDrive.

### Parameters
- **kwargs** (dict) - Optional - Standard parameters for the Google Drive API list request.
```

--------------------------------

### apply_format(ranges, format_info, fields='userEnteredFormat')

Source: https://pygsheets.readthedocs.io/en/stable/worksheet.html

Apply formatting for multiple ranges.

```APIDOC
## apply_format(ranges, format_info, fields='userEnteredFormat')

### Description
Apply formatting for multiple ranges.

### Parameters
- **ranges** (list) - Required - List of ranges to apply the formats to.
- **format_info** (list/cell/dict) - Required - Properties specifying the formats to be updated.
- **fields** (string) - Optional - Fields to be updated in the cell.
```

--------------------------------

### Cell Color Property

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/cell.html

Gets or sets the background color of a cell as a tuple (red, green, blue, alpha).

```python
@property
def color(self):
    """Get/Set background color of this cell as a tuple (red, green, blue, alpha)."""
    if self._simplecell:
        self.fetch()
    return self._color

@color.setter
def color(self, value):
    if self._simplecell:
        self.fetch()
    if type(value) is tuple:
        if len(value) < 4:
            value = list(value) + [1.0]*(4-len(value))
    else:
        value = (value, 1.0, 1.0, 1.0)
    for c in value:
        if c < 0 or c > 1:
            raise InvalidArgumentValue("Color should be in range 0-1")
    self._color = tuple(value)
    self.update()
```

--------------------------------

### Create file permission

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/drive.html

Creates a new permission for a specific file or Team Drive.

```python
    def create_permission(self, file_id, role, type, **kwargs):
        """Creates a permission for a file or a TeamDrive.

        See `reference <https://developers.google.com/drive/v3/reference/permissions/create>`__ for more details.

        :param file_id:                 The ID of the file or Team Drive.
        :param role:                    The role granted by this permission.
        :param type:                    The type of the grantee.
        :keyword emailAddress:          The email address of the user or group to which this permission refers.
        :keyword domain:                The domain to which this permission refers.
        :parameter allowFileDiscovery:  Whether the permission allows the file to be discovered through search. This is
                                        only applicable for permissions of type domain or anyone.
        :keyword expirationTime:        The time at which this permission will expire (RFC 3339 date-time). Expiration
                                        times have the following restrictions:
```

--------------------------------

### list_permissions(file_id, **kwargs)

Source: https://pygsheets.readthedocs.io/en/stable/drive_api.html

List all permissions for the specified file.

```APIDOC
## list_permissions(file_id, **kwargs)

### Description
List all permissions for the specified file.

### Parameters
- **file_id** (string) - Required - The file to get the permissions for.
- **pageSize** (integer) - Optional - Number of permissions returned per request.
- **supportsTeamDrives** (boolean) - Optional - Whether the application supports TeamDrives.
- **useDomainAdminAccess** (boolean) - Optional - Request permissions as domain admin.

### Returns
- **List of Permission Resources**
```

--------------------------------

### Initialize GridRange

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/address.html

Constructor for the GridRange class, accepting various parameters to define the range boundaries.

```python
    def __init__(self, label=None, worksheet=None, start=None, end=None, worksheet_title=None,
                 worksheet_id=None, propertiesjson=None):
        """
        :param label: label in A1 format
        :param worksheet: worksheet object this grange belongs to
        :param start: start address of the range
        :param end: end address of the range
        :param worksheet_title: worksheet title if worksheet not given
        :param worksheet_id: worksheet id if worksheet not given
        :param propertiesjson: json with all range properties
        """
        self._worksheet_title = worksheet_title
        self._worksheet_id = worksheet_id
        self._worksheet = worksheet
        self._start = Address(start, True)
        self._end = Address(end, True)
        # if fill_bounds and self._start and not self._end:
        #     if not worksheet:
        #         raise InvalidArgumentValue('worksheet need to fill bounds.')
        #     self._end = Address((worksheet.rows, worksheet.cols), True)
        # if fill_bounds and self._end and not self._start:
        #     self._start = Address('A1', True)
        if propertiesjson:
            self.set_json(propertiesjson)
        elif label:
            self._calculate_addresses(label)
        else:
            self._apply_index_constraints()
            self._calculate_label()
```

--------------------------------

### open_all(query='')

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/client.html

Opens all available spreadsheets, optionally filtered by a query.

```APIDOC
## open_all(query='')

### Description
Opens all available spreadsheets. Result can be filtered when specifying the query parameter.

### Parameters
- **query** (string) - Optional - Used to filter the returned metadata.
```

--------------------------------

### Access Range Coordinates and Metadata

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/datarange.html

Properties to retrieve or modify the start and end addresses, range string, and linked worksheet.

```python
@property
def start_addr(self):
    """top-left address of the range"""
    return self.grid_range.start.index

@start_addr.setter
def start_addr(self, addr):
    self.grid_range.start = addr
    self.update_named_range()
    self.update_protected_range()

@property
def end_addr(self):
    """bottom-right address of the range"""
    return self.grid_range.end.index

@end_addr.setter
def end_addr(self, addr):
    self.grid_range.end = addr
    self.update_named_range()
    self.update_protected_range()

@property
def range(self):
    """Range in format A1:C5"""
    return self.grid_range.start.label + ':' + self.grid_range.end.label

@property
def worksheet(self):
    """ linked worksheet """
    return self._worksheet

@property
def cells(self):
    """Get cells of this range"""
    if len(self._data[0]) == 0:
        self.fetch()
    return self._data
```

--------------------------------

### Share a numpy array with a friend

Source: https://pygsheets.readthedocs.io/en/stable/index.html

Demonstrates authorizing a client, opening a spreadsheet, updating cell values, and managing sharing permissions.

```python
import pygsheets

client = pygsheets.authorize()

# Open the spreadsheet and the first sheet.
sh = client.open('spreadsheet-title')
wks = sh.sheet1

# Update a single cell.
wks.update_value('A1', "Numbers on Stuff")

# Update the worksheet with the numpy array values. Beginning at cell 'A2'.
wks.update_values('A2', my_numpy_array.to_list())

# Share the sheet with your friend. (read access only)
sh.share('friend@gmail.com')
# sharing with write access
sh.share('friend@gmail.com', role='writer')
```

--------------------------------

### Address(value, allow_non_single=False)

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/address.html

Initializes a new Address object. The value can be an A1 notation string, a tuple of (row, col) integers, or another Address object.

```APIDOC
## Address(value, allow_non_single=False)

### Description
Represents the address of a cell. Supports A1 notation or coordinate tuples (row, col). Integer indexes start from 1.

### Parameters
- **value** (str | tuple | Address) - Required - The address in A1 format (e.g., 'A2') or coordinate tuple (e.g., (1, 4)).
- **allow_non_single** (bool) - Optional - If True, allows unbounded addresses (e.g., 'A' or '2'). Defaults to False.
```

--------------------------------

### Open a Spreadsheet by Title

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/client.html

Opens the first spreadsheet found with the specified title. Raises SpreadsheetNotFound if no match exists.

```python
import pygsheets
c = pygsheets.authorize()
c.open('TestSheet')
```

--------------------------------

### Batch get spreadsheet values

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/sheet.html

Retrieves values from multiple ranges. Handles conversion of ValueRenderOption and DateTimeRenderOption enums to their string values.

```python
        if isinstance(value_render_option, ValueRenderOption):
            value_render_option = value_render_option.value

        if isinstance(date_time_render_option, DateTimeRenderOption):
            date_time_render_option = date_time_render_option.value

        request = self.service.spreadsheets().values().batchGet(spreadsheetId=spreadsheet_id,
                                                                ranges=value_ranges,
                                                                majorDimension=major_dimension,
                                                                valueRenderOption=value_render_option,
                                                                dateTimeRenderOption=date_time_render_option)
        response = self._execute_requests(request)
        return response.get('valueRanges', [])
```

--------------------------------

### Open a Spreadsheet by URL

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/client.html

Opens a spreadsheet using its full browser URL. Raises SpreadsheetNotFound if the URL is invalid or no key is found.

```python
import pygsheets
c = pygsheets.authorize()
c.open_by_url('https://docs.google.com/spreadsheet/ccc?key=0Bm...FE&hl')
```

--------------------------------

### Get Bounded Indexes

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/address.html

Calculates the absolute row and column boundaries for a range, filling in unbounded values based on worksheet dimensions.

```python
def get_bounded_indexes(self):
    """ get bounded indexes of this range based on worksheet size, if the indexes are unbounded """
    start_r, start_c = tuple(iter(self.start)) if self.start else (None, None)
    end_r, end_c = tuple(iter(self.end)) if self.end else (None, None)
    start_r = start_r if start_r else 1
    start_c = start_c if start_c else 1
    if not self._worksheet and not (end_r or end_c):
        raise InvalidArgumentValue('Worksheet not set for calculating size.')
    end_r = end_r if end_r else self._worksheet.rows
    end_c = end_c if end_c else self._worksheet.cols
    return Address((start_r, start_c)), Address((end_r, end_c))
```

--------------------------------

### List file permissions

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/drive.html

Retrieves all permissions for a specified file, ensuring all fields are returned by default.

```python
def list_permissions(self, file_id, **kwargs):
        """List all permissions for the specified file.

        See `reference <https://developers.google.com/drive/v3/reference/permissions/list>`__  for more details.

        :param file_id:                     The file to get the permissions for.
        :keyword pageSize:                  Number of permissions returned per request. (Default: all)
        :keyword supportsTeamDrives:        Whether the application supports TeamDrives. (Default: False)
        :keyword useDomainAdminAccess:      Request permissions as domain admin. (Default: False)
        :return: List of `Permission Resources <https://developers.google.com/drive/v3/reference/permissions#resource>`_
        """
        kwargs['supportsAllDrives'] = self.is_team_drive()

        # Ensure that all fields are returned. Default is only id, type & role.
        if 'fields' not in kwargs:
            kwargs['fields'] = '*'

        permissions = list()
        response = self._execute_request(self.service.permissions().list(fileId=file_id, **kwargs))
        permissions.extend(response['permissions'])
        while 'nextPageToken' in response:
            response = self._execute_request(self.service.permissions().list(fileId=file_id,
                                                                             pageToken=response['nextPageToken'],
                                                                             **kwargs))
            permissions.extend(response['permissions'])
        return permissions
```

--------------------------------

### Fetch and Format Data

Source: https://pygsheets.readthedocs.io/en/stable/_modules/pygsheets/datarange.html

Methods to retrieve data from the cloud and apply formatting to cells.

```python
[docs]    def fetch(self, only_data=True):
        """
        update the range data/properties from cloud

        .. warning::
                Currently only data is fetched not properties, so `only_data` wont work

        :param only_data: fetch only data

        """
        self._data = self._worksheet.get_values(grange=self.grid_range, returnas='cells',
                                                include_tailing_empty_rows=True, include_tailing_empty=True)
        if not only_data:
            logging.error("functionality not implimented")


[docs]    def apply_format(self, cell=None, fields=None, cell_json=None):
        """
        Change format of all cells in the range
```

--------------------------------

### enable_team_drive(team_drive_id)

Source: https://pygsheets.readthedocs.io/en/stable/drive_api.html

Configures the wrapper to access a specific Team Drive instead of the user's personal drive.

```APIDOC
## enable_team_drive(team_drive_id)

### Description
Access TeamDrive instead of the users personal drive.

### Parameters
- **team_drive_id** (string) - Required - The ID of the Team Drive to access.
```
Tokens:
Raw