SheetDream
Search…
API Tasks
The API Tasks data source allows you to make API requests to third party APIs, and to take the result and import it into your dataset.
This allows for much richer spreadsheets, populating them with information retrieved from APIs all across the web.
API Tasks also allow you to create phantom tables, which are tables that are not attached to a specific spreadsheet or Airtable. You can leverage phantom tables, the data from your preferred data source (Google Sheets or Airtable), and transforms to build powerful API endpoints that pull and join data from multiple different data sources.
First Look
At a first glance, there's obviously a number of fields involved with each API request you configure. We'll be covering those in more depth in the coming pages.
What we want to hone in on here in the beginning is the variety of buttons and actions you have at your disposal, so you can know your way around the API Tasks data source.
First things first, on the left hand side every API Task you configure will have a little X button next to it. This allows you to delete this particular action. Take note that just clicking the X button is not enough to perform the delete: you will also need to click Save Configuration at the bottom of the page. This is a common theme when making changes to anything in SheetDream, and it applies to the API Tasks you configure as well.
On the right hand side of each API you will see some arrow buttons. These allow you to re-organize your API Tasks so that they make more sense in the context of one another and for better readability.
Order of the API requests on this screen does not necessarily dictate the order the requests are executed in on the backend. For instance, you may have slightly different frequencies configured for specific API requests, or you may have disabled/enabled APIs over time causing the timings for each External API to change. Workflows and API tasks in SheetDream are data driven as opposed to flow driven (like you would see with tools like Integromat or Zapier).
Within the actual API Task you are configuring, you will notice a row of buttons on the top left hand side. More details on these buttons in the below table:
Button
Description
View Run Logs
As your API Task data sources run over time they will generate logs each time they run. This button pops open a modal populated with these logs, giving you a deeper view into what your API tasks are doing. Very useful for debugging.
Load Template
Pops open a modal allowing you to import any built-in templates or templates you've previously saved. More on templates below.
Save Template
Given your current API configuration, you can save it as a template by clicking this button. It will pop up a modal asking for you to name the template.
Templates
The template system is still rudimentary (changes will be coming soon!) but the intention is to allow you to work faster.
Usually when working with APIs you'll be repeating certain steps: namely with things like authentication and/or headers. Instead of forcing you to do these steps all over again for each subsequent API task you setup, templates allow you to just do it once.
For instance: let's say that you have several API tasks configured which communicate with the Slack API. The Slack API requires you to pass it an API key before it will authorize any work for you. You could use SheetDream's template system to save a template of the first Slack API request you write -- and then in future requests, use Load Template to populate just the authentication specific fields. In the case of Slack, it'd be the Header field which contains an Authorization field and the API key.
In the future, we will be overhauling the template system to include more built-in integrations to popular platforms, making it much easier for you to create API tasks without having to spend much more time on the nitty gritty details.
Features
SheetDream's API engine supports many very advanced features, allowing you to pull information from APIs of all shapes and sizes:
- Automatic OAuth authorization handling: SheetDream will take your refresh token(s) and will renew your access tokens for you automatically. Very useful in workflows where user authentication is required.
- Advanced paging handling: there are a series of common paging patterns you tend to see in APIs across the web, and SheetDream supports most of them! Paging is utilized by APIs to split up the returned results amongst multiple API calls. SheetDream has the logic baked in to navigate these API calls for you.
- Automation: API calls run at a frequency you specify. You can have them run once a minute, or you can have them run once every 30 days. This is why we define these requests as "tasks", as they run at periodic times.
- Transformation: SheetDream utilizes a technology called JSONata, a simple mapping language which can transform one JSON structure to another. It's like SQL or XPath, but far simpler and for JSON.
- Templates: If you find that a lot of the API calls you need to make are very similar sans a few changes in parameters, save time by creating a template from the current API definition. You can load this template in any other API definition to quickly get things up and running.
- Logging: Nothing is perfect, and things break sometimes. SheetDream keeps a log of your API calls so you can review what happened.
JSONata and The Mysterious $ Button
A lot of the power behind SheetDream's API handling comes from the data transformation side of things: and for that we use JSONata.
You will notice a $ button next to many different fields, and this button indicates that this particular field can use JSONata. Clicking that button pops up a modal covering most of your screen. Here's what that looks like:
This may look slightly daunting at first glance, but do not despair. It's actually extremely easy to use, and you'll sometimes find that JSONata almost writes itself.
On the left hand pane is the incoming data. In this particular scenario I clicked the $ button next to the URL field. So this incoming data is actually the datasets you have on SheetDream.
You can programmatically generate the API URL(s) you make a request out to, based directly on the data that you currently have in SheetDream. For example: you may want to do some sort of city-based API lookup on some service, and you want to do it for all of the cities listed in a particular spreadsheet.
The two right hand panes are:
- Your JSONata query in the top right pane
- The Result JSON (after running your JSONata query) in the bottom right pane
This modal is interactive, so as you write in the JSONata query box you will see the result change in realtime in the bottom pane.
When you're satisfied with how the resulting JSON looks, you can click Apply at the top right.
SheetDream will perform a series of checks to make sure that your resulting JSON makes sense for the particular field you are working with.
The URL field has different requirements than the Request Body field (for non-GET requests), and the Refresh Payload field (for OAuth requests) has different requirements than those, and so on.
SheetDream will try to determine if anything looks incorrect for you, guiding you along with the API definition process.
Confused about the JSONata syntax? Don't worry. We have a small guide available here, which also links out to several resources and videos to help you learn. We promise you can learn it and write something that works in under 1 hour. And if you're still struggling, drop us a line at [email protected] and we'll reach out to you and help.
Last modified 4mo ago
Copy link