Today, most organizations are facing the problem of not being “organized”. Poor organization is one of the factors that can hinder business focus. It causes poor communication among organizational teams and sporadic thinking. The end result of this is low morale and low productivity. The goal of Airtable is to solve this problem. Airtable facilitates collaboration among users in an organization.
When using Airtable, you will want to extract insights from the generated data. One of the ways to do this is by visualizing the data. However, Airtable is not a good tool for data visualization. You need to move data from Airtable to another tool like Tableau and visualize it. This way, you’ll be able to extract insights from the data for proper decision-making. In this article, you will learn about Airtable and the procedure to set up your Airtable Tableau Integration.
Table of Contents
Introduction to Airtable
Airtable is an online database that a group of collaborators can use to store, share, and edit information. The information, in this case, can be inventory, ideas, tasks, etc.
It’s true that you can put information about a project on an online spreadsheet like Google Sheets and share it with others for viewing and editing. The spreadsheet can contain basic information like names of people and their contact information, or sensitive information like financial accounting data.
Airtable is similar to such spreadsheets, but it comes with two major differences. First, with Airtable, you can manage databases instead of sheets. The major difference between databases and spreadsheets is that databases are a bit complex. Secondly, Airtable is a user-friendly tool, hence, you must not be an expert in using Excel to use Airtable. See Airtable as an online platform where a group of people can collaborate on creating and editing databases. It comes with many templates that can guide you on how to use the system.
Pre-Requisites for Airtable Tableau Integration
This is what you need for this article:
- Airtable Account.
- Tableau Desktop/Tableau Server/Tableau Online.
Procedure to Implement Airtable Tableau Integration
Method 1: Airtable Tableau Integration using Google Sheets
There is no direct way of integrating Airtable and Tableau manually. However, you can use Google sheets as an intermediary to transfer data between the two platforms. You can sync your Airtable account to Google Sheets and move your Airtable Data to Google Sheets.
You can then move data from Google Sheets into Tableau for visualization. Let us demonstrate these two processes:
Airtable Tableau Integration Part 1: Syncing Airtable to Google Sheets
Follow the steps given below:
Step 1: Create a Google Sheets template
First, sign into your Google account. Create a new spreadsheet to hold data from your Airtable base.
Give the spreadsheet a name that makes sense to you.
Step 2: Add the Tables to be copied from Airtable
Open the Airtable base that you need to copy data from. Select the table whose data is to be visualized. Click the “VIEWS” icon located at the top right corner of the window.
Copy the URL of the view which closely resembles something like this:
https://airtable.com/tblf2OjtW3FwYb10j/viwZHOHKL7I2b611N?blocks=hide
In the above case, the Airtable View ID is the part that starts with “view” and ends before the “?”. In the above case, it is viwZHOHKL7I2b611N.
Step 3: Add Code to your Spreadsheet
On your Spreadsheet, click the “Tools” button from the top and select “Script editor”.
This will open the script editor window. Delete the default code from it.
We can then write some script that can be used to sync data between Airtable and Google Sheets. Here is the code:
var api_key = "keyXXXXXXXXXXXX"; //ADD YOUR AIRTABLE API KEY HERE
var baseID = "appXXXXXXXXXXXX"; //ADD YOUR BASE ID HERE
var tablesToSync_fromSheetRange = "A2:B6"; //UPDATE TABLE CELL RANGE HERE (for the tables to be synced)
////////// create UI menu items///////////
function onOpen(e) {
SpreadsheetApp.getUi()
.createMenu('Sync Airtable to Google Sheets')
.addItem('Sync all data manually', 'syncData')
.addToUi();
}
////////// The function that will trigger the data syncing process ///////////
function syncData(){
//fetch table names from the control panel of the spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var tablesToSync = ss.getSheetByName("Control panel").getRange(tablesToSync_fromSheetRange).getValues();
//sync all tables
for (var i = 0; i<tablesToSync.length; i++){
var tableName = tablesToSync[i][0];
var viewID = tablesToSync[i][1];
var airtableData = fetchDataFromAirtable(tableName, viewID);
pasteDataToSheet(tableName, airtableData);
//wait for some time so that Airtable doesn't limit the rate
Utilities.sleep(201);
}
}
/////////////////////////////
function saveFormulas(dataSheets){
//add control panel to the data-related sheets
dataSheets.push("Control panel");
//initialise the object to hold the formulas
var formulas = {};
//get all sheets in spreadsheet
var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
//iterate over the sheets
for (var i in sheets){
var sheetName = sheets[i].getSheetName();
if (dataSheets.indexOf(sheetName) == -1){
formulas[sheetName] = sheets[i].getRange("A:Z").getFormulas();
}
}
return formulas;
}
////////// paste airtable data into a sheet ///////////////////////////
function pasteDataToSheet(sheetName, airtableData){
//define the field schema
var fieldNames = ["Record ID"];
for (var i = 0; i<airtableData.length; i++){
for (var field in airtableData[i].fields){
fieldNames.push(field);
}
}
//remove duplicates from the field names array
fieldNames = fieldNames.filter(function(item, pos){
return fieldNames.indexOf(item)== pos;
});
//select the sheet to be updated, or create new one if it doesn't exist
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet;
if (ss.getSheetByName(sheetName) == null){
sheet = ss.insertSheet(sheetName);
} else {
sheet = ss.getSheetByName(sheetName);
}
//clear the sheet data
sheet.clear();
//add the field names to the sheet as row headers, then format the headers
var headerRow = sheet.getRange(1,1,1,fieldNames.length);
headerRow.setValues([fieldNames]).setFontWeight("bold").setWrap(true);
sheet.setFrozenRows(1);
//add the Airtable record IDs to the first column of every row
for (var i = 0; i<airtableData.length; i++){
sheet.getRange(i+2,1).setValue(airtableData[i].id);
}
//// add the other data to the rows ////
//for every record in the Airtable data...
for (var i = 0; i<airtableData.length; i++){
//iterate over every field in the record
for (var field in airtableData[i].fields){
sheet.getRange(i+2,fieldNames.indexOf(field)+1) //find the cell to be updated
.setValue(airtableData[i].fields[field]); //update the cell
}
}
}
////////////// query Airtable API to obtain raw data ///////////////////////
function fetchDataFromAirtable(tableName, viewID) {
// Initialize the offset.
var offset = 0;
// Initialize the result set.
var records = [];
// Make calls to Airtable to retrieve all the data...
while (offset !== null){
// Specify the URL to be called.
var url = [
"https://api.airtable.com/v0/",
baseID,
"/",
encodeURIComponent(tableName),
"?",
"api_key=",
api_key,
"&view=",
viewID,
"&offset=",
offset
].join('');
var options =
{
"method" : "GET"
};
//call the URL and add results to to our result set
response = JSON.parse(UrlFetchApp.fetch(url,options));
records.push.apply(records, response.records);
//wait for a bit so we don't get rate limited by Airtable
Utilities.sleep(201);
// Adjust the offset.
if (response.offset){
offset = response.offset;
} else {
offset = null;
}
}
return records;
}
////////////////////////////////////
Copy the above code and paste it into the script editor window. You can get the Airtable API key from the account page of your Airtable account here.
Click “Generate API Key” to generate it.
Next, you should find the base ID for the base that you need to use. Open the Airtable API page which can be found here.
Click the base that you will want to use. Copy the API key from that page and paste it into the right place in your code. The API key has the pattern app…
Save the script and reload the spreadsheet.
If everything worked okay, you should see a new menu named “Sync Airtable to Google Sheets”.
Click the new menu then select “Sync all data manually”.
If asked to to give the script permission, do so. You should see the Airtable table begin to come through.
Airtable Tableau Integration Part 2: Connect Tableau And Google Sheets
Now that the Airtable data is in Google Sheets, you can pull it from there into Tableau. Follow the steps given below:
Step 1: Open Tableau, click “Connect” and then select “Google Sheets”.
Step 2: Sign into your Google Sheets account using your Google account credentials.
Step 3: Click “Allow” to give Tableau access to your Google Sheets.
Step 4: Select the Google Sheets that you need Tableau to connect to, then click the “Connect” button.
Congratulations!
You’ll have connected Tableau to Google Sheets and pulled data from Airtable into Tableau.
Limitations of Airtable Tableau Integration using Google Sheets
The following are the challenges of integrating Airtable and Tableau:
- There is no direct way to integrate Airtable and Tableau. One has to use Google Sheets as the intermediary.
- The process of integrating Airtable and Tableau is lengthy. One may take a long time to sync data between the two platforms.
- Coding knowledge is required. This makes it difficult for individuals without coding knowledge and companies with no skilled technical team.
- It’s impossible to pull data from Airtable into Tableau in real-time.
Method 2: Airtable Tableau Integration using Hevo
Hevo is a No-code Data Pipeline. It supports pre-built data integrations from 100+ data sources, including Airtable. Hevo offers a fully managed solution for your fully automated Airtable Tableau integration process and will let you directly load data to Tableau from Airtable. It will automate your data flow in minutes without writing any line of code. Its fault-tolerant architecture makes sure that your data is secure and consistent. Hevo provides you with a truly efficient and fully automated solution to manage data in real-time and always have analysis-ready data on Tableau.
Hevo focuses on three simple steps to get you started:
- Connect: Connect Hevo with Airtable and various other payments, sales & marketing data sources by simply logging in with your credentials.
- Integrate: Consolidate your payments & customer data from several sources in Hevo’s Managed Data Integration Platform and automatically transform it into an analysis-ready form.
- Visualize: Connect Hevo with your desired BI tool such as Tableau and visualize your unified payments and sales data easily to gain better insights.
As can be seen, you are simply required to enter the corresponding credentials to implement this fully automated data pipeline without using any code.
Let’s look at some salient features of Hevo:
- Fully Managed: It requires no management and maintenance as Hevo is a fully automated platform.
- Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer.
- Real-Time: Hevo offers real-time data migration. So, your data is always ready for analysis.
- Schema Management: Hevo can automatically detect the schema of the incoming data and maps it to the destination schema.
- Live Monitoring: Advanced monitoring gives you a one-stop view to watch all the activities that occur within pipelines.
- Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support call.
Explore more about Hevo by signing up for a 14-day free trial today.
Conclusion
In this article, you learned about Airtable and the procedure to set up your Airtable Tableau Integration, the limitations of the manual method, and the option to use Hevo Data’s no-code Data pipeline.
Integrating and analyzing data from a huge set of diverse sources can be challenging, this is where Hevo comes into the picture. Hevo Data, a No-code Data Pipeline helps you transfer data from a source of your choice in a fully automated and secure manner without having to write the code repeatedly. Hevo with its strong integration with 100+ sources & BI tools, allows you to not only export & load Data but also transform & enrich your Data & make it analysis-ready in a jiffy.
Get started with Hevo today! Sign up here for a 14-day free trial!
Skilled in freelance writing within the data industry, Nicholas is passionate about unraveling the complexities of data integration and data analysis through informative content for those delving deeper into these subjects. He has written more than 150+ blogs on databases, processes, and tutorials that help data practitioners solve their day-to-day problems.