Git Product home page Git Product logo

gspread's Introduction

Google Spreadsheets Python API v4

main workflow GitHub licence GitHub downloads documentation PyPi download PyPi version python version

Simple interface for working with Google Sheets.

Features:

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

Installation

pip install gspread

Requirements: Python 3.8+.

Basic Usage

  1. Create credentials in Google API Console

  2. Start using gspread

import gspread

gc = gspread.service_account()

# Open a sheet from a spreadsheet in one go
wks = gc.open("Where is the money Lebowski?").sheet1

# Update a range of cells using the top left corner address
wks.update('A1', [[1, 2], [3, 4]])

# Or update a single cell
wks.update('B42', "it's down there somewhere, let me take another look.")

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

v5.12 to v6.0 Migration Guide

Upgrade from Python 3.7

Python 3.7 is end-of-life. gspread v6 requires a minimum of Python 3.8.

Change Worksheet.update arguments

The first two arguments (values & range_name) have swapped (to range_name & values). Either swap them (works in v6 only), or use named arguments (works in v5 & v6).

As well, values can no longer be a list, and must be a 2D array.

- file.sheet1.update([["new", "values"]])
+ file.sheet1.update([["new", "values"]]) # unchanged

- file.sheet1.update("B2:C2", [["54", "55"]])
+ file.sheet1.update([["54", "55"]], "B2:C2")
# or
+ file.sheet1.update(range_name="B2:C2", values=[["54", "55"]])

Change colors from dictionary to text

v6 uses hexadecimal color representation. Change all colors to hex. You can use the compatibility function gspread.utils.convert_colors_to_hex_value() to convert a dictionary to a hex string.

- tab_color = {"red": 1, "green": 0.5, "blue": 1}
+ tab_color = "#FF7FFF"
file.sheet1.update_tab_color(tab_color)

Switch lastUpdateTime from property to method

- age = spreadsheet.lastUpdateTime
+ age = spreadsheet.get_lastUpdateTime()

Silence warnings

In version 5 there are many warnings to mark deprecated feature/functions/methods. They can be silenced by setting the GSPREAD_SILENCE_WARNINGS environment variable to 1

More Examples

Opening a Spreadsheet

# You can open a spreadsheet by its title as it appears in Google Docs
sh = gc.open('My poor gym results') # <-- Look ma, no keys!

# If you want to be specific, use a key (which can be extracted from
# the spreadsheet's url)
sht1 = gc.open_by_key('0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE')

# Or, if you feel really lazy to extract that key, paste the entire url
sht2 = gc.open_by_url('https://docs.google.com/spreadsheet/ccc?key=0Bm...FE&hl')

Creating a Spreadsheet

sh = gc.create('A new spreadsheet')

# But that new spreadsheet will be visible only to your script's account.
# To be able to access newly created spreadsheet you *must* share it
# with your email. Which brings us to…

Sharing a Spreadsheet

sh.share('[email protected]', perm_type='user', role='writer')

Selecting a Worksheet

# Select worksheet by index. Worksheet indexes start from zero
worksheet = sh.get_worksheet(0)

# By title
worksheet = sh.worksheet("January")

# Most common case: Sheet1
worksheet = sh.sheet1

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

Creating a Worksheet

worksheet = sh.add_worksheet(title="A worksheet", rows="100", cols="20")

Deleting a Worksheet

sh.del_worksheet(worksheet)

Getting a Cell Value

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

# With coords
val = worksheet.cell(1, 2).value

Getting All Values From a Row or a Column

# Get all values from the first row
values_list = worksheet.row_values(1)

# Get all values from the first column
values_list = worksheet.col_values(1)

Getting All Values From a Worksheet as a List of Lists

from gspread.utils import GridRangeType
list_of_lists = worksheet.get(return_type=GridRangeType.ListOfLists)

Getting a range of values

Receive only the cells with a value in them.

>>> worksheet.get("A1:B4")
[['A1', 'B1'], ['A2']]

Receive a rectangular array around the cells with values in them.

>>> worksheet.get("A1:B4", pad_values=True)
[['A1', 'B1'], ['A2', '']]

Receive an array matching the request size regardless of if values are empty or not.

>>> worksheet.get("A1:B4", maintain_size=True)
[['A1', 'B1'], ['A2', ''], ['', ''], ['', '']]

Finding a Cell

# Find a cell with exact string value
cell = worksheet.find("Dough")

print("Found something at R%sC%s" % (cell.row, cell.col))

# Find a cell matching a regular expression
amount_re = re.compile(r'(Big|Enormous) dough')
cell = worksheet.find(amount_re)

Finding All Matched Cells

# Find all cells with string value
cell_list = worksheet.findall("Rug store")

# Find all cells with regexp
criteria_re = re.compile(r'(Small|Room-tiering) rug')
cell_list = worksheet.findall(criteria_re)

Updating Cells

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

# Update a range
worksheet.update('A1:B2', [[1, 2], [3, 4]])

# Update multiple ranges at once
worksheet.batch_update([{
    'range': 'A1:B2',
    'values': [['A1', 'B1'], ['A2', 'B2']],
}, {
    'range': 'J42:K43',
    'values': [[1, 2], [3, 4]],
}])

Documentation

Documentation: https://gspread.readthedocs.io/

Ask Questions

The best way to get an answer to a question is to ask on Stack Overflow with a gspread tag.

Contributors

List of contributors

How to Contribute

Please make sure to take a moment and read the Code of Conduct.

Report Issues

Please report bugs and suggest features via the GitHub Issues.

Before opening an issue, search the tracker for possible duplicates. If you find a duplicate, please add a comment saying that you encountered the problem as well.

Improve Documentation

Documentation is as important as code. If you know how to make it more consistent, readable and clear, please submit a pull request. The documentation files are in docs folder, use reStructuredText markup and rendered by Sphinx.

Contribute code

Please make sure to read the Contributing Guide before making a pull request.

gspread's People

Contributors

burnash avatar alifeee avatar lavigne958 avatar dependabot[bot] avatar andrewbasem1 avatar msuozzo avatar cclauss avatar dgilman avatar fendse avatar candeira avatar yongrenjie avatar jlumbroso avatar chisvi avatar butvinm avatar laike9m avatar aiguofer avatar cgkoutzigiannis avatar idonec avatar mephinet avatar 2lambda123 avatar oskarbrzeski avatar thebestmensch avatar ccppoo avatar p-doyle avatar flantasticdan avatar sweep-ai[bot] avatar cstarner avatar timgates42 avatar rafa-guillermo avatar pizzapanther avatar

Watchers

 avatar

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.