Google Sheets Python API v4

Nithin Murali

Last update: Dec 31, 2022

Related tags

Database Drivers python python-lib spreadsheet google-sheets google-sheets-api google-sheets-api-v4 google-sheets-library

Overview

pygsheets - Google Spreadsheets Python API v4

A simple, intuitive library for google sheets which gets your work done.

Features:

Open, create, delete and share spreadsheets using title or key
Intuitive models - spreadsheet, worksheet, cell, datarange
Control permissions of spreadsheets.
Set cell format, text format, color, write notes
Named and Protected Ranges Support
Work with range of cells easily with DataRange and Gridrange
Data validation support. checkboxes, drop-downs etc.
Conditional formatting support
get multiple ranges with get_values_batch and update wit update_values_batch

Updates

version 2.0.5 released

Installation

From PyPi (Stable)

pip install pygsheets

If you are installing from pypi please see the docs here.

From GitHub (Recommended)

pip install https://github.com/nithinmurali/pygsheets/archive/staging.zip

If you are installing from github please see the docs here.

Basic Usage

Basic features are shown here, for complete set of features see the full documentation here.

Obtain OAuth2 credentials from Google Developers Console for google spreadsheet api and drive api and save the file as client_secret.json in same directory as project. read more here.
Start using pygsheets:

Sample scenario : you want to share a numpy array with your remote friend

import pygsheets
import numpy as np

gc = pygsheets.authorize()

# Open spreadsheet and then worksheet
sh = gc.open('my new sheet')
wks = sh.sheet1

# Update a cell with value (just to let him know values is updated ;) )
wks.update_value('A1', "Hey yank this numpy array")
my_nparray = np.random.randint(10, size=(3, 4))

# update the sheet with array
wks.update_values('A2', my_nparray.tolist())

# share the sheet with your friend
sh.share("[email protected]")

Sample Scenario: you want to fill height values of students

## import pygsheets and open the sheet as given above

header = wks.cell('A1')
header.value = 'Names'
header.text_format['bold'] = True # make the header bold
header.update()

# or achive the same in oneliner
wks.cell('B1').set_text_format('bold', True).value = 'heights'

# set the names
wks.update_values('A2:A5',[['name1'],['name2'],['name3'],['name4']])

# set the heights
heights = wks.range('B2:B5', returnas='range')  # get the range as DataRange object
heights.name = "heights"  # name the range
heights.update_values([[50],[60],[67],[66]]) # update the vales
wks.update_value('B6','=average(heights)') # set the avg value of heights using named range

More Examples

Opening a Spreadsheet

# You can open a spreadsheet by its title as it appears in Google Docs 
sh = gc.open("pygsheetTest")

# If you want to be specific, use a key
sht1 = gc.open_by_key('1mwA-NmvjDqd3A65c8hsxOpqdfdggPR0fgfg5nXRKScZAuM')

# create a spreadsheet in a folder (by id)
sht2 = gc.create("new sheet", folder_name="my worksheets")

# open enable TeamDrive support
gc.drive.enable_team_drive("Dqd3A65c8hsxOpqdfdggPR0fgfg")

Operations on Spreadsheet doc

import pygsheets
c = pygsheets.authorize()
sh = c.open('spreadsheet')

# create a new sheet with 50 rows and 60 colums
wks = sh.add_worksheet("new sheet",rows=50,cols=60)

# create a new sheet with 50 rows and 60 colums at the begin of worksheets
wks = sh.add_worksheet("new sheet",rows=50,cols=60,index=0)

# or copy from another worksheet
wks = sh.add_worksheet("new sheet", src_worksheet='<other worksheet instance>')

# delete this wroksheet
sh.del_worksheet(wks)

# unshare the sheet
sh.remove_permissions("[email protected]")

Selecting a Worksheet

import pygsheets
c = pygsheets.authorize()
sh = c.open('spreadsheet')

# Select worksheet by id, index, title.
wks = sh.worksheet_by_title("my test sheet")

# By any property
wks = sh.worksheet('index', 0)

# Get a list of all worksheets
wks_list = sh.worksheets()

# Or just
wks = sh[0]

