Sheet Handler

googleSheetsLib.core.Sheet

Interface that deals with Sheets at the tab level via API.

This shouldn't be instanced directly, and instead it's expected to be created via the Spreadsheet class using the get methods.

The interface with the service and ClientWrapper are derived from it's parent Spreadsheet object.

For more information on the API, visit: https://developers.google.com/workspace/sheets/api/quickstart/python

Attributes:

Name	Type	Description
name	str	The tab name, the same as in Google Sheets.
id	int	Numeric id that uniquely identifies the Sheet in a Spreadsheet.
client	ClientWrapper	Handler for API requests, authentication, and retry logic. References parent Spreadsheet.
service	SpreadsheetResource	The authenticated Google Sheets API resource. References parent spreadsheet.
row_count	int	Count of rows in the tab. Only updated after a metadata refresh.
column_count	int	Count of columns in the tab. Only updated after a metadata refresh.
parent_spreadsheet	Spreadsheet	Spreadsheet object that originated this Sheet.

Parameters:

Name	Type	Description	Default
name	str	Tab name.	required
id	int	Tab id.	required
parent_spreadsheet	Spreadsheet	Parent Spreadsheet,	required
client	ClientWrapper	Client interface to handle requests	required
service	SpreadsheetsResource	Google Sheets API resource to create requests.	required
row_count	int	Number of rows.	0
column_count	int	Number of columns.	0

Notes

Do not instantiate this directly. References parent Spreadsheet while it exists, but not all requests need to go through the parent Spreadsheet object.

Example

# Instantiating Sheet object
tab = ss['Tab Name']

# Accessing values from the tab
values = tab['A1:G22']

# Updating a range in the tab
values = [[1,2],
          [3,4]]
tab.update(rng = 'C3:D4', values = values)

# Also works
tab['C3:D4'] = values

Source code in src/googleSheetsLib/core.py

