Get external data via an Airtable formula?

Hi,
I’m new to Airtable programming, so I apologize if this is obvious for the most of you…
Is it possible to directly retrieve external data from another website (through their API) ?
I’m working on a personal movie db, and it would be nice, that if I give in a column the unique IMDB id, it will get me the IMDB rating automatically in another column ?
If not possible via Formula, how should I proceed ?
Thx for any helpfull reply

There is a rich environment of SaaS integration services out there. Two of the ones most frequently mentioned here are Zapier and Integromat — but if you wanted, you could spend the rest of 2019 doing nothing but evaluating competing options. Each has its own constellation of strengths and weaknesses: price, UI/UX, number of canned integrations, quality of documentation, polling cycle, and the like. Most work the same way: By providing pre-packaged endpoint integrations — for instance, to Airtable on one end and IMDB on the other, with both linked to a common data abstraction in the middle — and then allowing you to pass data through the central abstract data layer in the middle so as to complete the circuit.

I’ve never tried to integrate with IMDB, so I don’t know which services support that end-point. However, building a simple interface between Airtable and another supported site — and I’ve integrated with a number of Google services, the US Postal Service, and others — is typically less than an hour’s work the first time around.

Thank You W. for your clarification.
If I understood correctly their are ways to connect Airtable and another application by using the API from Airtable and using the API from the other system, but also using another tool (SaaS solution) to “glue” them together. But this happens at the “back” end I suppose ? There is no solution within Airtable to do this on the front end, like an action button, or formula, to get me the info ? Or even a refresh button to collect the latest result ?
I’ve experimented with the ODMB API which is RESTful web service to obtain movie information, and when I use it in a browser, it presents me all the info I needed in the browser in a structured way. But how I get this info in AT ? Is the only way to do this, in backend and through a SaaS solution ?

Well, the OMDB API provides you that data in either JSON or XML format; any structured presentation is the work of the browser or browser extension.

There is a way to get that information into Airtable: It’s called the Airtable API. But, like all APIs, it’s designed for a programmatic interface. If you want to bypass using a SaaS service, you can write the necessary code to shuffle data from one site to the other and back: That’s why APIs were invented, after all.

Does Airtable have its own JSON or XML gateway, one that allows you to format outgoing requests, receive responses, break down the responses in a structured manner, and store the resulting values in Airtable records? Sure! It’s called Zapier. :wink:

…or Integromat, or any of the other countless SaaS integrators. Could Airtable write its own? Quite possibly — but they are database experts, not integration experts. And while an API gets you 80% of the way there, it only gets you 80%. Each system with an API has its own eccentricities: Maximum calls per second, maximum requests per call; segmenting of large responses; blocking and flow control; error recovery; and the like. An Airtable-provided integration front-end would either have to be extremely agile — in the old-school sense — to be flexible enough to handle all contingencies, or it would risk not supporting an important subset of sites. Quite possibly you would reach a point where development resources that should be going to maintain and enhance the core product would instead be spent on maintaining an interface to a handful of sites used by a handful of customers.

…or they could punt: Let Zapier, et al., deal with the headache of maintaining bidirectional interfaces with thousands of systems, and concentrate their integration efforts on providing a useful and robust API, to allow integration services to make as efficient use of Airtable as possible.

I must confess to having started out thinking the way you do: Why let another company make money off your customers? It made no sense to me that people were using paid Zapier plans to automate free Airtable accounts. But as I became more deeply involved with hybrid systems that tied Airtable together with a handful of other business apps, the trade off of short-term gains against long-term expenses began to make more sense. The value to the user/developer/consultant is immediately obvious the first time one is called upon to swap out, say, an integration with IMDB for one with OMDB, or GoodReads instead of Google Books: The transition can be nearly seamless, merely a matter of mapping different incoming field names to the target Airtable fields. (Sometimes, if field names aren’t different, the integration is virtually as simple as pointing to a different gateway.)