Operations on Worksheet doc

# Get values as 2d array('matrix') which can easily be converted to an numpy aray or as 'cell' list
values_mat = wks.get_values(start=(1,1), end=(20,20), returnas='matrix')

# Get values of - rows A1 to B10, column C, 1st row, 10th row
wks.get_values_batch(['A1:B10', 'C', '1', (10, None)])

# Get all values of sheet as 2d list of cells
cell_matrix = wks.get_all_values(returnas='matrix')

# update a range of values with a cell list or matrix
wks.update_values(crange='A1:E10', values=values_mat)

# update multiple ranges with bath update
wks.update_values_batch(['A1:A2', 'B1:B2'], [[[1],[2]], [[3],[4]]])

# Insert 2 rows after 20th row and fill with values
wks.insert_rows(row=20, number=2, values=values_list)

# resize by changing rows and colums
wks.rows=30

# use the worksheet as a csv
for row in wks:
    print(row)

# get values by indexes
 A1_value = wks[0][0]

# clear all values
wks.clear()

# Search for a table in the worksheet and append a row to it
wks.append_table(values=[1,2,3,4])

# export a worksheet as csv
wks.export(pygsheets.ExportType.CSV)

# Find/Replace cells with string value
cell_list = worksheet.find("query string")

# Find/Replace cells with regexp
filter_re = re.compile(r'(small|big) house')
cell_list = worksheet.find(filter_re, searchByRegex=True)
cell_list = worksheet.replace(filter_re, 'some house', searchByRegex=True)

# Move a worksheet in the same spreadsheet (update index)
wks.index = 2 # index start at 1 , not 0

# Update title
wks.title = "NewTitle"

# Update hidden state
wks.hidden = False

# working with named ranges
wks.create_named_range('A1', 'A10', 'prices')
wks.get_named_range('prices')
wks.get_named_ranges()  # will return a list of DataRange objects
wks.delete_named_range('prices')

# Plot a chart/graph
wks.add_chart(('A1', 'A6'), [('B1', 'B6')], 'Health Trend')

# create drop-downs
wks.set_data_validation(start='C4', end='E7', condition_type='NUMBER_BETWEEN', condition_values=[2,10], strict=True, inputMessage="inut between 2 and 10")

Pandas integration

If you work with pandas, you can directly use the dataframes

#set the values of a pandas dataframe to sheet
wks.set_dataframe(df,(1,1))

#you can also get the values of sheet as dataframe
df = wks.get_as_df()

Cell Object doc

Each cell has a value and cordinates (row, col, label) properties.

Getting cell objects

c1 = Cell('A1',"hello")  # create a unlinked cell
c1 = worksheet.cell('A1')  # creates a linked cell whose changes syncs instantanously
cl.value  # Getting cell value
c1.value_unformatted #Getting cell unformatted value
c1.formula # Getting cell formula if any
c1.note # any notes on the cell
c1.address # address object with cell position

cell_list = worksheet.range('A1:C7')  # get a range of cells 
cell_list = worksheet.col(5, returnas='cell')  # return all cells in 5th column(E)

Most of the functions has returnas param, if whose value is cell it will return a list of cell objects. Also you can use label or (row,col) tuple interchangbly as a cell adress

Cell Operations

Each cell is directly linked with its cell in spreadsheet, hence changing the value of cell object will update the corresponding cell in spreadsheet unless you explictly unlink it Also not that bu default only the value of cell is fetched, so if you are directly accessing any cell properties call cell.fetch() beforehand.

Different ways of updating Cells

# using linked cells
c1 = worksheet.cell('B1') # created from worksheet, so linked cell
c1.col = 5  # Now c1 correponds to E1
c1.value = "hoho"  # will change the value of E1

# Or onliner
worksheet.update_value('B1', 'hehe')

# get a range of cells
cell_list = worksheet.range('A1:C7')
cell_list = worksheet.get_values(start='A1', end='C7', returnas='cells')
cell_list = worksheet.get_row(2, returnas='cells')


# add formula
c1.formula = 'A1+C2'
c1.formula # '=A1+C2'

