- Notifications
You must be signed in to change notification settings - Fork39
Python library to interact with Google Sheets V4 API
License
socialpoint-labs/sheetfu
Folders and files
| Name | Name | Last commit message | Last commit date | |
|---|---|---|---|---|
Repository files navigation
Sheetfu was built to interacts with Google Sheets with a simple, intuitive, and fast API.The primary goal of this library is to adapt the Google App Script API for spreadsheets,to Python. With Sheetfu, you can easily get or set cell values, background colors, fontcolors or any other cell attributes.
Install and update usingpip:
pip install -U SheetfufromsheetfuimportSpreadsheetAppsa=SpreadsheetApp('path/to/secret.json')spreadsheet=sa.open_by_id('<insert spreadsheet id here>')sheet=spreadsheet.get_sheet_by_name('Sheet1')data_range=sheet.get_data_range()# returns the sheet range that contains data values.# this is how you get thingsvalues=data_range.get_values()# returns a 2D matrix of values.backgrounds=data_range.get_backgrounds()# returns a 2D matrix of background colors in hex format.# this is how you set thingsdata_range.set_background('#000000')# set every cell backgrounds to blackdata_range.set_font_color('#ffffff')# set every cell font colors to white
You can also create your SpreadsheetApp object with environment variablesinstead of the secrets.json file. You can refer tothe authentication tutorial for more info.
Please read thesheetfu API documentation for a more detailed description.
Sheetfu also contains a table module that abstracts completely the coordinatessystem for an ORM-like syntax. The example below is for a sheet with the 3columns 'name', 'surname' and 'age'.
fromsheetfuimportTablespreadsheet=SpreadsheetApp('path/to/secret.json').open_by_id('<insert spreadsheet id here>')data_range=spreadsheet.get_sheet_by_name('people').get_data_range()table=Table(data_range,backgrounds=True)foritemintable:ifitem.get_field_value('name')=='foo':item.set_field_value('surname','bar')# this set the surname field valueage=item.get_field_value('age')item.set_field_value('age',age+1)item.set_field_background('age','#ff0000')# this set the field 'age' to red color# Every set functions are batched for speed performance.# To send the batch update of every set requests you made,# you need to commit the table object as follow.table.commit()
You can refer to theTable API documentation for a more detailed description.
An effort has been made to guide Sheetu as a Google Sheet ORM, where any valuesfound in a spreadsheet are casted to a matching Python object. Since version1.5.7, Sheetfu returns DATE and DATE_TIME as Python datetime object.Similarly, setting a cell with a datetime object will make the necessaryparsing and casting to reflect those cells as DATE_TIME in the sheet.
fromsheetfuimportSpreadsheetAppsa=SpreadsheetApp('path/to/secret.json')spreadsheet=sa.open_by_id('<insert spreadsheet id here>')sheet=spreadsheet.get_sheet_by_name('Sheet1')# Assuming the cells are in DATE or DATE_TIME format.cells_with_dates=sheet.get_range_from_a1("A1:A2"))print(cells_with_dates.get_values())# [# [datetime.datetime(2021, 11, 26, 16, 58, 37, 737940)],# [datetime.datetime(2021, 11, 26, 16, 58, 37, 737940)]# ]
This means we can introduce python datetime operation in our code veryeffectively.
fromsheetfuimportSpreadsheetAppfromdatetimeimportdatetimesa=SpreadsheetApp('path/to/secret.json')spreadsheet=sa.open_by_id('<insert spreadsheet id here>')sheet=spreadsheet.get_sheet_by_name('Sheet1')a1=sheet.get_range_from_a1("A1")# The following will set today's date in the#cell in the right google sheet formata1.set_value(datetime.today())
For guidance on how to make a contribution to Sheetfu, see thecontributing guidelines.
- License:MIT
- Releases:https://pypi.org/project/sheetfu/
- Code:https://github.com/socialpoint-labs/sheetfu
- Issue tracker:https://github.com/socialpoint-labs/sheetfu/issues
If you are looking for the original sheetfu google apps script library, it has been relocated tothis page.
About
Python library to interact with Google Sheets V4 API