# Google Spreadsheets Python API v4
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
```sh
pip install gspread
```
Requirements: Python 3.8+.
## Basic Usage
1. [Create credentials in Google API Console](http://gspread.readthedocs.org/en/latest/oauth2.html)
2. Start using gspread
```python
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](https://devguide.python.org/versions/). 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.
```diff
- 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"]])
```
### More
<details><summary>See More Migration Guide</summary>
### 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.
```diff
- 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
```diff
- age = spreadsheet.lastUpdateTime
+ age = spreadsheet.get_lastUpdateTime()
```
### Replace method `Worksheet.get_records`
In v6 you can now only get *all* sheet records, using `Worksheet.get_all_records()`. The method `Worksheet.get_records()` has been removed. You can get some records using your own fetches and combine them with `gspread.utils.to_records()`.
```diff
+ from gspread import utils
all_records = spreadsheet.get_all_records(head=1)
- some_records = spreadsheet.get_all_records(head=1, first_index=6, last_index=9)
- some_records = spreadsheet.get_records(head=1, first_index=6, last_index=9)
+ header = spreadsheet.get("1:1")[0]
+ cells = spreadsheet.get("6:9")
+ some_records = utils.to_records(header, cells)
```
### 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`
### Add more data to `gspread.Worksheet.__init__`
```diff
gc = gspread.service_account(filename="google_credentials.json")
spreadsheet = gc.open_by_key("{{key}}")
properties = spreadsheet.fetch_sheet_metadata()["sheets"][0]["properties"]
- worksheet = gspread.Worksheet(spreadsheet, properties)
+ worksheet = gspread.Worksheet(spreadsheet, properties, spreadsheet.id, gc.http_client)
```
</details>
## More Examples
### Opening a Spreadsheet
```python
# 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
```python
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
```python
sh.share('otto@example.com', perm_type='user', role='writer')
```
### Selecting a Worksheet
```python
# 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
```python
worksheet = sh.add_worksheet(title="A worksheet", rows="100", cols="20")
```
### Deleting a Worksheet
```python
sh.del_worksheet(worksheet)
```
### Getting a Cell Value
```python
# With label
val = worksheet.get('B1').first()
# With coords
val = worksheet.cell(1, 2).value
```
### Getting All Values From a Row or a Column
```python
# 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
```python
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.
```python
>>> worksheet.get("A1:B4")
[['A1', 'B1'], ['A2']]
```
Receive a rectangular array around the cells with values in them.
```python
>>> 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.
```python
>>> worksheet.get("A1:B4", maintain_size=True)
[['A1', 'B1'], ['A2', ''], ['', ''], ['', '']]
```
### Finding a Cell
```python
# 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
```python
# 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
```python
# 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/][Documentation]
[Documentation]: https://gspread.readthedocs.io/en/latest/
### Ask Questions
The best way to get an answer to a question is to ask on [Stack Overflow with a gspread tag](http://stackoverflow.com/questions/tagged/gspread?sort=votes&pageSize=50).
## Contributors
[List of contributors](https://github.com/burnash/gspread/graphs/contributors)
## How to Contribute
Please make sure to take a moment and read the [Code of Conduct](https://github.com/burnash/gspread/blob/master/.github/CODE_OF_CONDUCT.md).
### Report Issues
Please report bugs and suggest features via the [GitHub Issues](https://github.com/burnash/gspread/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](https://gspread.readthedocs.io/) 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`](https://github.com/burnash/gspread/tree/master/docs) folder, use [reStructuredText](http://www.sphinx-doc.org/en/master/usage/restructuredtext/index.html#rst-index) markup and rendered by [Sphinx](http://www.sphinx-doc.org/).
### Contribute code
Please make sure to read the [Contributing Guide](https://github.com/burnash/gspread/blob/master/.github/CONTRIBUTING.md) before making a pull request.
Raw data
{
"_id": null,
"home_page": "",
"name": "gspread",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.8",
"maintainer_email": "Alexandre Lavigne <lavigne958@gmail.com>, alifeee <alifeee.web@outlook.com>",
"keywords": "spreadsheets,google-spreadsheets,google-sheets",
"author": "",
"author_email": "Anton Burnashev <fuss.here@gmail.com>",
"download_url": "https://files.pythonhosted.org/packages/9c/c1/5ff71f99409917c6d096b3ead94b060a6f26e7cdda0606ed855a9831365d/gspread-6.0.1.tar.gz",
"platform": null,
"description": "# Google Spreadsheets Python API v4\n\n\n\n\n\n\n\n\n\nSimple interface for working with Google Sheets.\n\nFeatures:\n\n- Open a spreadsheet by **title**, **key** or **URL**.\n- Read, write, and format cell ranges.\n- Sharing and access control.\n- Batching updates.\n\n## Installation\n\n```sh\npip install gspread\n```\n\nRequirements: Python 3.8+.\n\n## Basic Usage\n\n1. [Create credentials in Google API Console](http://gspread.readthedocs.org/en/latest/oauth2.html)\n\n2. Start using gspread\n\n```python\nimport gspread\n\ngc = gspread.service_account()\n\n# Open a sheet from a spreadsheet in one go\nwks = gc.open(\"Where is the money Lebowski?\").sheet1\n\n# Update a range of cells using the top left corner address\nwks.update('A1', [[1, 2], [3, 4]])\n\n# Or update a single cell\nwks.update('B42', \"it's down there somewhere, let me take another look.\")\n\n# Format the header\nwks.format('A1:B1', {'textFormat': {'bold': True}})\n```\n\n## v5.12 to v6.0 Migration Guide\n\n### Upgrade from Python 3.7\n\nPython 3.7 is [end-of-life](https://devguide.python.org/versions/). gspread v6 requires a minimum of Python 3.8.\n\n### Change `Worksheet.update` arguments\n\nThe 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).\n\nAs well, `values` can no longer be a list, and must be a 2D array.\n\n```diff\n- file.sheet1.update([[\"new\", \"values\"]])\n+ file.sheet1.update([[\"new\", \"values\"]]) # unchanged\n\n- file.sheet1.update(\"B2:C2\", [[\"54\", \"55\"]])\n+ file.sheet1.update([[\"54\", \"55\"]], \"B2:C2\")\n# or\n+ file.sheet1.update(range_name=\"B2:C2\", values=[[\"54\", \"55\"]])\n```\n\n### More\n\n<details><summary>See More Migration Guide</summary>\n\n### Change colors from dictionary to text\n\nv6 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.\n\n```diff\n- tab_color = {\"red\": 1, \"green\": 0.5, \"blue\": 1}\n+ tab_color = \"#FF7FFF\"\nfile.sheet1.update_tab_color(tab_color)\n```\n\n### Switch lastUpdateTime from property to method\n\n```diff\n- age = spreadsheet.lastUpdateTime\n+ age = spreadsheet.get_lastUpdateTime()\n```\n\n### Replace method `Worksheet.get_records`\n\nIn v6 you can now only get *all* sheet records, using `Worksheet.get_all_records()`. The method `Worksheet.get_records()` has been removed. You can get some records using your own fetches and combine them with `gspread.utils.to_records()`.\n\n```diff\n+ from gspread import utils\n all_records = spreadsheet.get_all_records(head=1)\n- some_records = spreadsheet.get_all_records(head=1, first_index=6, last_index=9)\n- some_records = spreadsheet.get_records(head=1, first_index=6, last_index=9)\n+ header = spreadsheet.get(\"1:1\")[0]\n+ cells = spreadsheet.get(\"6:9\")\n+ some_records = utils.to_records(header, cells)\n```\n\n### Silence warnings\n\nIn version 5 there are many warnings to mark deprecated feature/functions/methods.\nThey can be silenced by setting the `GSPREAD_SILENCE_WARNINGS` environment variable to `1`\n\n### Add more data to `gspread.Worksheet.__init__`\n\n```diff\n gc = gspread.service_account(filename=\"google_credentials.json\")\n spreadsheet = gc.open_by_key(\"{{key}}\")\n properties = spreadsheet.fetch_sheet_metadata()[\"sheets\"][0][\"properties\"]\n- worksheet = gspread.Worksheet(spreadsheet, properties)\n+ worksheet = gspread.Worksheet(spreadsheet, properties, spreadsheet.id, gc.http_client)\n```\n\n</details>\n\n## More Examples\n\n### Opening a Spreadsheet\n\n```python\n# You can open a spreadsheet by its title as it appears in Google Docs\nsh = gc.open('My poor gym results') # <-- Look ma, no keys!\n\n# If you want to be specific, use a key (which can be extracted from\n# the spreadsheet's url)\nsht1 = gc.open_by_key('0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE')\n\n# Or, if you feel really lazy to extract that key, paste the entire url\nsht2 = gc.open_by_url('https://docs.google.com/spreadsheet/ccc?key=0Bm...FE&hl')\n```\n\n### Creating a Spreadsheet\n\n```python\nsh = gc.create('A new spreadsheet')\n\n# But that new spreadsheet will be visible only to your script's account.\n# To be able to access newly created spreadsheet you *must* share it\n# with your email. Which brings us to\u2026\n```\n\n### Sharing a Spreadsheet\n\n```python\nsh.share('otto@example.com', perm_type='user', role='writer')\n```\n\n### Selecting a Worksheet\n\n```python\n# Select worksheet by index. Worksheet indexes start from zero\nworksheet = sh.get_worksheet(0)\n\n# By title\nworksheet = sh.worksheet(\"January\")\n\n# Most common case: Sheet1\nworksheet = sh.sheet1\n\n# Get a list of all worksheets\nworksheet_list = sh.worksheets()\n```\n\n### Creating a Worksheet\n\n```python\nworksheet = sh.add_worksheet(title=\"A worksheet\", rows=\"100\", cols=\"20\")\n```\n\n### Deleting a Worksheet\n\n```python\nsh.del_worksheet(worksheet)\n```\n\n### Getting a Cell Value\n\n```python\n# With label\nval = worksheet.get('B1').first()\n\n# With coords\nval = worksheet.cell(1, 2).value\n```\n\n### Getting All Values From a Row or a Column\n\n```python\n# Get all values from the first row\nvalues_list = worksheet.row_values(1)\n\n# Get all values from the first column\nvalues_list = worksheet.col_values(1)\n```\n\n### Getting All Values From a Worksheet as a List of Lists\n\n```python\nfrom gspread.utils import GridRangeType\nlist_of_lists = worksheet.get(return_type=GridRangeType.ListOfLists)\n```\n\n### Getting a range of values\n\nReceive only the cells with a value in them.\n\n```python\n>>> worksheet.get(\"A1:B4\")\n[['A1', 'B1'], ['A2']]\n```\n\nReceive a rectangular array around the cells with values in them.\n\n```python\n>>> worksheet.get(\"A1:B4\", pad_values=True)\n[['A1', 'B1'], ['A2', '']]\n```\n\nReceive an array matching the request size regardless of if values are empty or not.\n\n```python\n>>> worksheet.get(\"A1:B4\", maintain_size=True)\n[['A1', 'B1'], ['A2', ''], ['', ''], ['', '']]\n```\n\n### Finding a Cell\n\n```python\n# Find a cell with exact string value\ncell = worksheet.find(\"Dough\")\n\nprint(\"Found something at R%sC%s\" % (cell.row, cell.col))\n\n# Find a cell matching a regular expression\namount_re = re.compile(r'(Big|Enormous) dough')\ncell = worksheet.find(amount_re)\n```\n\n### Finding All Matched Cells\n\n```python\n# Find all cells with string value\ncell_list = worksheet.findall(\"Rug store\")\n\n# Find all cells with regexp\ncriteria_re = re.compile(r'(Small|Room-tiering) rug')\ncell_list = worksheet.findall(criteria_re)\n```\n\n### Updating Cells\n\n```python\n# Update a single cell\nworksheet.update('B1', 'Bingo!')\n\n# Update a range\nworksheet.update('A1:B2', [[1, 2], [3, 4]])\n\n# Update multiple ranges at once\nworksheet.batch_update([{\n 'range': 'A1:B2',\n 'values': [['A1', 'B1'], ['A2', 'B2']],\n}, {\n 'range': 'J42:K43',\n 'values': [[1, 2], [3, 4]],\n}])\n```\n\n## Documentation\n\n[Documentation]\\: [https://gspread.readthedocs.io/][Documentation]\n\n[Documentation]: https://gspread.readthedocs.io/en/latest/\n\n### Ask Questions\n\nThe best way to get an answer to a question is to ask on [Stack Overflow with a gspread tag](http://stackoverflow.com/questions/tagged/gspread?sort=votes&pageSize=50).\n\n## Contributors\n\n[List of contributors](https://github.com/burnash/gspread/graphs/contributors)\n\n## How to Contribute\n\nPlease make sure to take a moment and read the [Code of Conduct](https://github.com/burnash/gspread/blob/master/.github/CODE_OF_CONDUCT.md).\n\n### Report Issues\n\nPlease report bugs and suggest features via the [GitHub Issues](https://github.com/burnash/gspread/issues).\n\nBefore 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.\n\n### Improve Documentation\n\n[Documentation](https://gspread.readthedocs.io/) 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`](https://github.com/burnash/gspread/tree/master/docs) folder, use [reStructuredText](http://www.sphinx-doc.org/en/master/usage/restructuredtext/index.html#rst-index) markup and rendered by [Sphinx](http://www.sphinx-doc.org/).\n\n### Contribute code\n\nPlease make sure to read the [Contributing Guide](https://github.com/burnash/gspread/blob/master/.github/CONTRIBUTING.md) before making a pull request.\n\n",
"bugtrack_url": null,
"license": "",
"summary": "Google Spreadsheets Python API",
"version": "6.0.1",
"project_urls": {
"Documentation": "https://gspread.readthedocs.io/en/latest/",
"Source": "https://github.com/burnash/gspread"
},
"split_keywords": [
"spreadsheets",
"google-spreadsheets",
"google-sheets"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "9ae8fbfb53c7e82f9a306f39c8864115658c97b6b86c6e9699a75f6e34190392",
"md5": "1758224f598ab59ee90fe72a5ed9c685",
"sha256": "6c3af32b753fe75d9dd513ea9e088e9e043e09b9e3bf04d61d77213f37e67b79"
},
"downloads": -1,
"filename": "gspread-6.0.1-py3-none-any.whl",
"has_sig": false,
"md5_digest": "1758224f598ab59ee90fe72a5ed9c685",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.8",
"size": 53915,
"upload_time": "2024-02-06T14:15:19",
"upload_time_iso_8601": "2024-02-06T14:15:19.666715Z",
"url": "https://files.pythonhosted.org/packages/9a/e8/fbfb53c7e82f9a306f39c8864115658c97b6b86c6e9699a75f6e34190392/gspread-6.0.1-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "9cc15ff71f99409917c6d096b3ead94b060a6f26e7cdda0606ed855a9831365d",
"md5": "0cc5fed712199cba7a89302752b63d57",
"sha256": "8c8bf83be676a019d3a483455d8b17b442f2acfc620172f245422ca4fc960dd0"
},
"downloads": -1,
"filename": "gspread-6.0.1.tar.gz",
"has_sig": false,
"md5_digest": "0cc5fed712199cba7a89302752b63d57",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.8",
"size": 73337,
"upload_time": "2024-02-06T14:15:21",
"upload_time_iso_8601": "2024-02-06T14:15:21.922517Z",
"url": "https://files.pythonhosted.org/packages/9c/c1/5ff71f99409917c6d096b3ead94b060a6f26e7cdda0606ed855a9831365d/gspread-6.0.1.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-02-06 14:15:21",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "burnash",
"github_project": "gspread",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"tox": true,
"lcname": "gspread"
}
JSON
