Google Sheets Python API v4

Overview

pygsheets - Google Spreadsheets Python API v4

Build Status PyPI version Documentation Status

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

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.

  1. 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.

  2. 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? Buy Me A Coffee

Comments
  • Index error list index out of range - pygsheets

    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

    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?

    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

    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

    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

    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

    RequestError: Timeout

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

    Here is my flow:

    1. read 2 DFs from sheets (pass)
    2. write the 2 DFs to 2 csv files and 1 xlsx w 2 tabs using pandas (pass)
    3. read the 2 csv files using pandas (pass)
    4. 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.
    5. 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

    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

    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?

    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

    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

    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

    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

    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

    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

    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.5(Feb 5, 2021)

  • 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)

  • 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)

  • 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)

  • 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)

  • v1.0.0(Feb 21, 2017)

A powerful bot to copy your google drive data to your team drive

⚛️ Clonebot - Heroku version ⚡ CloneBot is a telegram bot that allows you to copy folder/team drive to team drives. One of the main advantage of this

MsGsuite 269 Dec 23, 2022
Pythonic wrapper for the Aladhan prayer times API.

aladhan.py is a pythonic wrapper for the Aladhan prayer times API. Installation Python 3.6 or higher is required. To Install aladhan.py with pip: pip

HETHAT 8 Aug 17, 2022
An API wrapper around the pythonanywhere's API.

pyaww An API wrapper around the pythonanywhere's API. The name stands for pythonanywherewrapper. 100% API coverage Most of the codebase is documented

7 Dec 11, 2022
Python Client Library to interface with the Phoenix Realtime Server

supabase-realtime-client Python Client Library to interface with the Phoenix Realtime Server This is a fork of the supabase community realtime client

Anand 2 May 24, 2022
Telegram bot to scrape images from the reddit universe

Telegram bot to scrape images from the reddit universe

XD22 3 Sep 30, 2022
Automate TikTok follower bot, like bot, share bot, view bot and more using selenium

Zefoy TikTok Automator Automate TikTok follower bot, like bot, share bot, view bot and more using selenium. Click here to report bugs. Usage Download

555 Dec 30, 2022
trackbranch is a tool for developers that can be used to store collections of branches in the form of profiles.

trackbranch trackbranch is a tool for developers that can be used to store collections of branches in the form of profiles. This can be useful for sit

Kevin Morris 1 Oct 21, 2021
A community made discord bot coded in Python and running on AWS.

Pogbot Project Open Group Discord This is an open source community ran project. Join the discord for more information on how to participate. Coded in

Project Open Group 2 Jul 27, 2022
RaidBot for WhatsApp

WhatsappRaid Скрипт подготовлен специально для сайта https://pysoc.ru и Ютуб канала PyPro Русский Простой спам бот для WhatsApp на Python3. Работает с

2 May 12, 2022
Code release for Transferable Curriculum for Weakly-Supervised Domain Adaptation (AAAI2019)

TCL Code release for Transferable Curriculum for Weakly-Supervised Domain Adaptation (AAAI2019) Dataset Office-31 dataset, with 0.4 label noise Requir

THUML @ Tsinghua University 17 Jul 07, 2022
A simple telegram voting bot based on the python-telegram-bot api.

A simple telegram voting bot based on the python-telegram-bot api. *To make it more easy to use, I might make a C++ code in the future so you don't ha

3 Sep 13, 2021
Migrate BiliBili watched anime to Bangumi

说明 之前为了将B站看过的动画迁移到bangumi写的, 本来只是自己用, 但公开可能对其他人会有帮助. 仓库最近无法维护, 程序有很多缺点, 欢迎 PR 和 Contributors 使用说明 Python版本要求:Python 3.8+ 使用前安装依赖包: pip install -r requ

51 Sep 08, 2022
A Telegram bot that add a dynamic caption to musics

Music Channel Manager A Telegram bot that add a dynamic caption to musics Deploy to Heroku What is it ? It manage your music channel. With just adding

13 Oct 18, 2022
An API Client package to access the APIs for NBA.com

nba_api An API Client package to access the APIs for NBA.com Development Version: v1.1.9 nba_api is an API Client for www.nba.com. This package is mea

Swar Patel 1.4k Jan 01, 2023
A Discord Token Spammer, multi webhooks compatibility, made in python +3.7. By Ezermoz

DiscordWebhookSpammer A Discord Token Spammer, multi webhooks compatibility, made in python +3.7. By Ezermoz Put you webhook in webhooks.txt if you wa

3 Nov 24, 2021
A discord bot made by the community (uses python)

discord community bot context: this is a discord bot made by the community by community i mean people adding commands to the bot or changing the bot b

TR ASH 0 Oct 11, 2022
Discord Bot that can translate your text, count and reply to your messages with a personalised text

Discord Bot that can translate your text, count and reply to your messages with a personalised text

Grizz 2 Jan 26, 2022
Python wrapper for WhatsApp web-based on selenium

alright Python wrapper for WhatsApp web made with selenium inspired by PyWhatsApp Why alright ? I was looking for a way to control and automate WhatsA

Jordan Kalebu 193 Jan 06, 2023
Telegram bot to stream videos in telegram voicechat for both groups and channels.

Telegram bot to stream videos in telegram voicechat for both groups and channels. Supports live streams, YouTube videos and telegram media. With record stream support, Schedule streams, and many more

SOCIAL MECHANIC 4 Nov 13, 2022
ClearML - Auto-Magical Suite of tools to streamline your ML workflow. Experiment Manager, MLOps and Data-Management

ClearML - Auto-Magical Suite of tools to streamline your ML workflow Experiment Manager, MLOps and Data-Management ClearML Formerly known as Allegro T

ClearML 3.9k Jan 01, 2023