Now, I’m not sure any of the major integration services have a dedicated OMDB interface available. But, as you said, it’s a well-structured and seemingly well-maintained API. (Although it does appear to be rather frequently updated, so you may find yourself having to tweak transactions on occasion; I haven’t dug around enough in the documentation to see if you can specify a desired API revision level.) All major SaaS integrators have generic JSON and XML gateways, so you should be able to build your own interface to OMDB, if required, in under an hour: enter your API key, point the service at OMDB, have it download a schema, and you should be ready to start pulling data into Airtable.

1 Like

Thank you once again W,
I fully understand the added value of these SaaS tools. I even created an account with Zapier, and as far as I can discover, it seems pretty easy to transfer data to Airtable form google sheets, Evernote, Gmail, etc, and a whole bunch of other apps. All you have to do is create a zap , a trigger , an action , and the rest Zapier will take care of. So, that’s the good part, and I’m convinced of the added value.
But what should I do if the app, or the website with the API, is NOT in the list of supported apps ? Like OMDB for instance .
As I’m very new in this world of connections through API’s and SaaS, do you have any idea where I can find any “starters” info ? Or an example ?
Any help strongly appreciated.

Sure!

I just spent 90 minutes throwing together this 15-minute Zap :wink: — but that is my own fault. This is one of the simplest Airtable → Zapier → Airtable Zaps possible, as it simply gets a field from the triggering record (in this case the movie title), sends a request to OMDB, receives a response, and stores the returned values back into the same Airtable record. There are far more complex Zaps possible — for instance, searching based on some other criteria, receiving a page of responses, parsing the data from each response, and using it to create a linked record in a different Airtable table — but they all use essentially the same Zapier building blocks, just arranged in different ways. This first Zap is a good start to build familiarity.

Note: On Zapier, you’ll need a paid account to run this multi-step Zap, but you can build and test it for free, using their trial period. (Other integration services might have different plan offerings, but I built this on Zapier because I’m more familiar with their offering.) I’ll describe it in brief detail, so if you have any questions, don’t hesitate to ask.