class Sheet:
    """
    Interface that deals with Sheets at the tab level via API.

    This shouldn't be instanced directly, and instead it's expected to be created
    via the Spreadsheet class using the get methods.

    The interface with the service and ClientWrapper are derived from it's parent Spreadsheet object.

    For more information on the API, visit:
    https://developers.google.com/workspace/sheets/api/quickstart/python

    Attributes:
        name (str): The tab name, the same as in Google Sheets.
        id (int): Numeric id that uniquely identifies the Sheet in a Spreadsheet.
        client (ClientWrapper): Handler for API requests, authentication, and retry logic. References parent Spreadsheet.
        service (SpreadsheetResource): The authenticated Google Sheets API resource. References parent spreadsheet.
        row_count (int): Count of rows in the tab. Only updated after a metadata refresh.
        column_count (int): Count of columns in the tab. Only updated after a metadata refresh.
        parent_spreadsheet (Spreadsheet): Spreadsheet object that originated this Sheet.

    Args:
        name (str): Tab name.
        id (int): Tab id.
        parent_spreadsheet (Spreadsheet): Parent Spreadsheet,
        client (ClientWrapper): Client interface to handle requests 
        service (SpreadsheetsResource): Google Sheets API resource to create requests.
        row_count (int, optional): Number of rows.
        column_count (int, optional): Number of columns.

    Notes:
        Do not instantiate this directly.
        References parent Spreadsheet while it exists, but not all requests need to go
        through the parent Spreadsheet object.


    Example:
        ```python
        # Instantiating Sheet object
        tab = ss['Tab Name']

        # Accessing values from the tab
        values = tab['A1:G22']

        # Updating a range in the tab
        values = [[1,2],
                  [3,4]]
        tab.update(rng = 'C3:D4', values = values)

        # Also works
        tab['C3:D4'] = values
        ```
    """
    def __init__(self,
                 name:str,
                 id:int,
                 parent_spreadsheet: Spreadsheet,
                 client:ClientWrapper,
                 service,
                 row_count:int = 0,
                 column_count:int = 0):

        self.name = name
        self.id = id
        self.spreadsheet_id = parent_spreadsheet.spreadsheet_id
        self.service = service
        self.client = client
        self.row_count = row_count
        self.column_count = column_count
        self.parent_spreadsheet = parent_spreadsheet

    def get_values(self, rng:str = '') -> Response:
        """
        Method to access the sheet's values. If range is not specified, returns the whole content if the sheet.
        Returns a Response object with the values.
        Can also by called by subscript notation, e.g. tab['A1:C2']

        Args: 
            rng(str, optional): Range in the Excel Format. E.g `A1:Q22`, `A:Q`, `C32`.
                If not specified, the values for the whole tab will be returned. 

        Returns:
            Response: Response object with the sheet's data, if succeded, or error information, if failed.
                The data is accessed by the Response.data attribute, and it's expected to be a list of lists (list of rows),
                or a single value if only a single cell was requested.
                If the range is not a valid xrange, it returns a Response with a 'Invalid Range' error.
                All other errors are repassed as is via the Response.error object.

        Notes:
            If only a single cell is specified, the Response.data is a singular value.
            All other times, it contains a list of lists or None.

        Example:
            ```python
            # Requesting a range
            response = tab.get_values('A2:C3') # Response.data = [[1,2,3],[4,5,6]]

            # Requesting a row range using subscript:
            response = tab['2:10'] # Response.data = [row2, row3, row4 ... ]

            # Requesting a singular cell:
            response = tab['C2'] # Response.data = 3

            # Handling errors:
            response = tab.get_values('A33sd:221AB2') # Invalid range
            if response.error:
                print(response.error.message) # 'Invalid x range: A33sd:221AB2'
            ```
        """

        details = self._get_dets(locals())
        function_name = 'Sheet.get_values'

        # Validando e formatando range
        if rng:
            is_valid_range = validate_xrange(rng)
            if not is_valid_range:
                print(f'Invalid range: {rng}.')
                return Response.fail(f'Invalid x Range: {rng}.', function_name=function_name, details = details)
            if '!' in rng:
                rng = rng.split('!')[-1]
            request_range = f'{self.name}!{rng}'
        else:
            request_range = f'{self.name}'

        # Criando requisição
        try:
            request = self.service.values().get( 
                spreadsheetId = self.spreadsheet_id,
                range = request_range
            )
        except Exception as e:
            return Response.fail(f'Error while building request: {e}', function_name=function_name, details=details)

        # Fazendo requisição
        response = self.client.execute(request)

        # Resolvendo resposta da requisição
        if response.ok:
            details['range'] = request_range
            if response.data:
                response.data = response.data.get('values')
                if is_cell(rng) and isinstance(response.data, list) and len(response.data[0]) == 1:
                    response.data = response.data[0][0]

            response.details = details

        else:
            if response.error:
                response.error.details = details
                response.error.function_name = function_name

        return response

    def append_values(self, values: list[list], 
                      rng:str = '',
                      input_option: InputOption = 'USER_ENTERED',
                      insert_data_option: InsertDataOption = 'INSERT_ROWS') -> Response:
        """
        This method inserts new data into the Spreadsheet's tab, starting at the specified range.

        The values to be inserted can be larger than the specified range; the range just delimits
        where the append starts.

        Values also need to be formated as a list of lists, a 2D matrix where each value is stored
        in an indexed `values[i][j]`.

        Args:
            values (list[list]): Values to be appended. Needed to be formated as a list of rows,
                i.e. a 2D matrix. Try to keep the values to str and int types, as other object types tend
                to trigger a bad request error.
            rng (str): Range to start the append. Formated in Excel range (e.g. 'A1:B2'), can be a single cell
                in which the API will append the whole set of values.
            input_option (InputOption, optional): Input mode, defaulted to USER_ENTERED.
            insert_data_option (InsertDataOption, optional): How to append, either by inserting new rows, or
                by overwritting blank cells.

        Returns:
            Response: response object with the status of the request. Response.data defaults to None.
                Returns a failed response if: the value list is empty; the range is invalid; selected
                an invalid input or insert option; failed to build request; or request sent an error
                response.

        Notes:
            The most common type of error here is badly formated value list. This means inputing something that is not
            a list of lists, or inserting object types that are not supported. 

            A quick way to fix types is valling `values = [[str(val) for val in row] for row in values]`, 
            which converts every value to str.

        Examples:
            ```python
            # appending values to a tab
            values = [[1,2,3],
                      [4,5,6]]
            tab.append_values(rng = 'A1', values = values) # Will try to append the values to the first cell

            # handling errors
            invalid_values = []
            response = tab.append_values(rng = 'C2:D4', values = invalid_values)
            print(response.error) # No values to insert.
            ```
        """

        # Registrando dados para validação depois.
        details = self._get_dets(locals())
        function_name = 'Sheet.append_values'

        # Validando valores:
        if not values:
            return Response.fail(f'No values to insert.', function_name=function_name, details = details)

        # Validando e formatando range.
        if rng:
            is_valid_range = validate_xrange(rng)
            if not is_valid_range:
                print(f'Invalid range: {rng}.')
                return Response.fail(f'Invalid x Range: {rng}.', function_name=function_name, details = details)
            if '!' in rng:
                rng = rng.split('!')[-1]
            request_range = f'{self.name}!{rng}'
        else:
            request_range = f'{self.name}'

        # Validando as outras opções:
        if input_option not in get_args(InputOption):
            error_msg = f'Arg Error: Invalid input option: {input_option}.'
            print(error_msg)
            return Response.fail(error_msg, function_name=function_name, details=details)
        if insert_data_option not in get_args(InsertDataOption):
            error_msg = f'Arg Error: Invalid insert data option: {insert_data_option}.'
            print(error_msg)
            return Response.fail(error_msg, function_name=function_name, details=details)

        # Preparando requisição
        body = {'values':values}

        try:
            request = self.service.values().append( 
                spreadsheetId = self.spreadsheet_id,
                range = request_range,
                valueInputOption = input_option,
                insertDataOption = insert_data_option,
                body = body
            )
        except Exception as e:
            return Response.fail(f'Error while building request: {e}', function_name=function_name, details=details)

        response = self.client.execute(request)

        if response.ok:
            result = response.data
            if result:
                details['range'] = request_range
                details['table_range'] = result.get('tableRange')
                if 'updates' in result:
                    details['updated_range'] = result['updates'].get('updatedRange')
            response.data = None
            response.details = details
            return response
        else:
            if response.error:
                response.error.details = details
                response.error.function_name = function_name
            return response

    def clear_cells(self, rng:str = '') -> Response:
        """
        Method to clear cells in a tab of the Spreadsheet. Will only empty the value of the cell,
        otherwise keeping the format and other properties.

        Args:
            rng (str): range to clear, in Excel format (e.g. 'A1:G3', '1:12'). If left empty,
                whole tab will be cleared, so be careful.

        Returns:
            Response: Response object with the status of the request. Returns an failed response if the
                rng is invalid, if it failed to build the request, or if the API call returned
                an error.

        Example:
            ```python
            # clearing a few cells:
            tab.clear_cells('A1:D9')

            # clearing the whole tab
            tab.clear_cells()
            ```
        """
        details = self._get_dets(locals())
        function_name = 'Sheet.append_values'

        if rng:
            is_valid_range = validate_xrange(rng)
            if not is_valid_range:
                print(f'Invalid range: {rng}.')
                return Response.fail(f'Invalid x Range: {rng}.', function_name=function_name, details = details)
            if '!' in rng:
                rng = rng.split('!')[-1]
            request_range = f'{self.name}!{rng}'
        else:
            request_range = f'{self.name}'

        # Construíndo a request:
        try:
            request = self.service.values().clear( 
                spreadsheetId = self.spreadsheet_id,
                range = request_range
            )
        except Exception as e:
            return Response.fail(f'Error while building request: {e}', function_name=function_name, details=details)

        response = self.client.execute(request)

        if response.ok:
            result = response.data
            if result:
                details['range'] = request_range
                details['cleared_range'] = result.get('clearedRange')
            response.data = None
            response.details = details
            return response
        else:
            if response.error:
                response.error.details = details
                response.error.function_name = function_name
            return response

    def update(self,
               values:list[list],
               rng:str = 'A1', 
               value_input_option:InputOption = 'USER_ENTERED',
               major_dimension:MajorDimension = 'ROWS'):
        "Função que atualiza células da planilha."

        details = self._get_dets(locals())
        function_name = 'Sheet.update'

        if rng:
            is_valid_range = validate_xrange(rng)
            if not is_valid_range:
                print(f'Invalid range: {rng}.')
                return Response.fail(f'Invalid x Range: {rng}.', function_name=function_name, details = details)
            if '!' in rng:
                rng = rng.split('!')[-1]
            if ':' not in rng:
                rng = get_values_delta(rng, values)
            request_range = f'{self.name}!{rng}'
        else:
            return Response.fail(f'No range specified.', function_name=function_name, details = details)

        # Validando parâmetros adicionais:
        if major_dimension not in get_args(MajorDimension):
            error_msg = f'Args Error: Invalid major dimension {major_dimension}'
            return Response.fail(error_msg, function_name = function_name, details = details)
        if value_input_option not in get_args(InputOption):
            error_msg = f'Args Error: Invalid input option {value_input_option}'
            return Response.fail(error_msg, function_name = function_name, details = details)

        body = {
            'values' : values,
            'majorDimension' : major_dimension
        }

        try:
            request = self.service.values().update(
                range = request_range,
                spreadsheetId = self.spreadsheet_id,
                body = body, 
                valueInputOption = value_input_option
            )
        except Exception as e:
            return Response.fail(f'Error while building request: {e}', function_name=function_name, details=details)

        response = self.client.execute(request)

        if response.ok:
            result = response.data
            if result:
                details['range'] = request_range
                details['updated_range'] = result.get('updatedRange')
                details['updated_cells'] = result.get('updatedCells')
                details['updated_rows'] = result.get('updatedRows')
                details['updated_columns'] = result.get('updatedColumns')
            response.data = None
            response.details = details
            return response
        else:
            if response.error:
                response.error.details = details
                response.error.function_name = function_name
            return response

    def update_cell(self, cell:str, value):

        details = self._get_dets(locals())
        function_name = 'Sheet.update_cell'

        if not is_cell(cell):
            print(f'Invalid cell format: {cell}')
            return Response.fail(f'Invalid cell format: {cell}', function_name=function_name, details=details)


        response = self.update(rng = cell, values = [[value]])

        if response.ok:
            updated_range = response.details.get('updatedRange') # type: ignore
            response.details = details
            response.details['updated_range'] = updated_range
            response.details['cell'] = cell
        elif response.error:
            response.error.details = details
            response.error.function_name = function_name
        return response

    def autofill_drag(self, source_range:str, drag_distance:int, prepare = False, dimension:MajorDimension = 'ROWS'):

        details = self._get_dets(locals())
        function_name = 'Sheet.autofill_drag'

        if drag_distance < 0:
            return Response.fail(f'Invalid drag distance: {drag_distance}.', function_name=function_name, details = details)

        if dimension not in get_args(MajorDimension):
            return Response.fail(f'Invalid dimension: {dimension}.', function_name=function_name, details = details)

        if source_range:
            if '!' in source_range:
                source_range = source_range.split('!')[-1]
            grid_range = xrange_to_grid_range(source_range)
            if not grid_range:
                return Response.fail(f'Invalid range.', function_name=function_name, details = details)
        else:
            return Response.fail(f'No range specified.', function_name=function_name, details = details)


        grid_range['sheetId'] = self.id

        autofill_request = {
            'autoFill':{
                'sourceAndDestination': {
                    'source' : grid_range,
                    'dimension' : dimension,
                    'fillLength' : drag_distance
                }
            }
        }

        details['autofill_request'] = autofill_request

        if prepare:
            details['prepared'] = True
            self.parent_spreadsheet.batch_requests.append(autofill_request)
            return Response.success(details = details)

        try:
            body: BatchUpdateSpreadsheetRequest = {'requests' : [autofill_request]} # type: ignore
            request = self.service.batchUpdate(
                spreadsheetId=self.spreadsheet_id,
                body = body
            )
        except Exception as e:
            return Response.fail(f'Error while building request: {e}', function_name=function_name, details=details)

        response = self.client.execute(request)

        if response.ok:
            response.details = details
            response.data = None
            return response
        elif response.error:
            response.error.details = details
            response.error.function_name = function_name
        return response

    def delete_rows(self, rng:str = '', start_row:int =-1, end_row:int=-1, prepare = False):
        "Função que deleta linhas da planilha. Pode receber tanto range, quanto pode receber start_row e end_row (base 1 inclusivo)"

        details = self._get_dets(locals())
        function_name = 'Sheet.delete_rows'

        if rng:
            if '!' in rng:
                rng = rng.split('!')[-1]
            grid_range = xrange_to_grid_range(rng)
            if not grid_range:
                return Response.fail(f'Invalid range: {rng}', function_name=function_name, details=details)
            elif grid_range['startRowIndex'] < 0 or grid_range['endRowIndex'] < 1:
                return Response.fail(f'Invalid range: {rng}', function_name=function_name, details=details)
            else:
                start_index = grid_range['startRowIndex']
                end_index = grid_range['endRowIndex']
        if not rng:
            if end_row == -1 and start_row == -1:
                return Response.fail(f'Missing arguments: range or start_row and end_row', function_name=function_name, details=details)
            if (end_row < 1 or start_row < 1) or end_row < start_row:
                return Response.fail(f'Invalid row ranges: {(start_row, end_row)}', function_name=function_name, details=details)
            start_index = start_row - 1 # Offset de passar de base 1 pra base 0
            end_index = end_row         # Mantém, porque o índice é exclusivo

        delete_request = {
            'deleteDimension' : {
                'range': {
                    'sheetId' : self.id,
                    'dimension' : 'ROWS',
                    'startIndex' : start_index,
                    'endIndex' : end_index
                }
            }
        }

        details['delete_request'] = delete_request # Telemetria

        if prepare:
            details['prepared'] = True
            self.parent_spreadsheet.batch_requests.append(delete_request)
            return Response.success(details = details)    


        try:
            body: BatchUpdateSpreadsheetRequest = {'requests' : [delete_request]} # type: ignore
            request = self.service.batchUpdate(
                spreadsheetId=self.spreadsheet_id,
                body = body
            )
        except Exception as e:
            return Response.fail(f'Error while building request: {e}', function_name=function_name, details=details)

        response = self.client.execute(request)

        if response.ok:
            response.details = details
            response.data = None
        elif response.error:
            response.error.details = details
            response.error.function_name = function_name
        return response

    def refresh_metadata(self):
        self.parent_spreadsheet.refresh_metadata()
        metadata = dict()
        for sheet_info in self.parent_spreadsheet.sheets_info.values():
            if sheet_info['sheet_id'] == self.id or sheet_info['title'] == self.name:
                metadata = sheet_info
                break
        if metadata:
            self.name = metadata['title']
            self.id = metadata['sheet_id']
            self.row_count = metadata['row_count']
            self.column_count = metadata['column_count']
        else:
            print(f'Não foi possível localizar metadados da aba {self.name}. Possivelmente foi deletada.')

    def __str__(self):
        return f'Sheet Object "{self.name}"; Id = {self.id}; Parent Spreadsheet = {self.parent_spreadsheet.name}; Rows = {self.row_count}; Columns = {self.column_count}'

    def get_info(self) -> dict:
        return {
            'title': self.name,
            'sheet_id' : self.id,
            'row_count' : self.row_count,
            'column_count' : self.column_count,
            'parent_spreadsheet': self.parent_spreadsheet.name
        }

    def _get_dets(self, locals:dict) -> dict:
        deets = locals.copy()
        if 'self' in deets:
            del deets['self']
        deets['sheet_info'] = self.get_info()
        return deets

    def __getitem__(self, rng) -> Response:
        result = self.get_values(rng)
        if result.data:
            return result.data
        else:
            return result

    def __setitem__(self, rng, new_value) -> Response:
        if is_cell(rng) and not isinstance(new_value, list):
            return self.update_cell(cell = rng, value = new_value)
        else:
            return self.update(rng = rng, values = new_value)

    def to_csv(self, fp, rng = '', sep = ','):
        "Função que pega os dados de uma planilha e salva em formato CSV."
        values = self.get_values(rng)
        if values.data:
            pd.DataFrame(values.data).to_csv(fp, index = False, sep = sep, header = False)

    def to_df(self, rng = '', headers = [], dtype = None):
        values = self.get_values(rng).data
        try:
            if values:
                if not headers and len(values)>1:
                    headers = values[0]
                    values = values[1:]
                if not headers:
                    headers = [n for n in range(len(values[0]))]
                return pd.DataFrame(values, columns = headers, dtype = dtype)
            else:
                return pd.DataFrame()
        except Exception as e:
            print(f"Not possible to create dataframe: {e}.\nReturning empty one instead.")
            return pd.DataFrame()

