Integrating Airtable with DronaHQ Studio

Airtable is a spreadsheet-database hybrid, with the features of a database but the power of a spreadsheet. DronaHQ Studio provides integration with Airtable that allows you to manage the Airtable tables and keep track of anything and everything.

You can make use of the Airtable Integration for a host of areas like Marketing campaign planning, Design project tracking, team collaboration contributing to Knowledge Base, and many more.

Note: It is important to note that Airtable has paused granting access to their metadata API. While waiting for access, this integration requires a couple of manual steps.

Configuring Airtable connector

The Airtable Third Party connector is available under Connectors.

To configure the account, under Authenticate Airtable, click +Add Account.

If you have already configured your account, you simply need to choose it from the list and click Continue.

Here you can also Edit the configured account or delete an existing configured account.

  • Now Configure connector fields. Add a Connector name. Then add the respective Fields for the connector account.
  • Once all details are added, click Finish. Your connector configuration is now done.

Using Airtable connector

Now let us consider a few scenarios to understand some of the functionalities available with this integration. Let us take the example to add the Airtable connector in an Action flow. The connection and authentication process would be the same whether you use an action flow or workflow.

  • For using the Airtable connector go to the Action flow and under Server-side actions > Ready Third-party connectors, select the Airtable connector.
  • Under Select from Airtable, select specific Action and click Continue.
  • Add the authenticated account and click Continue.

Note that in case you make any changes to the Airtable table name or even the Base, you should reconfigure your connector so that it is updated with the current data.

Create a row

The integration with Airtable enables you to add a new record to the Airtable table. For the sake of an example let us add a new record to the Airtable table “Marketing” under the Marketing team base created here.

Airtable does not provide a meta API to get a list of columns, hence we will fetch 1 row from Airtable. Airtable returns a list of columns that are filled. So it is important to remember that for a new table, make sure that you add 1 row for all the columns to be shown in Add Row connector. You can then add values for all the columns of that row in Airtable to be able to configure all the columns here.

Let us for this example add Static values. You can also make use of Keywords to add the dynamic values as well on form submission or on an action event. In this example, let us add the new row with the click of the Action button to Add the record.

  • To create a new row, under Select from Airtable, select Create row and click Continue.

  • Add the Base ID (copied from Airtable) and enter the Table name.

  • Once you add the table name the columns in the table would be listed out. You can then enter the static values or the dynamic values using the Keywords.

  • On adding the details, click Continue. Enter the Action Unique name and you are done.

A few things to remember when adding a new row using this connector:

  • It supports simple key values, nested keys like those for the Attachment column type are not supported. To enable adding new records, such columns should be kept empty or you can create a custom function or JS Code to prepare data in the expected format.
  • If you have a formula column, that should also be kept empty.
  • You can create custom functions to insert columns that have nested key values.

For instance - Below is the expected format required for Attachment Column type for creating record