# get neighbouring cells
c2 = c1.neighbour('topright') # you can also specify relative position as tuple eg (1,1)

# set cell format
c1.set_number_format(pygsheets.FormatType.NUMBER, '00.0000')

# write notes on cell
c1.note = "yo mom"

# set cell color
c1.color = (1.0, 1.0, 1.0, 1.0) # Red, Green, Blue, Alpha

# set text format
c1.text_format['fontSize'] = 14
c1.set_text_format('bold', True)

# sync the changes
 c1.update()

# you can unlink a cell and set all required properties and then link it
# So yu could create a model cell and update multiple sheets
c.unlink()
c.note = "offine note"
c.link(wks1, True)
c.link(wks2, True)

DataRange Object doc

The DataRange is used to represent a range of cells in a worksheet. They can be named or protected. Almost all get_ functions has a returnas param, set it to range to get a range object.

# Getting a Range object
rng = wks.get_values('A1', 'C5', returnas='range')
rng.start_addr = 'A' # make the range unbounded on rows <Datarange Sheet1!A:B>
drange.end_addr = None # make the range unbounded on both axes <Datarange Sheet1>

# Named ranges
rng.name = 'pricesRange'  # will make this range a named range
rng = wks.get_named_ranges('commodityCount') # directly get a named range
rng.name = ''  # will delete this named range

#Protected ranges
rng.protected = True
rng.editors = ('users', '[email protected]')

# Setting Format
 # first create a model cell with required properties
model_cell = Cell('A1')
model_cell.color = (1.0,0,1.0,1.0) # rose color cell
model_cell.format = (pygsheets.FormatType.PERCENT, '')

 # Setting format to multiple cells in one go
rng.apply_format(model_cell)  # will make all cell in this range rose color and percent format
# Or if you just want to apply format, you can skip fetching data while creating datarange
Datarange('A1','A10', worksheet=wks).apply_format(model_cell)

# get cells in range
cell = rng[0][1]

Batching calls

If you are calling a lot of spreadsheet modification functions (non value update). you can merge them into a single call. By doing so all the requests will be merged into a single call.

gc.set_batch_mode(True)
wks.merge_cells("A1", "A2")
wks.merge_cells("B1", "B2")
Datarange("D1", "D5", wks).apply_format(cell)
gc.run_batch() # All the above requests are executed here
gc.set_batch_mode(False)

Batching also happens when you unlink worksheet. But in that case the requests are not merged.

How to Contribute

This library is still in development phase.

Follow the Contributing to Open Source Guide.
Branch off of the staging branch, and submit Pull Requests back to that branch. Note that the master branch is used for version bumps and hotfixes only.
For quick testing the changes you have made to source, run the file tests/manual_testing.py. It will give you an IPython shell with lastest code loaded.

Report Issues/Features

Please report bugs and suggest features via the GitHub Issues.
Before opening an issue, search the tracker for possible duplicates.
If you have any usage questions, ask a question on stackoverflow with pygsheets Tag

Run Tests

install py.test
run make test

Now that you have scrolled all the way down, finding this library useful?

Comments

Index error list index out of range - pygsheets

Hi Nithin, How are you? My wife having few Google sheets with data asked me to make reports based those data. Before touching her drive, I tested your code in my drive first and it worked perfect. When i tried in my wife's google account it shows following error. ''ss = gc.open('chitra') File "C:\Users\Aruloli\Python\pygsheets-staging\pygsheets\client.py", line 136, in open spreadsheet = list(filter(lambda x: x['name'] == title, self.drive.spreadsheet_metadata()))[0] IndexError: list index out of range'' When I open with gc.open_by_key & gc.open_by_url it works perfect but without many pygsheets methods Pl help me. Arul

opened by Aruloli 43
get_row - list index out of range
If row w\o values, get_row fails to form objects list:

ipdb> wks.get_row(1, returnas='cells') *** IndexError: list index out of range

Also, no include_empty option from docs: http://pygsheets.readthedocs.io/en/latest/worksheet.html#pygsheets.Worksheet.get_row

ipdb> wks.get_row(1, returnas='cells', include_empty=True) *** TypeError: get_row() got an unexpected keyword argument 'include_empty'

