Making API requests from your spreadsheets

With Quadratic, you can connect APIs to spreadsheets in a programmatic way to access live data from any data source exposed via an API endpoint. You can read from APIs with GET requests and write to APIs with POST requests, all directly from Quadratic's native Python experience.

Connecting your spreadsheet to your APIs is easy. We'll start with the simplest possible API, with no credentials or parameters, and then move to one with credentials and parameters.

Step 1: identify your API endpoint

In this example we're using a free sample API from JSONPlaceholder.

The endpoint is:

https://jsonplaceholder.typicode.com/users

We can view what some of that sample data looks like in our browser.

Step 2: build query and view in spreadsheet

This is the simplest possible query as there are no parameters, only a URL that returns our data.

import requests
import pandas as pd

# make request
x = requests.get('https://jsonplaceholder.typicode.com/users')

# go from JSON response to DataFrame
df = pd.DataFrame.from_dict(x.json())

# display DataFrame in the sheet
df

This returns our results, which are tabular in the spreadsheet.

Step 3: manipulate the DataFrame

From here, we can manipulate the DataFrame, doing statistics, visualizations, or selecting individual pieces of data. Here are just a few examples:

Select a column

df['username']

This returns only the username column to the sheet.

Summarize the column's data

We can do analytics on our selected data. In this example, we count the number of times a username starts with a specific letter. This would have taken a bit to figure out, so instead, we can just ask the AI assistant in the console. On the first try, the AI performed an answer that worked great.

Make a chart

With our summarized data from the last step, we can make a simple chart showing each letter's occurrence rate, such as the starting letter in a username.

# import plotly
import plotly.express as px

# create your chart type, for more chart types: https://plotly.com/python/
fig = px.bar(df, x = letter, y = frequency)

# make chart prettier
fig.update_layout(
    plot_bgcolor="White",
    height=700
)

# display chart
fig.show()

API requests with parameters (plus POST instead of GET)

For this next example, we connect to Exa AI, a search AI API. The same steps are used above, just with query parameters, changing our API request slightly. It's also a POST instead of a GET request, meaning we'll POST data, and then they'll respond with an answer to what we're looking for. POST requests are also a common way to write data to external sources, where the response is a confirmation of whether or not the data was successfully received. In this case, we're sending data and receiving an answer using our POST request.

In this example, we build our request from parameters set in the sheet so we can dynamically make requests to the API based on sheet data.

Building API request with parameters

We need to follow the parameters specifications in the API's documentation. In the Exa example, we must provide the query, category, and number of results as the payload; as headers, we need to minimally supply our API key and the data type we will receive. See the example below for how this works.

import requests
import pandas as pd

# API URL
url = "https://api.exa.ai/search"

# payload
payload = {
    # adjusts our query to an optimized prompt
    "useAutoprompt": True,
    # returns 10 results
    "numResults": 10,
    # category of response we want
    "category": "company",
    # question we want to ask, read from spreadsheet cell (0,3)
    "query": cell(0,3)
}

# headers
headers = {
    # what data format we'll accept
    "accept": "application/json",
    # what data format we're sending
    "content-type": "application/json",
    # API key
    "x-api-key": "your_api_key_here",
}

# send request
response = requests.post(url, json=payload, headers=headers)
# turn response into json format we can shove into DataFrame
response.json()['results']
# put request into DataFrame
df = pd.DataFrame.from_dict(response.json()['results'])
# dislay DataFrame to sheet
df

Summary

API requests are a powerful way to connect to live data sources from your spreadsheet—query all sorts of data sources directly from Python in Quadratic to level up your analytics.

Feel free to contact us and let us know how or what we can improve in Quadratic. User feedback is what directly guides our product roadmap.