Transforms
Transforms are an advanced feature of SheetDream that allow you to build custom endpoints using JSONata queries.
Their general purpose is as follows:
- Having complete control over the returned data from the API endpoint (headers, HTTP status code, request body)
- Allowing you to perform complex joins and transformations of your sheet data (across multiple different sheets). Instead of forcing you to do the transformations on Google Sheets or Airtable and syncing that to SheetDream, this allows you to keep Google Sheets and Airtable relatively clean and instead let JSONata work its magic
Transforms only support GET requests: you cannot mutate or change any data with this feature. A good way to think of them are as follows: go and grab some data for me, but before you return it, I also want you to apply a few transformations to it.
Creating a transforms table is done through the exact same process you would for a webhooks table: except instead of naming the table webhooks, you name it transforms. SheetDream will pick up on this and will give this table the special transforms treatment when you query against it using your API:
As with webhooks, there are a few required fields for this table:
Field Name | Field Description |
|---|---|
response | JSONata expression that will evaluate the returned result. The resulting JSON from this expression has strict requirements (see below). |
public | Either true or false. Controls whether or not you want this transform to be available publicly (e.g. do not need to pass in a user's API token) |
The response JSONata expression has strict requirements on what it should evaluate to. It should evaluate to an object that looks like the following:
{
"statusCode": 200, // can be any status code, just must be a number
"headers": {
... // any set of HTTP headers can be put here
},
"body": "" // can be string or JSON
}
The environment that gets passed in to your JSONata expression will look like the following, as an example:
{
...queryStringParams,
"table1": [
{ ... },
{ ... },
{ ... }
],
"table2": [
{ ... },
{ ... },
{ ... }
]
}
Where queryStringParams are the parameters that are passed to your endpoint after the ? character in the transforms URL, and table1 and table2 are fictional tables that we're syncing from Google Sheets or Airtable.
So as an example, let's say that I've already setup a table called "Contacts" from Airtable in the Data Sources tab. If I were to query this URL:
/transforms/:transform_id?param1=hello¶m2=world
Here is what the resulting environment will look like for the JSONata expression:
{
"param1": "hello",
"param2": "world",
"Contacts": [
{ ... },
{ ... },
{ ... }
]
}
Last modified 1yr ago