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

Open Source Python Library for Google Spreadsheets

Python API allows to Create and Modify Google Spreadsheets, Add a New Set of Cells or Rows, Apply Formatting to Rows or Cells, Protect Spreadsheets, and many more.

What is Pygsheets?

Pygsheets is a simple Python library that gives software developers the capability to create and handle Google spreadsheets using Python commands. The library is very stable and makes it easy for developers to create a new spreadsheet and modify an existing one. It helps developers to handle most of the spreadsheet-related tasks with just a couple of lines of code. Advanced features like applying conditional formatting to cells or rows and getting value are fully supported and easy to handle.

The Pygsheets library has provided a set of advanced as well as basic features for handling Google spreadsheets, such as creating, editing or deleting spreadsheets using a title or key, adding new worksheet, adding new set of cells or rows, applying formatting to a set of cells, spreadsheet protection, setting cell format, texting format, apply color to a range of cells, adding notes to a worksheet, named ranges support, data validation support, using checkboxes or drop-downs, getting value from a cell, updating a cell value and much more.

Previous Next

Getting Started with Pygsheets

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

Install Pygsheets via PIP Command

pip install pygsheets

The other way install is from GitHub. Please use the following command.

Install Pygsheets via GitHub

pip install https://github.com/nithinmurali/pygsheets/archive/staging.zip

Manage Worksheet using Python API

The open source Pygsheets library enables software applications to handle worksheets inside their Google spreadsheet. It has provided several important functions for handling worksheets such as creating a new worksheet, inserting cells into a sheet, renaming a worksheet, updating the content of a worksheet, getting a value of a particular row or column, updating a range of values with a cell list or matrix, insert a new row in a place of your choice, resize rows or columns and more.

How to Apply Excel Workbook Formatting to Columns via Python API?

model_cell = pygsheets.Cell("A1")

model_cell.set_number_format(
    format_type = pygsheets.FormatType.PERCENT,
    pattern = "0%"
)
# first apply the percentage formatting
pygsheets.DataRange(
    left_corner_cell , right_corner_cell , worksheet = wks
 ).apply_format(model_cell)

# now apply the row-colouring interchangeably
gray_cell = pygsheets.Cell("A1")
gray_cell.color = (0.9529412, 0.9529412, 0.9529412, 0)

white_cell = pygsheets.Cell("A2")
white_cell.color = (1, 1, 1, 0)

cells = [gray_cell, white_cell]

for r in range(start_row, end_row + 1):
    print(f"Doing row {r} ...", flush = True, end = "\r")
    wks.get_row(r, returnas = "range").apply_format(cells[ r % 2 ], fields = "userEnteredFormat.backgroundColor")

Open and Edit a Spreadsheet via Python

The Pygsheets library makes it easy for software programmers to access and open a spreadsheet inside their own Python applications. The library has provided multiple ways to open a spreadsheet. You can open a spreadsheet by its title as it appears in Google Docs with just one liner code. You can also open a spreadsheet by providing a key. It is possible to share the spreadsheet with other team members and make changes to it according to your need.

How to Open and Modify a Spreadsheet via Python API?

import pygsheets
import numpy as np

gc = pygsheets.authorize()

# Open spreadsheet and then worksheet
sh = gc.open('my new sheet')
wks = sh.sheet1

# Update a cell with value (just to let him know values is updated ;) )
wks.update_value('A1', "Hey yank this numpy array")
my_nparray = np.random.randint(10, size=(3, 4))

# update the sheet with array
wks.update_values('A2', my_nparray.tolist())

# share the sheet with your friend
sh.share("myFriend@gmail.com")

Protecting a Worksheet via Python

The open source Pygsheets library has provided functionality for protecting a particular worksheet from unintended changes inside their Python applications. It has included support for protecting whole worksheet or certain range of cells and let users make changes to most of the spreadsheet as per their requirements.

How to Protect an Whole sheet via Python API?

r = Datarange(worksheet=wks)
r # this is a datarange unbounded on both indexes

r.protected = True # this will make the whole sheet protected
 English