Hello everyone! Hopefully I'm not redoing any work previously done, but while working on my own project (building a backend system for processing tests in the biotech space by running automations in PipeDream), I couldn't find any existing Python libraries to help streamline working with AppSheet's API when interacting with databases. So, I went ahead and built one. I tried to make it as lightweight and simple to use as possible (only requires the requests library). GitHub . PyPI .
Any input/feedback is appreciated. There are definitely some limitations (e.g. "find" pulls all table rows and I'm not using any of the filtering/sorting capabilities in the API) and I'm sure some nitpicks. Contributions are also welcome. On a basic level, this is doing the job for me!
Why would you want to use this?
Working with API in Python is nice if you want to build out any automations to interact with AppSheet databases. I personally work with PipeDream to host my automations without needing to do any additional devops. Or you could use something like AWS Lambda, which is its lower-level equivalent.
For me: I'm leveraging AppSheet for its (very reasonably priced) hosted database to manage tracking purchases of my company's test, test kit inventory, and test-processing state end-to-end from checkout to result delivery.
Library Details
Installation: You can install this from Python's package manager
pip install py-appsheet
Setup:
To use the library, you'll need to create an AppSheet App and pull the App ID and generate an API Key in the App's settings. You'll also need to make sure to add the tables you want to interact with into the app and set the columns you want to use as keys accordingly.
from py_appsheet.client import AppSheetClient
# Step 1: Load environment variables from .env file
load_dotenv()
APPSHEET_APP_ID = <APP ID>
APPSHEET_API_KEY = <API KEY>
client = AppSheetClient(app_id=APPSHEET_APP_ID, api_key=APPSHEET_API_KEY)
Supported Methods:
- Find Items in a Table
- Search for a Specific Value in Any Column
result = client.find_item("Table Name", "ABC123")
- Search for a Specific Vaue in a Specific Column
result = client.find_item("Table Name", "ABC123", target_column="column name")
rows_to_add = [
{
"Generation Date": "1700000000",
"UserID": "someone@someone.com",
"Serial Number Hex": "ABC123",
"SKU": "SKU123",
"Batch": "Batch01",
},
{
"Generation Date": "1700000001",
"UserID": "john@doe.com",
"Serial Number Hex": "DEF456",
"SKU": "SKU456",
"Batch": "Batch02",
}
]
# Add rows to the AppSheet table
response = client.add_item("Inventory Table", rows_to_add)
# Process the response
print("Response from AppSheet API:", response)โ
# Example usage of edit_item
serial_number = "ABC123"
sku = "SKU456"
row_data = {
"Serial Number Hex": serial_number, # Key column for the table
"Bar Code": f"Inventory_Images/{serial_number}_serial_barcode.svg",
"QR Code": f"Inventory_Images/{serial_number}_serial_qr.svg",
"SKU Bar Code": f"Inventory_Images/{sku}_sku_barcode.svg"
}
response = client.edit_item("Inventory Table", "Serial Number Hex", row_data)
if response.get("status") == "OK":
print("Row updated successfully with image paths.")
else:
print(f"Failed to update row. API response: {response}")
โ
# Example: Delete a row by its key
# "Serial Number Hex" is key col name
response = client.delete_row("Inventory Table", "Serial Number Hex", "ABC123")
โ
You can see a full example here: https://github.com/GreatScott/py-appsheet/blob/main/examples/example.py
And additional documentation on the GitHub repo's main page: https://github.com/GreatScott/py-appsheet
LinkedIn
Twitter