pygsheets==1.1.4
opened by alexz-kh 43
Does pygsheets Support Access to Spreadsheets on Team Drives?

I am trying to access a Spreadsheet on a Team Drive using gspread. It is not working. I was wondering if gsheets has the new capability available in Google Drive API v3 to open spreadsheets on Team Drives. If so, how do I specify the fact I want to open a spreadsheet on a Google Team Drive and not my own Google drive? If not, when will that functionality be available? Thanks!
feature

opened by casalemi 43
Make a copy of a spreadsheet and rename spreadsheet

I need to make a complete copy of a spreadsheet(not worksheet) and also rename it to it's ID. ie. Like you would do using "File-Make a Cop" and also rename "stockdata" to "12xxlLWhrhnTMP7yDxVLaA_amWdhTFIFazyo9N7gG9_I"

Is this possible in pygsheets?
feature

opened by g4spow 24
Extract Google Drive API

I want to extract the Google Drive API parts from the client class to better distinguish the two. This should help make it a bit clearer and clean up the Client class a bit which has a ton of functions.

The goal is to improve the way the Google Drive API is used for the useful features. And potentially to have the option to load pygsheets without granting access to the Google Drive API, as this may be a security issue.

Currently only the export function is changed. All the functionality is moved from the Spreadsheet & Worksheet classes to the DriveAPIWrapper class.

The implementation is changed that always all sheets will be exported, but to have the option to export specific sheets into CSV & TSV.

Adds support for TSV & HTML export. Added a lot of documentation. Added a test.

Need to still update the documentation and add an additional test.

What do you think?

opened by Kordishal 22

SSLError: [SSL: DECRYPTION_FAILED_OR_BAD_RECORD_MAC] decryption failed or bad record mac

I have a python script which uses pygsheets to connect to gsheet. The script runs quite well on the Windows Server but throws an exception 'SSLError: [SSL: DECRYPTION_FAILED_OR_BAD_RECORD_MAC] decryption failed or bad record mac' when running on Ubuntu.

from oauth2client.service_account import ServiceAccountCredentials

scope = [
        'https://spreadsheets.google.com/feeds',
        'https://www.googleapis.com/auth/drive'
         ]

credentials01 = ServiceAccountCredentials.from_json_keyfile_name('creds01.json', scope)
credentials02 = ServiceAccountCredentials.from_json_keyfile_name('creds02.json', scope)

file01 = pygsheets.authorize(credentials=credentials01)
file02 = pygsheets.authorize(credentials=credentials02)
wb01 = file01.open('Database System 2')
wb02 = file02.open('Database System 2')