In this case, we can have a below JSCode block that can we add an output variable and use it in our Airtable Attachment keyword field. Here - url is the input to js code that can be from existing file url you have or can be from File Upload Action block output url.

    output = [];

    if(typeof url === 'string') {
       var obj = {};
       obj["url"] = url;
    } else if(typeof url === 'array') {
       for(let i=0; i<url.length; i++) {
  	       var obj = {};
  	       obj["url"] = url[i];

similarly, we can support other column formats which aren’t supported out of the box.

Find row / rows

There would be times when you would want to search a record as a part of your update or delete row action. Typically you search the row or rows matching criteria and fetch their record-id which would then be used to specify the record to be updated or deleted.

  • To find a row/rows, select the appropriate connector as required.
  • Select the connected Airtable account and click Continue.
    • Column to search: Is the column from the Table to be searched. You can type the name or use a keyword.
    • Value to search: Is the Value to be searched in the specified column. Can also be a dynamic value
    • Search Direction: Search the table from top to bottom or from the bottom to the top.
    • Filter by Formula: Airtable formula to be added to filter data. Typically useful for advanced users. You can find the detailed documentation for supported formulas here.
    • Base ID: Base ID is the ID for the respective Base you are using in Airtable. The ID is available in the Help > API Documentation of your Airtable base. You can copy it from the documentation and paste it here.
    • Table name: Is the table name from the specified Base.
    • Sort: You can provide formulas, to sort records by a specific attribute in a certain order, for example pass in: [{“field”: “Emp Name”, “direction”: “desc”}]
    • Transform keys: You can add transformation of the key values of your connector. Say for example you want to add certain keys by modifying the connector response then you can use the Column transformation. For more details refer to this article here.
  • After the necessary input is provided add the action unique name and also make sure that you add Variable to return values. The single or multiple values for the record id would be available as “” depending upon the action used; find the row or find rows. This response can then be used in other connectors.

The Airtable API returns only those keys of the row in the API response whose value is present whereas Studio expects uniform/consistent keys present in the API response to be able to work on those keys. So you can either make sure all the cells in Airtable are filled which you want to display or you can write a custom javascript function that can give a consistent output.

Update Rows

Now let us consider an example to update an existing row. When updating records you would first need to use the Find Row / Rows function to find the record-id of the row to be updated. This value returned would then be used to update the rows for the specific record-id.

  • To update a row, under Select from Airtable, first select Find a row / Find rows as seen earlier.

  • Now to update the rows, from the Server-side action > Airtable connector, select the Update row action and add the authenticated account.

  • Add the Base Id and the Table name. Now using the keywords, add the search record id that was returned by the Find row/ rows.

  • Once you add the table name the columns in the table would be listed out. You can then enter the static values or the dynamic values using the Keywords for only those columns that you want to update. Leave the other columns blank so that they would be left with the existing values.

  • Add action unique name and click Finish.

  • Now if you want to update multiple rows that you had searched and fetched in the Find rows action earlier, then you need to iterate rows option in the Update Rows API.

  • Now whenever you initiate the action the column where you added the data would be updated.

Delete rows

  • To delete a row, under Select from Airtable, first select Find a row / Find rows as seen earlier.

  • Now to delete the row/s, from the Server-side action > Airtable connector, select the Delete row action and add the authenticated account.

  • Add the Base Id and the Table name. Now using the keywords, add the search record id that was returned by the Find row/ rows. Click Continue.

  • Add the action unique name and conditions if any. Click Finish. Now whenever you trigger the action flow, the matching record as per the given condition would be deleted.

  • Now if you want to delete multiple rows that you had searched and fetched in the Find rows action earlier, then you need to iterate rows option in the Delete Rows API.

View Airtable data

You can make use of the controls like the table grid control to view the data from Airtable tables. There is no direct API that can allow you to view all rows. You would have to configure an Airtable connector to ViewAll Rows. Add the Column to Search and the Value to search for according to the data you want to fetch.
Now say you want to display it in a table grid control, then bind the data, using the Connectors > +Airtable connector. You would typically have to use the functions like MAPKEYS() to display them. Optionally, you can also make use of the Refresh Response feature that enables you to get responses from custom connected accounts and map the keys to bind data to table grid control or other control. For more information refer to the article here.

Now if you want to fetch all rows where the Status is Todo, you need to provide the Column to Search as Status and Value to search for as Todo. Here all rows matching the search condition would be fetched.

You can then use the functions as shown in the example below.

Showing all data from the Table

We have seen how we could get all records matching the criteria. However, now in case you want to display all the rows from the Airtable table, then, in that case, you cannot do so directly. So to enable you to get the complete data from the table, you can make use of a column that has the default or common value which can then be provided as the parameter for the Column to search. This column is typically added as a Formula type column which gets the default value. Provide this column name as Column to search and the respective common value as the Value to Search for and then configure the remaining fields of the connector.

You can also use the Format Data Option to visually enhance the data of single select or JSON object types. Depending upon your column type you can display data better. For example, here we would differentiate the single tags with a particular color formatting.

format data airtable

format data

Show data of a specific record

You can make use of controls like the detailed card to view data of a specific record from the Airtable tables by using its record ID.
After connecting your Airtable table you can find the endpoint of FindRowById, and select it.

Choose the environment and select your configured Airtable connector. Then fill in the details.

  • Record ID: This is the ID of the record whose details you want to fetch and use in the control. This can be acquired from the table In Airtable by using RECORD_ID() formula. Also, this can be retrieved by Get/Find Rows API which we are available in the studio

Select columns to bind then click Test and Finish.

Now all the record details for that particular record ID will be displayed in the detailed card control.

Configuring Airtable Account

Now when you are adding a new account you need to use your Airtable account authentication method. Airtable has a simple token-based authentication. You can find all your account details in the Airtable account page. When configuring the Airtable connector following are the details that you would need.

  • Add a Tag for your account name. This will be the name that would be shown in the list of accounts.

  • API Key: Is a mandatory field. You can find your API key on your Airtable account page under Account overview. Click and copy the key to the API Key field.

  • Base ID: Is a mandatory field. To find your Airtable Base ID, open the Airtable API page and click the Base that you want to use.

  • You will find the ID of your base in the Introduction section.

    Copy and paste the Base ID.

  • Table name: Now to extract data you need to provide the table name from your Airtable Base. Make sure that you type it as it is in the Base.

Once all the above details are added click Submit.