append_values(values, rng='', input_option='USER_ENTERED', insert_data_option='INSERT_ROWS')

This method inserts new data into the Spreadsheet's tab, starting at the specified range.

The values to be inserted can be larger than the specified range; the range just delimits where the append starts.

Values also need to be formated as a list of lists, a 2D matrix where each value is stored in an indexed values[i][j].

Parameters:

Name	Type	Description	Default
values	list[list]	Values to be appended. Needed to be formated as a list of rows, i.e. a 2D matrix. Try to keep the values to str and int types, as other object types tend to trigger a bad request error.	required
rng	str	Range to start the append. Formated in Excel range (e.g. 'A1:B2'), can be a single cell in which the API will append the whole set of values.	''
input_option	InputOption	Input mode, defaulted to USER_ENTERED.	'USER_ENTERED'
insert_data_option	InsertDataOption	How to append, either by inserting new rows, or by overwritting blank cells.	'INSERT_ROWS'

Returns:

Name	Type	Description
Response	Response	response object with the status of the request. Response.data defaults to None. Returns a failed response if: the value list is empty; the range is invalid; selected an invalid input or insert option; failed to build request; or request sent an error response.

Notes

The most common type of error here is badly formated value list. This means inputing something that is not a list of lists, or inserting object types that are not supported.

