# 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([[1, 2], [3, 4]], "A1")
# Or update a single cell
wks.update_acell("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_acell('B1', 'Bingo!')
# Update a range
worksheet.update([[1, 2], [3, 4]], 'A1:B2')
# Update multiple ranges at once
worksheet.batch_update([{
'range': 'A1:B2',
'values': [['A1', 'B1'], ['A2', 'B2']],
}, {
'range': 'J42:K43',
'values': [[1, 2], [3, 4]],
}])
```
### Get unformatted cell value or formula
```python
from gspread.utils import ValueRenderOption
# Get formatted cell value as displayed in the UI
>>> worksheet.get("A1:B2")
[['$12.00']]
# Get unformatted value from the same cell range
>>> worksheet.get("A1:B2", value_render_option=ValueRenderOption.unformatted)
[[12]]
# Get formula from a cell
>>> worksheet.get("C2:D2", value_render_option=ValueRenderOption.formula)
[['=1/1024']]
### Add data validation to a range
```python
import gspread
from gspread.utils import ValidationConditionType
# Restrict the input to greater than 10 in a single cell
worksheet.add_validation(
'A1',
ValidationConditionType.number_greater,
[10],
strict=True,
inputMessage='Value must be greater than 10',
)
# Restrict the input to Yes/No for a specific range with dropdown
worksheet.add_validation(
'C2:C7',
ValidationConditionType.one_of_list,
['Yes',
'No',]
showCustomUi=True
)
```
## 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": null,
"name": "gspread",
"maintainer": null,
"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": null,
"author_email": "Anton Burnashev <fuss.here@gmail.com>",
"download_url": "https://files.pythonhosted.org/packages/48/76/d23fcdef5d8137513b64f7447849369983078e4025100d9149d5c8222e5a/gspread-6.1.4.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([[1, 2], [3, 4]], \"A1\")\n\n# Or update a single cell\nwks.update_acell(\"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_acell('B1', 'Bingo!')\n\n# Update a range\nworksheet.update([[1, 2], [3, 4]], 'A1:B2')\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### Get unformatted cell value or formula\n\n```python\nfrom gspread.utils import ValueRenderOption\n\n# Get formatted cell value as displayed in the UI\n>>> worksheet.get(\"A1:B2\")\n[['$12.00']]\n\n# Get unformatted value from the same cell range\n>>> worksheet.get(\"A1:B2\", value_render_option=ValueRenderOption.unformatted)\n[[12]]\n\n# Get formula from a cell\n>>> worksheet.get(\"C2:D2\", value_render_option=ValueRenderOption.formula)\n[['=1/1024']]\n### Add data validation to a range\n\n```python\nimport gspread\nfrom gspread.utils import ValidationConditionType\n\n# Restrict the input to greater than 10 in a single cell\nworksheet.add_validation(\n 'A1',\n ValidationConditionType.number_greater,\n [10],\n strict=True,\n inputMessage='Value must be greater than 10',\n)\n\n# Restrict the input to Yes/No for a specific range with dropdown\nworksheet.add_validation(\n 'C2:C7',\n ValidationConditionType.one_of_list,\n ['Yes',\n 'No',]\n showCustomUi=True\n)\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": null,
"summary": "Google Spreadsheets Python API",
"version": "6.1.4",
"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": "47c728ad61476626d635288d1671326f6540145778a5f2fc9f907ad4d929ba90",
"md5": "32687af323187f48df523f5e4a29077b",
"sha256": "c34781c426031a243ad154952b16f21ac56a5af90687885fbee3d1fba5280dcd"
},
"downloads": -1,
"filename": "gspread-6.1.4-py3-none-any.whl",
"has_sig": false,
"md5_digest": "32687af323187f48df523f5e4a29077b",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.8",
"size": 57574,
"upload_time": "2024-10-20T22:17:53",
"upload_time_iso_8601": "2024-10-20T22:17:53.906186Z",
"url": "https://files.pythonhosted.org/packages/47/c7/28ad61476626d635288d1671326f6540145778a5f2fc9f907ad4d929ba90/gspread-6.1.4-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "4876d23fcdef5d8137513b64f7447849369983078e4025100d9149d5c8222e5a",
"md5": "fb89187c5414547bcf49b1a09fb13932",
"sha256": "b8eec27de7cadb338bb1b9f14a9be168372dee8965c0da32121816b5050ac1de"
},
"downloads": -1,
"filename": "gspread-6.1.4.tar.gz",
"has_sig": false,
"md5_digest": "fb89187c5414547bcf49b1a09fb13932",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.8",
"size": 78251,
"upload_time": "2024-10-20T22:17:55",
"upload_time_iso_8601": "2024-10-20T22:17:55.678282Z",
"url": "https://files.pythonhosted.org/packages/48/76/d23fcdef5d8137513b64f7447849369983078e4025100d9149d5c8222e5a/gspread-6.1.4.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-10-20 22:17:55",
"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
