Google colab - using Google spreadsheet from Google drive - gspread

examples-colab-gspread.py

# reference: https://docs.gspread.org/en/latest/user-guide.html

# Install the required libraries
!pip install gspread google-auth

# Import libraries
import gspread
from google.colab import auth
from oauth2client.client import GoogleCredentials
from google.auth import default

# Authenticate and create a client
auth.authenticate_user()
creds, _ = default()
gc = gspread.authorize(creds)

#
# Open the Google
#
# Open the Google Sheets file (replace 'your_spreadsheet_name' with your file's name)
spreadsheet = gc.open("your_spreadsheet_name_here_without_folder")

# Open the Google Sheets file (by url)
spreadsheet2 = gc.open_by_url('https://docs.google.com/spreadsheet/ccc?key=0Bm...FE&hl')

# Open the Google Sheets file (by key)
spreadsheet3 = gc.open_by_key('0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE')


#
# Selecting a Worksheet
#

# (by sheet number)
sheet0 = spreadsheet.get_worksheet(0)
# (by sheet title)
sheet0 = spreadsheet.worksheet("January")


#
# Get values
#

# Get all values of row twenty
sheet0.row_values(20)

# Get value of specific cell by (cell name)
sheet0.acell("B7").value

# Get value of specific cell by (row number, column number)
sheet0.cell(7, 2).value


#
# Update values
#

# Update value of specific cell by (cell name)
sheet0.update_acell("B7", "Hi! I'm changing here!")

# Update value of specific cell by (row number, column number)
sheet0.update_cell(7, 2, "Hi! I'm changing here!")

#
# Finding Cells
#

# Finding a Cell by value
cell = worksheet.find("Dough")
print(f"Found something at Row: {cell.row} Column: {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
cell_list = worksheet.findall("Rug store")

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


#
# Managing files
#

# Listing sheets avaliable in file
spreadsheet.worksheets()

# Creating new spreadsheet file
newsheet = gc.create('A new spreadsheet')

# Creating sharing spreadsheet file
newsheet.share('[email protected]', perm_type='user', role='writer')

# Clear A Worksheet (one or multiple cells ranges at once)
worksheet.batch_clear(["A1:B1", "C2:E2", "my_named_range"])

# Clear the entire worksheet:
worksheet.clear()


#
# Data Validation
#

# Data Validation (strict validation to a cell)
ws.add_validation(
   'A1',
   ValidationConditionType.number_greater,
   [10],
   strict=True,
   inputMessage='Value must be greater than 10',
)

# Data validation with a drop down
worksheet.add_validation(
   'C2:C7',
   ValidationConditionType.one_of_list,
   ['Yes',
   'No',]
   showCustomUi=True
)

# https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#DataValidationRule
# https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#ConditionType


#
# Formatting
# 

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

# set color the background of A2:B2 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
    }
})

# https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#DataValidationRule
# https://docs.gspread.org/en/latest/community.html#gspread-formating-label


#
# Using gspread with Numpy
#

# Importing Google spreadsheet values to Numpy dataframe
import numpy as np
array = np.array(worksheet.get_all_values())

# Write the array to worksheet starting from the A2 cell
worksheet.update(array.tolist(), 'A2')


#
# Using gspread with Pandas
#

# Importing Google spreadsheet values to Pandas dataframe
import pandas as pd
dataframe = pd.DataFrame(worksheet.get_all_records())

# Exporting Pandas dataframe values to Google spreadsheet values
import pandas as pd
worksheet.update([dataframe.columns.values.tolist()] + dataframe.values.tolist())


#
# Advanced Pandas usage
#

# reference: https://github.com/aiguofer/gspread-pandas

!pip install gspread-pandas

import pandas as pd
from gspread_pandas import Spread

spread = Spread('Example Spreadsheet')

df = pd.DataFrame({}}

spread.df_to_sheet(df, index=False, sheet='New Test Sheet', start='A2', replace=True)
spread.update_cells('A1', 'B1', ['Created by:', spread.email])


# query folders 
from gspread_pandas import Client

client = Client()
# Assumming you have a dir called 'example dir' with sheets in it
available_sheets = client.find_spreadsheet_files_in_folders('example dir')
spreads = []
for sheet in available_sheets.get('example dir', []):
    spreads.append(Spread(sheet['id'], client=client))