First, you’ll have to set up an account with Zapier — which I see you’ve done. You’ll also need to create a record with example data to use while building the Zap. For this example, I’m going to request a record from OMDB based on the film title. (As I’d just been telling a friend about watching my father jokingly swap insults with Wendell Scott, I’m using the 1977 Scott bio-pic, Greased Lighting, as my test case.) Now, to build the Zap:

  1. For your triggering app, select ‘Airtable’. The trigger will depend on your need; for the test, I chose ‘New record in table.’ Specify the base and table. You’ll find Zapier will take a moment during each step to load information needed for the next step, so the process is pretty intuitive. Zapier will issue a request to Airtable and return a number of records; select the one that includes the proper test value.

  2. Once the trigger is complete, select ‘Webhooks by Zapier’ as your action. While the Zapier webhook module supports a number of incoming webhooks as triggers, in this instance we’ll be using only one of the outgoing actions it supports, the ‘GET’ request, with query string parameters. (The OMDBAPI.com site outlines the possible parameters, and the ‘examples’ section allows you to provide several variables and have it return an example formatted request.) For this Zap, we need to tell Zapier

    1. the URL to use (http://omdbapi.com/),
    2. the search criteria ('t', for ‘title’, followed by the film title), and
    3. your apikey

    The URL is entered into the Zapier module’s ‘URL’ field; the other two variables go into its ‘Query String Params’ section. Each entry in this section has two fields, arranged side-by-side. The parameter key ('t' or 'apikey') goes into the first field; the value you want goes into the second. In this case, you want to enter the API key OMDB sent when you signed up for the service as the hard-coded value for 'apikey'. The value for 't' is a little different, though: Since it changes for each record, you want to tell Zapier to use the title passed to it by the Airtable trigger. To do so, select the ‘menu’ drop-down indicator beside the second field. You’ll see a single entry: ‘1 [Airtable logo] New Record’, indicating the trigger, and below it a list of the field values passed to Zapier. Select the one corresponding to the movie title (in my case, {Name}). (The default values are fine for the other parameters. Make sure the ‘Send as JSON’ toggle is off; while we want the response to be returned as JSON, we want it sent as a standard HTML ‘GET’.)

    Once you’ve entered the parameters, click ‘Continue’. Zapier will test this configuration by sending a test request to OMDB; with luck, you’ll see a test result containing all the fields OMDB shows when you run a search from its ‘example’ section using Greased Lightning as the test title.

  3. Finally, you need to add another action to your Zap; this will cause Zapier to update your Airtable record with the desired data. Choose ‘Airtable’ as the app and ‘Update Record’ as the action. Specify the same base and table as you did for the trigger; for ‘record’, though, click the menu drop-down and select ‘Use a custom value (advanced)’. A new field will appear, labeled ‘Custom Value for Record ID’; clicking its menu drop-down will display entries for the ‘New Record’ trigger and the ‘Get’ action. Select ‘New Record’ and, from the drop-down of passed values, choose ‘Record id’. The Action field will be updated with the actual Airtable RECORD_ID() of the record used as a test trigger — but when the Zap is run, it will pick up the ID of whichever record triggered the Zap.

    You’re now ready to specify which values from the response to the ‘GET’ request are stored in which Airtable fields. For each Airtable value you wish to update, click the menu drop-down, select the webhooks ‘Get’ action as the source, and choose which OMDB field you wish to save to Airtable. For my test, I defined a half-dozen fields — {Date}, {Release date}, {MPAA rating}, {IMDB ID}, {Actors}, and the like — and mapped the returned values accordingly. Zapier will show the values returned from the test trigger in the fields, but in practice they, too, will be replaced with the values actually received from OMDB.

  4. Your last step is to test the ‘Update Record’ action — which will happen as soon as you click ‘continue’ after configuring the action. If you tab back to your open Airtable view quickly enough, you’ll notice all the fields modified by Zapier are outlined in green and have been updated with the values from OMDB. If that’s the case, your Zap is finished and working correctly: Give it a name, and flip the toggle to enable it. From now on, adding a new record to that table in your base will trigger Zapier, which will then go out, query OMDB, and update your base accordingly.

One final note: In Airtable’s case, the term ‘trigger’ is a bit of a misnomer. Because Airtable does not currently support webhooks itself, it does not actually send a message to Zapier informing it a new record has been added. Instead, Zapier polls Airtable on a regular basis, checking for new records. The interval between polls is determined by your Zapier plan level — but the thing to keep in mind is it is not instantaneous. If you update Airtable and nothing seems to happen, as far as Zapier-automated updates go, make sure you’ve waited long enough for the polling cycle to take place.

I hope this helps. If you have any questions, reply here or PM me.


Oh, and what mistake did I make that took me more than an hour to debug? I always forget Zapier’s webhook module isn’t very adroit when it comes to parsing XML-formatted responses. However, since my brain is more XML-friendly than JSON-friendly, I typically ask for XML responses instead of JSON when I have a choice. (In this case it’s a silly worry, as I never have to deal with either XML or JSON: By the time Zapier shows me a key:value pair, it has already broken it down into component pieces, presenting me with the actual values, instead.) Typically, I go long enough between having to configure a Zapier webhook to forget this — and inevitably spin my wheels trying to figure out why my outgoing 'GET’s are evidently properly formatted, as I don’t get an ‘unknown key’ error message — but the incoming payload is always null. This time, it took an hour before I got around to removing '&r=xml' from the query string; once I did, Zapier immediately and painlessly parsed the JSON response, and everything went swimmingly from there.

Moral of the story: Always ask for a JSON response to a Zapier webhook ‘GET’ request…

2 Likes

Amazing W !!!
This is far more than a link to a help page or manual. This is a comprehensive step by step guide, with one you can’t go wrong at all ! I just followed it very thoroughly, struggled a little bit with the interface, but finally got it to work !
I explored a whole new world of technology and it is just there to grab it on the net.
So thank you so much for your patience and effort to help me out.
My deepest appreciation !

Glad I could help – and glad to learn of the OMDB API…