gsheeter 0.3.0

Google spreadsheet Python API Abstractor integrated with pandas

Google sheets API For Python3 v1

Pandas-integrated, Auto-positioning library for Google Sheet API

Features:

• Auto-positioning for value updates on sheets
• Designed to minimize API calls
• Auto-parse values on sheet to get tables with positions and pd.DataFrame interpretations
• Multi-level indexes and columns supported

1. Installation

pip install gsheeter

Requires Python 3.9+.

2. Basic Usage

1. Authentication (Only service account usage is allowed so far, I will develop functions to use other auth methods in the future)

# using a service account will create a global api client for the project to use throughout
import gsheeter
filename = 'service-account.json' # add a path of your service account json file
gsheeter.service_account(filename)

2. Load spreadsheet

from gsheeter import Drive

# 1. using fileId
fileId = 'spreadsheetFileId' # use the file id of the spreadsheet you want to access

spreadsheet = Drive.get_spreadsheet(fileId)

# 2. using filename
filename = 'spreadsheetFileName' # use the name of the spreadsheet you want to access
parentId = 'folderId' # use id of the parent folder of the spreadsheet above, I recommend using folderId when using filename, not fileId

spreadsheet = Drive.get_spreadsheet(
  target=filename,
  folderId=parentId)

3. Add(Create) spreadsheet

from gsheeter import Drive

filename = 'yourspeadsheet' # use the name you want
sheetname = 'default' # default value is None, use only if you want to create the first sheet with a specific name
parentId = 'anyFolderId' # if empty, spreadsheet will be replaced in your root
spreadsheet = Drive.create_spreadsheet(
  filename=filename,
  sheetname=sheetname,
  parentId=parentId)

4. Load sheet

from gsheeter import Drive


spreadsheet = Drive.get_spreadsheet(
  target='test',
  folderId='parentFolderId')

# 1. using sheetId
sheetId = 0
sheet = spreadsheet.get_sheet(sheetId)

# 2. using sheetname
sheetname = 'Sheet1'
sheet = spreadsheet.get_sheet(sheetname)

You can also use kwargs as follows when using Spreadsheet.get_sheet:

• delete_exist: bool = False: if the sheet being searched already exists and delete_exist is set to True, delete the existing sheet and create another one, otherwise, return the existing sheet

# example
sheet = spreadsheet.get_sheet(sheetname, delete_exist=True)

• add: bool = True: if the sheet being searched does not exist, add one

# example, throws exception if the sheet does not exist or is not added
sheet = spreadsheet.get_sheet(sheetname, add=False)

5. Add(Create) sheet

from gsheeter import Drive

spreadsheet = Drive.get_spreadsheet(
  target='test',
  folderId='parentFolderId'
)

# default function behavior, Create a sheet with sheetname "Sheet1", 1000 rowCount and 26 columnCount and index of 0
sheet = spreadsheet.add_sheet()

# with a sheetname
sheetname = 'new_sheet'
sheet = spreadsheet.add_sheet(sheetname)

# create a smaller sheet with 100 rows and 10 columns
sheet = spreadsheet.add_sheet(
  sheetname=sheetname,
  rowCount=10,
  columnCount=10,
)
# after creating a new sheet named "new_sheet", with 10 rows and 10 columns

6. Read sheet values: entire sheet

from gsheeter import Drive

fileId = 'fileId'
spreadsheet = Drive.get_spreadsheet(fileId)
sheetname = 'Sheet1'
sheet = spreadsheet.get_sheet(sheetname)
values = sheet.matrix # returns 2D np.ndarray filled with Values.
# If the sheet is empty, An empty np.ndarray with size of (sheet.rowCount, sheet.columnCount)

7. Read sheet values: tables

from gsheeter import Drive

fileId = 'fileId'
spreadsheet = Drive.get_spreadsheet(fileId)
sheetname = 'Sheet1'
sheet = spreadsheet.get_sheet(sheetname)
tables = sheet.tables

for t in tables:
  print(t)

8. Read sheet values: a table

from gsheeter import Drive

fileId = 'fileId'
spreadsheet = Drive.get_spreadsheet(fileId)
sheetname = 'Sheet1'
sheet = spreadsheet.get_sheet(sheetname)
table = sheet.table # by default, the first table(table #1) is assigned to sheet.table

9. Update values: using sheet

from gsheeter import Drive

fileId = 'fileId'
spreadsheet = Drive.get_spreadsheet(fileId)
sheetname = 'Sheet1'
sheet = spreadsheet.get_sheet(sheetname)

import numpy as np

# all coordinates follow array indexing convention, starting from 0
# gsheeter adds 1 to each coordinate to match cell address on sheet
# 1. set values of 2D np.ndarray with x, y coordinate on sheet
arr = np.zeros(shape=(4, 3))
y_offset = 0 # row 1
x_offset = 0 # column 1(A)
sheet.set_values(
  data=arr,
  y_offset=y_offset,
  x_offset=x_offset)

# 2. set values of 1D np.ndarray with x and y coordinate on sheet
arr = np.array([0,1,2,3,4])
y_offset = 1 # row 2
x_offset = 2 # column 3(C)
sheet.set_values(
  data=arr,
  y_offset=y_offset,
  x_offset=x_offset)

# 3. set values of pd.DataFrame with x and y coordinate on sheet
df = pd.DataFrame(...)
y_offset = 0
x_offset = 0
sheet.set_values(
  data=df,
  y_offset=y_offset,
  x_offset=x_offset)

# 4. set values of pd.Series
row = pd.Series(...)
# default values of y_offset and x_offset are 0
# if the input to .set_values() is of type pd.Series, the output value is transposed.
sheet.set_values(row)

# 5. append values at the next row after the last non-empty row
# x_offset determines the index at which the search for non-empty row starts
arr = np.zeros(shape=(4, 3))
sheet.set_values(
  data=arr,
  x_offset=1, # searches for the next empty row after the last non-empty row starting from x coordinate of 1 to x coordinate of 1 + width of input data
  append=True)

arr = np.zeros(shape=(4, 3))
sheet.set_values(
  data=arr,
  x_offset=1,
  y_offset=1, # paste the input data 1 index array along y-axis from the last-fill row
  append=True
)

10. Update values: replace values in a row of a table

from gsheeter import Drive

fileId = 'fileId'
spreadsheet = Drive.get_spreadsheet(fileId)
sheet = spreadsheet.get_sheet('Sheet1')
table = sheet.table
row = table.df.iloc[2] # select third row from this table
row['x'] = 'test'
row['y'] = 'test'
table.update_row(row) # then update the values of the row

11. Update values: delete a row of a table

from gsheeter import Drive

fileId = 'fileId'
spreadsheet = Drive.get_spreadsheet(fileId)
sheet = spreadsheet.get_sheet('Sheet1')
table = sheet.table

# delete second row
row = table.df.iloc[1]
table.delete_row(row)

12. Update values: delete a table

from gsheeter import Drive

fileId = 'fileId'
spreadsheet = Drive.get_spreadsheet(fileId)
sheet = spreadsheet.get_sheet('Sheet1')
table = sheet.tables[2] # select third table in the sheet
sheet.delete_table(table)
sheet.delete_table(1) # you can also use table index

Advanced Usage and Object structures

1. Drive
2. Spreadsheet
3. Sheet
4. Table

Future updates

1. Chart
2. Other authentication methods
3. Cell format