A quick way to fix types is valling values = [[str(val) for val in row] for row in values], which converts every value to str.

Examples:

# appending values to a tab
values = [[1,2,3],
          [4,5,6]]
tab.append_values(rng = 'A1', values = values) # Will try to append the values to the first cell

# handling errors
invalid_values = []
response = tab.append_values(rng = 'C2:D4', values = invalid_values)
print(response.error) # No values to insert.

Source code in src/googleSheetsLib/core.py

def append_values(self, values: list[list], 
                  rng:str = '',
                  input_option: InputOption = 'USER_ENTERED',
                  insert_data_option: InsertDataOption = 'INSERT_ROWS') -> Response:
    """
    This method inserts new data into the Spreadsheet's tab, starting at the specified range.

    The values to be inserted can be larger than the specified range; the range just delimits
    where the append starts.

    Values also need to be formated as a list of lists, a 2D matrix where each value is stored
    in an indexed `values[i][j]`.

    Args:
        values (list[list]): Values to be appended. Needed to be formated as a list of rows,
            i.e. a 2D matrix. Try to keep the values to str and int types, as other object types tend
            to trigger a bad request error.
        rng (str): Range to start the append. Formated in Excel range (e.g. 'A1:B2'), can be a single cell
            in which the API will append the whole set of values.
        input_option (InputOption, optional): Input mode, defaulted to USER_ENTERED.
        insert_data_option (InsertDataOption, optional): How to append, either by inserting new rows, or
            by overwritting blank cells.

    Returns:
        Response: response object with the status of the request. Response.data defaults to None.
            Returns a failed response if: the value list is empty; the range is invalid; selected
            an invalid input or insert option; failed to build request; or request sent an error
            response.

    Notes:
        The most common type of error here is badly formated value list. This means inputing something that is not
        a list of lists, or inserting object types that are not supported. 

        A quick way to fix types is valling `values = [[str(val) for val in row] for row in values]`, 
        which converts every value to str.

    Examples:
        ```python
        # appending values to a tab
        values = [[1,2,3],
                  [4,5,6]]
        tab.append_values(rng = 'A1', values = values) # Will try to append the values to the first cell

        # handling errors
        invalid_values = []
        response = tab.append_values(rng = 'C2:D4', values = invalid_values)
        print(response.error) # No values to insert.
        ```
    """

    # Registrando dados para validação depois.
    details = self._get_dets(locals())
    function_name = 'Sheet.append_values'

    # Validando valores:
    if not values:
        return Response.fail(f'No values to insert.', function_name=function_name, details = details)

    # Validando e formatando range.
    if rng:
        is_valid_range = validate_xrange(rng)
        if not is_valid_range:
            print(f'Invalid range: {rng}.')
            return Response.fail(f'Invalid x Range: {rng}.', function_name=function_name, details = details)
        if '!' in rng:
            rng = rng.split('!')[-1]
        request_range = f'{self.name}!{rng}'
    else:
        request_range = f'{self.name}'

    # Validando as outras opções:
    if input_option not in get_args(InputOption):
        error_msg = f'Arg Error: Invalid input option: {input_option}.'
        print(error_msg)
        return Response.fail(error_msg, function_name=function_name, details=details)
    if insert_data_option not in get_args(InsertDataOption):
        error_msg = f'Arg Error: Invalid insert data option: {insert_data_option}.'
        print(error_msg)
        return Response.fail(error_msg, function_name=function_name, details=details)

    # Preparando requisição
    body = {'values':values}

    try:
        request = self.service.values().append( 
            spreadsheetId = self.spreadsheet_id,
            range = request_range,
            valueInputOption = input_option,
            insertDataOption = insert_data_option,
            body = body
        )
    except Exception as e:
        return Response.fail(f'Error while building request: {e}', function_name=function_name, details=details)

    response = self.client.execute(request)

    if response.ok:
        result = response.data
        if result:
            details['range'] = request_range
            details['table_range'] = result.get('tableRange')
            if 'updates' in result:
                details['updated_range'] = result['updates'].get('updatedRange')
        response.data = None
        response.details = details
        return response
    else:
        if response.error:
            response.error.details = details
            response.error.function_name = function_name
        return response

