1. Products
  2.   Spreadsheet
  3.   Python
  4.   Gspread
 
  

Open Source Python API for Google Sheets

Create, Share, Parse & Modify Excel XML Spreadsheet Files via Open Source Python Library.

Gspread is an open source Python implementation for Google Sheets. The library enables computer programmers to create, open and modify a spreadsheet file from their own Python applications. More & more people are choosing to use online services like Google Sheets with each passing day.

Several important features are fully supported by the Gspread library, such as creating a new spreadsheet, selecting and sharing a spreadsheet, worksheet creation, deleting a worksheet, getting a cell value, getting values from a column as well as from a sheet row, searching a cell, updating a cell value, getting value from a cell as a list and many more.

Previous Next

Getting Started with Gspread

You need to have Python 3.6 or higher installed on your system to run Gspread smoothly. The recommended way to install via PIP. Please use the following command.

Install Gspread via PIP Command

 pip install gspread

Create & Share Spreadsheet via Python Library

The Gspread API provides features for new spreadsheet creation and modification. It allows software programmers to create a blank spreadsheet inside their own Python applications with just a few commands. Please remember that the new spreadsheet will be visible to the script's account only. For accessibly the newly created spreadsheet must be shared with your email. You can easily create a worksheet inside it.

Generate Spreadsheet & Add Worksheet to It via Python Library

 sh = gc.create('A new spreadsheet')
# Add a new worksheet to the list of current sheets
try:
        sheet = spreadsheet.worksheet(tab_label)

    except gspread.exceptions.WorksheetNotFound:
        spreadsheet.add_worksheet( tab_label, 1, len(col_defs) )
        sheet = spreadsheet.worksheet( tab_label )

Opening Spreadsheet via Python

The Open Source spreadsheet library Gspread allows software developers to Convert CSV Files to Excel 2003 XML file format with just a few lines of code. First of all, you need to load a CSV file from the server to be parsed and transfer data from parser to writer and change the writer type to XML. After that save the file with the specified name and specified target.

Opening Existing Spreadsheet via Python Library

# 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')

Parse Excel 2003 XML File

The Gspread library gives developers the capability to open an available spreadsheet by providing its title as it appears in Google Docs. To be precise you need to provide the key which can be taken from the URL of the spreadsheet. You can also provide the complete URL if you feel difficult to extract the key. You can also select a specific worksheet or list of all available worksheets.

Working with Spreadsheet Cells & Rows

It’s a very common practice to copy data from one or more worksheet cells to others. The Gspread API provides full support for manipulating cell data. You can easily get value from a cell or from spreadsheet rows and columns. The API also provides features getting all values from a worksheet as a list of lists. You can also search for a cell with exact value as well as find a cell equivalent with regular expression.

Apply Basic Formatting to Spreadsheet Cells via Python Library

# Set text format to bold:
worksheet.format('A1:B1', {'textFormat': {'bold': True}})

# Color the background of a Cell range in black
# change horizontal alignment, text color and font size

worksheet.format("A2:B2", {
    "backgroundColor": {
      "red": 0.0,
      "green": 0.0,
      "blue": 0.0
    },
    "horizontalAlignment": "CENTER",
    "textFormat": {
      "foregroundColor": {
        "red": 1.0,
        "green": 1.0,
        "blue": 1.0
      },
      "fontSize": 12,
      "bold": True
    }
})
 English