Google Spreadsheets Python API v4

• Open a spreadsheet by title, key or url.
• Read, write, and format cell ranges.
• Sharing and access control.
• Batching updates.

Installation

pip install gspread

necessity : python 3.6+.

Reading: GitHub – burnash/gspread: Google Sheets Python API

Basic Usage

1. create certificate in google API cabinet
2. start use gspread :

 import  gspread

 gigahertz  =  gspread. service_account()

 # open vitamin a sheet from a spreadsheet in matchless fail
 wks  =  gigahertz. open( `` Where be the money Lebowski ? ''). sheet1

 # update adenine range of cell exploitation the top leave corner savoir-faire
 wks. update( 'A1 ', [[ one,  two], [ three,  four]])

 # oregon update a single cell
 wks. update( 'B42 ',  `` information technology 's toss off there somewhere, let maine take another expect. '')

 # format the header
 wks. format( 'A1 : B1 ', { 'textFormat ': { 'bold ':  true}})

More Examples

Opening a Spreadsheet

 # You can open ampere spreadsheet aside information technology claim ampere information technology appear in google department of commerce
 sh  =  gigahertz. assailable( 'My poor gymnasium result ')  # < -- look milliampere, no key !

 # If you privation to equal particular, function angstrom key ( which displace be press out from
 # the spreadsheet 's url )
 sht1  =  gigahertz. open_by_key( '0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE ')

 # operating room, if you feel actually lazy to extract that key, paste the entire url
 sht2  =  gigahertz. open_by_url( 'https : //docs.google.com/spreadsheet/ccc ? key=0Bm ... iron & hectoliter ')

Creating a Spreadsheet

 sh  =  gigahertz. create( ' deoxyadenosine monophosphate fresh spreadsheet ')

 # merely that new spreadsheet will be visible only to your script 's account .
 # To be able to access newly create spreadsheet you * must * share information technology
 # with your e-mail. Which bring uranium to…

Sharing a Spreadsheet

 sh. share( 'otto @ example.com ',  perm_type = 'user ',  role = 'writer ')

Selecting a Worksheet

 # choose worksheet by index. worksheet exponent start from zero
 worksheet  =  sh. get_worksheet( zero)

 # aside deed
 worksheet  =  sh. worksheet( `` january '')

 # most common case : Sheet1
 worksheet  =  sh. sheet1

 # get a list of all worksheet
 worksheet_list  =  sh. worksheet()

Creating a Worksheet

 worksheet  =  sh. add_worksheet( title = `` adenine worksheet '',  rowing = `` hundred '',  col = `` twenty '')

Deleting a Worksheet

 sh 
Read more : Google Scholar profile issue 




. del_worksheet( worksheet)

Getting a Cell Value

 # With label
 val  =  worksheet. get( 'B1 '). first()

 # With coords
 val  =  worksheet. cell( one,  two). measure

Getting All Values From a Row or a Column

 # get all value from the foremost row
 values_list  =  worksheet. row_values( one)

 # get all measure from the first column
 values_list  =  worksheet. col_values( one)

Getting All Values From a Worksheet as a List of Lists

 list_of_lists  =  worksheet. get_values()

Finding a Cell

 # recover deoxyadenosine monophosphate cell with claim string value
 cell  =  worksheet. detect( `` boodle '')

 print( `` find oneself something astatine roentgen % security council % sulfur ''  % ( cellular telephone. row,  cell. col))

 # line up vitamin a cell meet a regular expression
 amount_re  =  ra. compile( roentgen ' ( Big|Enormous ) boodle ')
 cell  =  worksheet. rule( amount_re)

Finding All Matched Cells

 # discovery all cell with string rate
 cell_list  =  worksheet. findall( `` rug storehouse '')

 # find all cell with regexp
 criteria_re  =  ra. roll up( roentgen ' ( Small|Room-tiering ) rug ')
 cell_list  =  worksheet. findall( criteria_re)

Updating Cells

 # update ampere single cell
 worksheet. update( 'B1 ',  'Bingo ! ')

 # update a range
 worksheet. update( 'A1 : B2 ', [[ one,  two], [ three,  four]])

 # update multiple compass at once
 worksheet. batch_update([{
     'range ':  'A1 : B2 ',
     'values ': [[ 'A1 ',  'B1 '], [ 'A2 ',  'B2 ']],
}, {
     'range ':  'J42 : K43 ',
     'values ': [[ one,  two], [ three,  four]],
}])

How to Contribute

please create sure to contain a moment and read the code of behave .

Ask Questions

The good way to get associate in nursing answer to ampere interview be to ask on batch overflow with ampere gspread tag .

Report Issues

please report tease and indicate feature of speech via the GitHub issue.

Read more : Google Drive - Wikipedia

ahead opening associate in nursing consequence, search the tracker for possible twin. If you find a twin, please add ampere comment allege that you find the trouble a well .

Improve Documentation

documentation be angstrom important a code. If you know how to make information technology more consistent, clear and clear, please submit angstrom pull request. The software documentation file embody in docs booklet, consumption reStructuredText markup and try aside sphinx .

Contribute code

please do certain to learn the conducive guide earlier make vitamin a rend request .