clear_cells(rng='')

Method to clear cells in a tab of the Spreadsheet. Will only empty the value of the cell, otherwise keeping the format and other properties.

Parameters:

Name	Type	Description	Default
rng	str	range to clear, in Excel format (e.g. 'A1:G3', '1:12'). If left empty, whole tab will be cleared, so be careful.	''

Returns:

Name	Type	Description
Response	Response	Response object with the status of the request. Returns an failed response if the rng is invalid, if it failed to build the request, or if the API call returned an error.

Example

# clearing a few cells:
tab.clear_cells('A1:D9')

# clearing the whole tab
tab.clear_cells()

Source code in src/googleSheetsLib/core.py

def clear_cells(self, rng:str = '') -> Response:
    """
    Method to clear cells in a tab of the Spreadsheet. Will only empty the value of the cell,
    otherwise keeping the format and other properties.

    Args:
        rng (str): range to clear, in Excel format (e.g. 'A1:G3', '1:12'). If left empty,
            whole tab will be cleared, so be careful.

    Returns:
        Response: Response object with the status of the request. Returns an failed response if the
            rng is invalid, if it failed to build the request, or if the API call returned
            an error.

    Example:
        ```python
        # clearing a few cells:
        tab.clear_cells('A1:D9')

        # clearing the whole tab
        tab.clear_cells()
        ```
    """
    details = self._get_dets(locals())
    function_name = 'Sheet.append_values'

    if rng:
        is_valid_range = validate_xrange(rng)
        if not is_valid_range:
            print(f'Invalid range: {rng}.')
            return Response.fail(f'Invalid x Range: {rng}.', function_name=function_name, details = details)
        if '!' in rng:
            rng = rng.split('!')[-1]
        request_range = f'{self.name}!{rng}'
    else:
        request_range = f'{self.name}'

    # Construíndo a request:
    try:
        request = self.service.values().clear( 
            spreadsheetId = self.spreadsheet_id,
            range = request_range
        )
    except Exception as e:
        return Response.fail(f'Error while building request: {e}', function_name=function_name, details=details)

    response = self.client.execute(request)

    if response.ok:
        result = response.data
        if result:
            details['range'] = request_range
            details['cleared_range'] = result.get('clearedRange')
        response.data = None
        response.details = details
        return response
    else:
        if response.error:
            response.error.details = details
            response.error.function_name = function_name
        return response