if __name__ == '__main__':
    manager = multiprocessing.Manager()
    output_dfs = manager.list()
    new_dfs_dict = manager.dict()
    for l in main_list:
        for i in range(0, len(l[0]), 5):
            processes = []
            ids_sublist = []
            for j in range(i, i + 5):
                try:
                    ids_sublist.append(l[0][j])
                except:
                    pass
            for ID in ids_sublist:
                processes.append(multiprocessing.Process(target=fetch_data, args=(ID, start_date, end_date, new_dfs_dict, output_dfs)))
            for p in processes:
                p.start()
            for p in processes:
                p.join()

        for ID in l[0]:
            if not l[1].empty:
                cols_list = list(l[2][ID].columns)
                cols_list.remove('Type')
                cols_list[1:1] = ['Type']
                l[2][ID] = l[2][ID][cols_list]
                l[2][ID].update(new_dfs_dict[ID])
                l[2][ID] = pd.merge(l[2][ID], new_dfs_dict[ID])
            else:
                l[2][ID] = new_dfs_dict[ID]
        ready_to_set_df = pd.DataFrame()
        for ID in l[2]:
            ready_to_set_df = pd.concat([ready_to_set_df, l[2][ID]], sort=False)
            output_sheet = wb01.worksheet_by_title(l[3])            
            output_sheet.clear()
            output_sheet.set_dataframe(ready_to_set_df.fillna(0), 'A1')

    output_df = pd.DataFrame(columns=bank_portal_data_header)
    for df in output_dfs:
        output_df = output_df.append(df)
    try:
        data_sheet = wb02.worksheet_by_title(end_date)
        data_df = data_sheet.get_as_df(has_header=True)
        output_df = data_df[bank_portal_data_header].append(output_df)
        output_df = output_df.drop_duplicates(keep=False)
        data_sheet.clear()
        data_sheet.set_dataframe(output_df, 'A1', copy_head=True)
    except:
        data_sheet = wb02.add_worksheet(end_date)
        data_sheet.set_dataframe(output_df, 'A1', copy_head=True)`````

It first threw the exception at the line data_sheet.clear().
When I commented out this statement, it was at the next line data_sheet.set_dataframe(output_df, 'A1', copy_head=True)

Please help!

update docs

opened by engrumaraftab 20

RequestError: Timeout
I just started using pygsheet and am try a few basic operations.

Here is my flow:

read 2 DFs from sheets (pass)

write the 2 DFs to 2 csv files and 1 xlsx w 2 tabs using pandas (pass)

read the 2 csv files using pandas (pass)

cleanup the csv files by replacing NaN w/ zero. (note: before NaN cleanup, i received "HttpError: <HttpError 400 when requesting" .... "Invalid JSON payload received. Unexpected token." After cleanup no longer seeing the 400 error.

writing both DFs to sheets using 'set_dataframe' 1st DF (3767x124) => fails w/ below error 2nd DF (32x18) => pass no error, written correctly

ERROR: "RequestError: Timeout"

Is there a limit on the DF size either by Goggle Sheets API V4 or pygsheets?

I hope my explanation above is clear. Any help or guidance will be appreciated.

thank-you, --Rajeev
bug
opened by jainraje 20
exceeds grid limits. Max rows: 2280, max columns: 178

Recently I am getting a "exceeds grid limits. Max rows: 2280, max columns: 178" error message. My actual data frame is 2306 rows x 105 cols (242,130 cells). Please advise. thx.

Here is exception stack: File "/Users/rajeev/Documents/Code/Python/smdb/smdb_llio.py", line 357, in google_sheet_write_fit_false wks.set_dataframe(df, start, fit=False) File "/Users/rajeev/anaconda/lib/python3.5/site-packages/pygsheets/worksheet.py", line 780, in set_dataframe self.update_cells(crange=crange, values=values) File "/Users/rajeev/anaconda/lib/python3.5/site-packages/pygsheets/worksheet.py", line 431, in update_cells self.client.sh_update_range(self.spreadsheet.id, body, self.spreadsheet.batch_mode, parse=parse) File "/Users/rajeev/anaconda/lib/python3.5/site-packages/pygsheets/client.py", line 374, in sh_update_range self._execute_request(spreadsheet_id, final_request, batch) File "/Users/rajeev/anaconda/lib/python3.5/site-packages/pygsheets/client.py", line 418, in _execute_request response = request.execute() File "/Users/rajeev/anaconda/lib/python3.5/site-packages/oauth2client/_helpers.py", line 133, in positional_wrapper return wrapped(*args, **kwargs) File "/Users/rajeev/anaconda/lib/python3.5/site-packages/googleapiclient/http.py", line 840, in execute raise HttpError(resp, content, uri=self.uri) googleapiclient.errors.HttpError: <HttpError 400

opened by jainraje 17
using pyinstaller when importing pygsheets
Hi everyone! I'm trying to create an exe file to distribute among users in my company. the exe file open a simple GUI (made with Tkinter), which allows the user to select a txt file from their pc and inserts the data from the txt into a Google sheet. I tested my python script before attempting to turn it into an exe and it worked great. but now, when I'm trying to use Pyinstaller it shows the following error:

C:\Users\Asus\PycharmProjects\dmtrial\venv\dist>DMPulse2GS.exe Traceback (most recent call last): File "pygsheets\sheet.py", line 39, in __init__ FileNotFoundError: [Errno 2] No such file or directory: 'C:\\Users\\Asus\\AppData\\Local\\Temp\\_MEI102362\\pygsheets\\data\\sheets_discovery.json' During handling of the above exception, another exception occurred: Traceback (most recent call last): File "DMPulse2GS.py", line 39, in <module> File "pygsheets\authorization.py", line 131, in authorize File "pygsheets\client.py", line 61, in __init__ File "pygsheets\sheet.py", line 42, in __init__ File "googleapiclient\_helpers.py", line 134, in positional_wrapper File "googleapiclient\discovery.py", line 291, in build File "googleapiclient\discovery.py", line 405, in _retrieve_discovery_doc googleapiclient.errors.UnknownApiNameOrVersion: name: sheets version: v4 [11284] Failed to execute script DMPulse2GS

this is the cmd command I used to create the exe file: C:\Users\Asus\PycharmProjects\dmtrial\venv>pyinstaller --onefile DMPulse2GS.py

it created three folders in this directory called: 'dist', 'build', and 'pycache'

after it finished running successfully, I copied my Service Accounts Key (json file) to the 'dist' folder created by Pyinstaller. (I also tried adding the file using --add-data when running pyinstaller for the first time on the file but it returns the same error)

then I tried clicking the exe file, which was when it returned the above error.

I tried everything!! any help / tips would be amazing! Thanks in advance!
opened by danagold404 14
What to do to deploy in Web?

Hi I would want to use the package to log periodically on the spreadsheet. These would be on a timely basis and scheduled. I will be using Flask and APScheduler for these. But I think I got a problem when deploying it to Heroku, it won't authenticate via Oauth2. Then it presented me this: [LINK]

How do I go about these? Has someone tried it? Thanks! (https://developers.google.com/api-client-library/python/guide/aaa_client_secrets)

opened by nikkopante 14
Rework find for spreadsheet & worksheet

This PR reworks both find for spreadsheet & worksheet.

The spreadsheet.find will now simply call worksheet.find for every worksheet present. The result will be returned as a list of lists with an empty list if no match was found.

Find on worksheet will search the datagrid for the pattern according to the config params. Returns an empty list if no match was found.

If the worksheet is linked it will first update values from remote and update cells after replacement.

As a fix for #197

opened by Kordishal 14
Naming unbounded ranges

Currently, when you attempt to name an unbounded datarange ('Roster'!6:6), it will instead name the range but limited to the number of columns you currently have ('Roster'!A6:H6)

Describe the solution you'd like I'd like to be able to name an entire row which would make future expansion a bit cleaner.

opened by Vanifac 0

Error on adding a row to the table

In an attempt to add the first row to the empty table.

 File "*******\venv\lib\site-packages\pygsheets\utils.py", line 214, in wrapper
    return func(*args, **kwargs)
  File "*******\venv\lib\site-packages\pygsheets\worksheet.py", line 1148, in append_table
    'tableRange': GridRange.create(response_json['tableRange'], self),
KeyError: 'tableRange'

and the response_json itself

{
  'spreadsheetId': '*******',
  'updates': {
    'spreadsheetId': '**********',
    'updatedRange': "'********'!A1:F1",
    'updatedRows': 1,
    'updatedColumns': 6,
    'updatedCells': 6
  }
}

opened by exactstat 0

client.py, line 124, attempt to take index on dict

Module: client.py
Method: create
Code:

        if folder:
            self.drive.move_file(result['spreadsheetId'],
                                 old_folder=self.drive.spreadsheet_metadata(fid=result['spreadsheetId'])[0].get('parents', [None])[0],
                                 new_folder=folder)

self.drive.spreadsheet_metadata(fid=result['spreadsheetId']) - returns a dict, not a list.

opened by exactstat 0

append_table constantly crash

Describe the bug append_table crash with exception.

I have regular code which deletes data from spreadsheet with multiple deletes and append new data. Append stopped to work starting version 2.0.6 As I understand it is because of https://github.com/nithinmurali/pygsheets/issues/546

To Reproduce Try to append data to regular table with data.

credentials = Credentials.from_authorized_user_file(
    credentials_path, scopes=pygsheets.authorization._SCOPES)
gc = pygsheets.authorize(credentials=credentials)
sh = gc.open_by_key(my_key)

# it was in my code, but should not cause the issue
# col = wks.get_col(0)
# wks.delete_rows(my_row, my_count)

wks = sh.worksheet_by_title("My Data")
wks.append_table(values=my_data)

# see error
  File "/usr/local/lib/python3.11/site-packages/pygsheets/utils.py", line 214, in wrapper
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/pygsheets/worksheet.py", line 1148, in append_table
    'tableRange': GridRange.create(response_json['tableRange'], self),
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/pygsheets/address.py", line 374, in create
    grange = GridRange(label=data, worksheet=wks)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/pygsheets/address.py", line 259, in __init__
    self._calculate_addresses(label)
  File "/usr/local/lib/python3.11/site-packages/pygsheets/address.py", line 449, in _calculate_addresses
    self.worksheet_title = label.split('!')[0]
    ^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/site-packages/pygsheets/address.py", line 359, in worksheet_title
    raise InvalidArgumentValue("This range already has a worksheet with different title set.")
pygsheets.exceptions.InvalidArgumentValue: This range already has a worksheet with different title set.

System Information

OS: [e.g. iOS]: linux
pygsheets version : 2.0.6
pygsheets installed from (github or pypi): pypi

bug

opened by ossipsasha 1

Sheet reference gets ducked typed to cell reference

I encountered an issue when trying to call get_all_values() on a sheet that is a valid cell reference - such as A1(exists) or OT1(doesn't exist).

Describe the bug Whenever I try to read data from a sheet called OT1 I get a 400 error - Range ('OT1'!OT1). This seems to be related to this google sheet bug. Apparently, the sheet name is also duck typed to a cell reference (which may not exist) and it returns 400 error. If the cell exists (such as A1) this call passes but returns unexpected results - only the contents of cell A1 in sheet A1.

Current solution Explicitly define cell range. I replaced get_all_values() with get_values() with large enough column span.

To Reproduce Create a worksheet with a sheet called 'OT1' then try to get_all_values().

  File "/venv/lib/python3.10/site-packages/pygsheets/worksheet.py", line 492, in get_all_values
    return self.get_values(None, None, returnas=returnas, majdim=majdim,
  File "/venv/lib/python3.10/site-packages/pygsheets/utils.py", line 180, in wrapper
    return method(self, *args, **kwargs)
  File "/venv/lib/python3.10/site-packages/pygsheets/worksheet.py", line 354, in get_values
    values = self.client.get_range(self.spreadsheet.id, grange.label, majdim,
  File "/venv/lib/python3.10/site-packages/pygsheets/client.py", line 234, in get_range
    result = self.sheet.values_get(spreadsheet_id, value_range, major_dimension, value_render_option,
  File "/venv/lib/python3.10/site-packages/pygsheets/sheet.py", line 419, in values_get
    return self._execute_requests(request)
  File "/venv/lib/python3.10/site-packages/pygsheets/sheet.py", line 495, in _execute_requests
    response = request.execute(num_retries=self.retries)
  File "/venv/lib/python3.10/site-packages/googleapiclient/_helpers.py", line 134, in positional_wrapper
    return wrapped(*args, **kwargs)
  File "/venv/lib/python3.10/site-packages/googleapiclient/http.py", line 915, in execute
    raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: <HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/some-valid-id/values/OT1?majorDimension=ROWS&valueRenderOption=FORMATTED_VALUE&dateTimeRenderOption=SERIAL_NUMBER&alt=json returned "Range ('OT1'!OT1) exceeds grid limits. Max rows: 4999, max columns: 27". Details: "Range ('OT1'!OT1) exceeds grid limits. Max rows: 4999, max columns: 27">

System Information

OS: MacOS 12.2.1
pygsheets version : 2.0.5
pygsheets installed from (github or pypi): pypi

bug

opened by pungys 0

Releases(2.0.6)

2.0.6(Nov 30, 2022)

Source code(tar.gz)
Source code(zip)
2.0.5(Feb 5, 2021)
Bug fixes

added batch value update

changed default behavior of get_as_df ref breaking change

Source code(tar.gz)
Source code(zip)
2.0.4(Jan 18, 2021)

data validation support added added conditional formatting metadata support added @dankrause added metadata search @dankrause improved files api support @dankrause Added batch get (now you can fetch multiple ranges in single request) hyperlink support Added batchmode in client (Better api merging support) added support for specifying parent folder name in create bug fixes
Source code(tar.gz)
Source code(zip)
2.0.3.1(Mar 21, 2020)

hotfix for worksheet indexing issue. reverted the indexing to start from index 0
Source code(tar.gz)
Source code(zip)
2.0.3(Feb 15, 2020)

Changed how cell addressing is handled Added Address Added Grid Range Now all range implementations use this addressing Now Datarange supports unbounded ranges

Added update call batching while un-linked Docs Improved Other bug fixes
Source code(tar.gz)
Source code(zip)
2.0.2(Jul 26, 2019)

added cell merging and un-merging for a range added automatically resize rows / columns based on content added custom http object passing on auhtorization

other bug fixes

Contributors : @daverck
Source code(tar.gz)
Source code(zip)
2.0.1(Apr 7, 2019)

bug fixes
Source code(tar.gz)
Source code(zip)
2.0.0(Nov 25, 2018)
This version is not backwards compatible with 1.x There is major rework in the library with this release. Some functions are renamed to have better consistency in naming and clear meaning.

update_cell() renamed to update_value()

update_cells() renamed to update_values()

update_cells_prop() renamed to update_cells()

changed authorize() params : outh_file -> client_secret, outh_creds_store ->credentials_directory, service_file -> service_account_file, credentials -> custom_credentials

teamDriveId, enableTeamDriveSupport changed to client.drive.enable_team_drive, include_team_drive_items

parameter changes for all get_* functions : include_empty, include_all changed to include_tailing_empty, include_tailing_empty_rows

parameter changes in created_protected_range() : gridrange param changed to start, end

remoed batch mode

find() splited into find() and replace()

removed (show/hide)_(row/column), use (show/hide)_dimensions instead

removed link/unlink from spreadsheet

New Features added

chart Support added

sort feature added

better support for protected ranges

multi header/index support in dataframes

removes the dependency on oauth2client and uses google-auth and google-auth-oauth.

Other bug fixes and performance improvements

Credits

@Kordishal @ssg2526
Source code(tar.gz)
Source code(zip)
1.1.4(Mar 10, 2018)

Better Multi-index support for dataframe Better Team Drive Support Update cell list with properties Fixed authorization issue in windows share sheet with anyone protected range support, added visibility controls frozen row/col support dimension visibility controls added batch update only for cell properties

other bug fixes
Source code(tar.gz)
Source code(zip)
1.1.3(Aug 28, 2017)
Added Teamdrive support

Added cell text alignment

Improved Datarange support

Other bug fixes

Source code(tar.gz)
Source code(zip)
v1.1.2(Jun 24, 2017)

Add Text Rotation feature Add Teamdrive support Add adjust_row_height method to adjust the height of one or more row Various bug fixes/improvements in dataframe handling added extend to update_cells, improved cell_list update added refresh option to worksheet made cache process and os independed, fixes enum representation bug fixe
Source code(tar.gz)
Source code(zip)
v1.1.1(Feb 25, 2017)

New data grid model for storing ranges, hence support for named ranges. improved cell support. bug fixes
Source code(tar.gz)
Source code(zip)
v1.0.0(Feb 21, 2017)

Renamed functions to non ambiguous names bug fixes
Source code(tar.gz)
Source code(zip)

Owner

Nithin Murali

GitHub https://pygsheets.readthedocs.io/en/latest

python-bigquery Apache-2python-bigquery (🥈34 · ⭐ 3.5K · 📈) - Google BigQuery API client library. Apache-2

Python Client for Google BigQuery Querying massive datasets can be time consuming and expensive without the right hardware and infrastructure. Google

550 Jan 1, 2023

Google Cloud Client Library for Python

Google Cloud Python Client Python idiomatic clients for Google Cloud Platform services. Stability levels The development status classifier on PyPI ind

4.1k Jan 1, 2023

Pandas Google BigQuery

pandas-gbq pandas-gbq is a package providing an interface to the Google BigQuery API from pandas Installation Install latest release version via conda