Using SheetDB with Python: A Comprehensive Guide

If you’re looking to manipulate your Google Sheets data programmatically using Python, SheetDB provides an excellent solution. SheetDB lets you change your Google Spreadsheets into a JSON-based API that’s easy to integrate into your Python code, making your programming tasks more straightforward and efficient.

But before we dive in, you need to ensure that the ‘requests’ library is installed in your Python environment. If it isn’t, use this pip command to install it:

pip install requests

(Note: You may need to use “pip3” if your Python version is 3.x.)

GET: Retrieving Data

SheetDB’s RESTful API has the ability to return your data as a JSON response via a GET HTTP request. By utilizing the Python requests library, extracting data from your sheet comes like second nature.

import requests

response = requests.get("https://sheetdb.io/api/v1/58f61be4dda40")
data = response.json()

print(data)

SheetDB’s GET endpoint supports various options like limit, offset, sorting, and even number casting, which you can provide as parameters to the request. More details here: https://docs.sheetdb.io/sheetdb-api/read

POST: Adding Data

Get Chris Switalski’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Adding new data to your spreadsheet can be done effortlessly using SheetDB’s POST endpoint. Here’s how it’s done in Python:

import requests

data = {
  "data": {
    "id": "6",
    "name": "I ❤️ python",
    "age": "25",
  }
}
response = requests.post(
    "https://sheetdb.io/api/v1/58f61be4dda40",
    json=data
)

The POST endpoint also allows special values like INCREMENT, TIMESTAMP, and DATETIME. Details here: https://docs.sheetdb.io/sheetdb-api/create

PATCH: Updating Data

SheetDB supports the PATCH HTTP method for modifying existing data in your spreadsheet. Here’s a simple Python example:

import requests

data = {
  "data": {
    "name": "Updated by Python"
  }
}
requests.patch(
    "https://sheetdb.io/api/v1/58f61be4dda40/id/5",
    json=data
)

Remember to replace https://sheetdb.io/api/v1/58f61be4dda40/{name}/{value} in the requests.patch function with the name and value of the column you want to change, in this case, id=5.

DELETE: Removing Data

Deleting data from your spreadsheet is no hardship with SheetDB’s DELETE endpoint. Here’s an example:

import requests

requests.delete(
    "https://sheetdb.io/api/v1/58f61be4dda40/id/5",
    json=data
)

SheetDB delivers excellent versatility, not only by providing a DELETE endpoint for single queries, but also for deleting all duplicate records.

With all of these examples, remember to replace the dummy data with your own data. This guide ought to arm you with a solid starting point for using SheetDB with Python. For further understanding and additional information, our comprehensive API documentation is readily available SheetDB API documentation.

Happy Coding!