delete_rows(rng='', start_row=-1, end_row=-1, prepare=False)

Função que deleta linhas da planilha. Pode receber tanto range, quanto pode receber start_row e end_row (base 1 inclusivo)

Source code in src/googleSheetsLib/core.py

def delete_rows(self, rng:str = '', start_row:int =-1, end_row:int=-1, prepare = False):
    "Função que deleta linhas da planilha. Pode receber tanto range, quanto pode receber start_row e end_row (base 1 inclusivo)"

    details = self._get_dets(locals())
    function_name = 'Sheet.delete_rows'

    if rng:
        if '!' in rng:
            rng = rng.split('!')[-1]
        grid_range = xrange_to_grid_range(rng)
        if not grid_range:
            return Response.fail(f'Invalid range: {rng}', function_name=function_name, details=details)
        elif grid_range['startRowIndex'] < 0 or grid_range['endRowIndex'] < 1:
            return Response.fail(f'Invalid range: {rng}', function_name=function_name, details=details)
        else:
            start_index = grid_range['startRowIndex']
            end_index = grid_range['endRowIndex']
    if not rng:
        if end_row == -1 and start_row == -1:
            return Response.fail(f'Missing arguments: range or start_row and end_row', function_name=function_name, details=details)
        if (end_row < 1 or start_row < 1) or end_row < start_row:
            return Response.fail(f'Invalid row ranges: {(start_row, end_row)}', function_name=function_name, details=details)
        start_index = start_row - 1 # Offset de passar de base 1 pra base 0
        end_index = end_row         # Mantém, porque o índice é exclusivo

    delete_request = {
        'deleteDimension' : {
            'range': {
                'sheetId' : self.id,
                'dimension' : 'ROWS',
                'startIndex' : start_index,
                'endIndex' : end_index
            }
        }
    }

    details['delete_request'] = delete_request # Telemetria

    if prepare:
        details['prepared'] = True
        self.parent_spreadsheet.batch_requests.append(delete_request)
        return Response.success(details = details)    


    try:
        body: BatchUpdateSpreadsheetRequest = {'requests' : [delete_request]} # type: ignore
        request = self.service.batchUpdate(
            spreadsheetId=self.spreadsheet_id,
            body = body
        )
    except Exception as e:
        return Response.fail(f'Error while building request: {e}', function_name=function_name, details=details)

    response = self.client.execute(request)

    if response.ok:
        response.details = details
        response.data = None
    elif response.error:
        response.error.details = details
        response.error.function_name = function_name
    return response

