Skip to main content

Google Sheets

The Google Sheets importData command imports data at from a given url in .csv format. You can use it to load data from a UDF into a Google Sheets cell.

alt text

1. Create a UDF in Fused Hosted API​

Create a UDF that returns a table then save it on Workbench to automatically create an endpoint.

This example retrieves Caltrain live location data from GTFS realtime feed, and returns it as a dataframe.

@fused.udf
def udf():
    import pandas as pd
    import requests

    r = requests.get(f"https://www.caltrain.com/files/rt/vehiclepositions/CT.json")
    j = r.json()
    df = pd.json_normalize(j["Entities"])
    return df

2. Create a URL for the UDF​

Now, create a shareable Tile HTTP endpoint for the UDF.

Append this query parameter to the end of the URL to structure the response as a CSV type: ?dtype_out_vector=csv.

The generated URL should look like this:

https://www.fused.io/server/v1/realtime-shared/940c7d75bb3f12f2c411beeee7293729d35a9429ebb3760df29fa84c3166b7b9/run/file?dtype_out_vector=csv

3. Call the UDF in a cell​

Paste the importData function with the UDF endpoint in a cell.

=importData("https://www.fused.io/server/v1/realtime-shared/940c7d75bb3f12f2c411beeee7293729d35a9429ebb3760df29fa84c3166b7b9/run/file?dtype_out_vector=csv")

When you enter this formula into a cell, Google Sheets will call the UDF, and the returned dataframe will be loaded into the spreadsheet.

warning

Google Sheets' importData imposes data size constraints that will require the UDF to return a conservative number of rows. Otherwise it may show the following error message.

File