get_values(rng='')

Method to access the sheet's values. If range is not specified, returns the whole content if the sheet. Returns a Response object with the values. Can also by called by subscript notation, e.g. tab['A1:C2']

Parameters:

Name	Type	Description	Default
rng	str	Range in the Excel Format. E.g A1:Q22, A:Q, C32. If not specified, the values for the whole tab will be returned.	''

Returns:

Name	Type	Description
Response	Response	Response object with the sheet's data, if succeded, or error information, if failed. The data is accessed by the Response.data attribute, and it's expected to be a list of lists (list of rows), or a single value if only a single cell was requested. If the range is not a valid xrange, it returns a Response with a 'Invalid Range' error. All other errors are repassed as is via the Response.error object.

Notes

If only a single cell is specified, the Response.data is a singular value. All other times, it contains a list of lists or None.

Example

# Requesting a range
response = tab.get_values('A2:C3') # Response.data = [[1,2,3],[4,5,6]]

# Requesting a row range using subscript:
response = tab['2:10'] # Response.data = [row2, row3, row4 ... ]

# Requesting a singular cell:
response = tab['C2'] # Response.data = 3

# Handling errors:
response = tab.get_values('A33sd:221AB2') # Invalid range
if response.error:
    print(response.error.message) # 'Invalid x range: A33sd:221AB2'

Source code in src/googleSheetsLib/core.py

def get_values(self, rng:str = '') -> Response:
    """
    Method to access the sheet's values. If range is not specified, returns the whole content if the sheet.
    Returns a Response object with the values.
    Can also by called by subscript notation, e.g. tab['A1:C2']

    Args: 
        rng(str, optional): Range in the Excel Format. E.g `A1:Q22`, `A:Q`, `C32`.
            If not specified, the values for the whole tab will be returned. 

    Returns:
        Response: Response object with the sheet's data, if succeded, or error information, if failed.
            The data is accessed by the Response.data attribute, and it's expected to be a list of lists (list of rows),
            or a single value if only a single cell was requested.
            If the range is not a valid xrange, it returns a Response with a 'Invalid Range' error.
            All other errors are repassed as is via the Response.error object.

    Notes:
        If only a single cell is specified, the Response.data is a singular value.
        All other times, it contains a list of lists or None.

    Example:
        ```python
        # Requesting a range
        response = tab.get_values('A2:C3') # Response.data = [[1,2,3],[4,5,6]]

        # Requesting a row range using subscript:
        response = tab['2:10'] # Response.data = [row2, row3, row4 ... ]

        # Requesting a singular cell:
        response = tab['C2'] # Response.data = 3

        # Handling errors:
        response = tab.get_values('A33sd:221AB2') # Invalid range
        if response.error:
            print(response.error.message) # 'Invalid x range: A33sd:221AB2'
        ```
    """

    details = self._get_dets(locals())
    function_name = 'Sheet.get_values'

    # Validando e formatando range
    if rng:
        is_valid_range = validate_xrange(rng)
        if not is_valid_range:
            print(f'Invalid range: {rng}.')
            return Response.fail(f'Invalid x Range: {rng}.', function_name=function_name, details = details)
        if '!' in rng:
            rng = rng.split('!')[-1]
        request_range = f'{self.name}!{rng}'
    else:
        request_range = f'{self.name}'

    # Criando requisição
    try:
        request = self.service.values().get( 
            spreadsheetId = self.spreadsheet_id,
            range = request_range
        )
    except Exception as e:
        return Response.fail(f'Error while building request: {e}', function_name=function_name, details=details)

    # Fazendo requisição
    response = self.client.execute(request)

    # Resolvendo resposta da requisição
    if response.ok:
        details['range'] = request_range
        if response.data:
            response.data = response.data.get('values')
            if is_cell(rng) and isinstance(response.data, list) and len(response.data[0]) == 1:
                response.data = response.data[0][0]

        response.details = details

    else:
        if response.error:
            response.error.details = details
            response.error.function_name = function_name

    return response

to_csv(fp, rng='', sep=',')

Função que pega os dados de uma planilha e salva em formato CSV.

Source code in src/googleSheetsLib/core.py

def to_csv(self, fp, rng = '', sep = ','):
    "Função que pega os dados de uma planilha e salva em formato CSV."
    values = self.get_values(rng)
    if values.data:
        pd.DataFrame(values.data).to_csv(fp, index = False, sep = sep, header = False)

update(values, rng='A1', value_input_option='USER_ENTERED', major_dimension='ROWS')

Função que atualiza células da planilha.

Source code in src/googleSheetsLib/core.py

def update(self,
           values:list[list],
           rng:str = 'A1', 
           value_input_option:InputOption = 'USER_ENTERED',
           major_dimension:MajorDimension = 'ROWS'):
    "Função que atualiza células da planilha."

    details = self._get_dets(locals())
    function_name = 'Sheet.update'

    if rng:
        is_valid_range = validate_xrange(rng)
        if not is_valid_range:
            print(f'Invalid range: {rng}.')
            return Response.fail(f'Invalid x Range: {rng}.', function_name=function_name, details = details)
        if '!' in rng:
            rng = rng.split('!')[-1]
        if ':' not in rng:
            rng = get_values_delta(rng, values)
        request_range = f'{self.name}!{rng}'
    else:
        return Response.fail(f'No range specified.', function_name=function_name, details = details)

    # Validando parâmetros adicionais:
    if major_dimension not in get_args(MajorDimension):
        error_msg = f'Args Error: Invalid major dimension {major_dimension}'
        return Response.fail(error_msg, function_name = function_name, details = details)
    if value_input_option not in get_args(InputOption):
        error_msg = f'Args Error: Invalid input option {value_input_option}'
        return Response.fail(error_msg, function_name = function_name, details = details)

    body = {
        'values' : values,
        'majorDimension' : major_dimension
    }

    try:
        request = self.service.values().update(
            range = request_range,
            spreadsheetId = self.spreadsheet_id,
            body = body, 
            valueInputOption = value_input_option
        )
    except Exception as e:
        return Response.fail(f'Error while building request: {e}', function_name=function_name, details=details)

    response = self.client.execute(request)

    if response.ok:
        result = response.data
        if result:
            details['range'] = request_range
            details['updated_range'] = result.get('updatedRange')
            details['updated_cells'] = result.get('updatedCells')
            details['updated_rows'] = result.get('updatedRows')
            details['updated_columns'] = result.get('updatedColumns')
        response.data = None
        response.details = details
        return response
    else:
        if response.error:
            response.error.details = details
            response.error.function_name = function_name
        return response