Setting up Airtable Data Studio Integration: 2 Easy Methods

Most modern businesses make use of a vast number of platforms to run their day-to-day operations. These platforms generate a massive amount of data which is then analyzed to understand how businesses can improve their performance and plan future strategies accordingly.
One of the most well-known platforms used by many businesses today to enable employees to collaborate with each other is Airtable.

This article will provide you with an understanding of the easy method that can be used to set up Airtable Data Studio Integration to perform a comprehensive analysis of your Airtable data.

Introduction to Airtable

Airtable can be defined as an easy-to-use Cloud-based collaboration service that businesses can use to store, share, and edit Relational Databases. The databases, in this case, could contain data required to run the day-to-day operations of any business such as inventory details, tasks, growth strategies, and ideas, etc. 

Although it is true that you can store information about a project on an online Spreadsheet like Google Sheets and easily collaborate with others to work on that data, there are numerous limitations associated with using Spreadsheets. For example, you cannot define relationships between tables using a Spreadsheet. Storing related data together in a single Spreadsheet can be unwieldy and would result in a large number of errors when you try to perform any operations on it.

Airtable is similar to Spreadsheets, but it comes with two major differences. Firstly, with Airtable, data is stored in Relational Databases instead of Spreadsheets. The major difference between databases and Spreadsheets is that databases give users the ability to perform various complex operations that they would not have been able to perform ideally using a Spreadsheet. Secondly, Airtable is a user-friendly tool, hence, it is not necessary for you to be an expert in using Spreadsheets or Relational Databases to use Airtable. Airtable also houses numerous templates that can guide you on how to use the system. 

More information on Airtable can be found here.

Understanding the Key Features of Airtable

The key features of Airtable are:

• Customizable Views: Airtable allows users to set up different Views based on the business use cases. Numerous powerful sorting, filtering, and grouping operations can be performed on these Views that give users the freedom to arrange their work as per their preferences.
• Airtable Apps: Airtable gives users the ability to choose from over 50+ prebuilt applications that can help them bring their data to life and perform a vast number of operations.
• Automation: Airtable can help businesses perform their operations faster and focus on the work that matters by creating custom notifications, orchestrate work via integrations, and automate redundant tasks.
• Robust API: The official Airtable API allows users to connect their workflow to hundreds of third-party applications and services, or access their content programmatically.

Introduction to Google Data Studio

Google Data Studio is Google’s data reporting and visualization solution and is part of the Google Marketing Platform. Being a part of the Google Cloud Suite, it gives users the ability to easily integrate with other Google products and services such as Google Analytics, Google Sheets, Google Ads, etc, along with numerous third-party sources by either manually importing data or connecting to the data source using its API. It allows users to visualize data seamlessly and turn it into informative dashboards and reports and gain valuable insights from it. Along with that, it also houses functionality to allow real-time collaboration with multiple users irrespective of their physical location.

More information on Google Data Studio can be found here.

Understanding the Key Features of Google Data Studio

The key features of Google Data Studio are as follows:

• Live Data Connectors: Google Data Studio supports seamless integration with over 150+ data sources eliminating the need to schedule periodic data refreshes for your reports.
• Full Customization Controls: Google Data Studio gives users the ability to add new pages, tables, charts, etc., and customize them as per business use case and data requirements.
• Dynamic Controls: One of the reasons why people love Google Data Studio is its dynamic control feature, which gives them the ability to perform slice and dice operations on the data without having to update the report setup itself. With Google Data Studio, users can also insert dynamic controls to enable viewers to filter through the content using dimensions and include and exclude content using filters as per requirements.
• Data Transformation: Raw data often needs to be transformed into a more complete and meaningful format before any useful analysis can be performed on it. Google Data Studio turns your data’s metrics and dimensions into powerful building blocks for graphs and charts.

Understanding the Need for Airtable Data Studio Integration

Airtable helps organizations solve their problems of not being organized enough. This is of paramount importance for quality communication and preventing sporadic thinking in organizations. Airtable is also a good tool for facilitating collaboration on projects in organizations. Since it centralizes data, Airtable gives you an opportunity to run your business from one place.

However, most Airtable users feel that its built-in data visualization features are not powerful enough and only offer partial insights. Hence, it is a common practice for businesses to export their Airtable data and load it into a Business Intelligence and Reporting tool of choice in order to perform an in-depth analysis of their data. One of the most popular Reporting tools available in the market today is Google Data Studio. Google Data Studio allows users to create powerful dashboards that they can use to extract insights from your data. Considering the functionality offered by Google Data Studio, businesses feel the need to set up Airtable Data Studio Integration to analyze their Airtable data.

Setting up Airtable Data Studio Integration

There is no in-built connector that can be used to set up Airtable Data Studio Integration and hence, the process has to implemented manually. To integrate these two for data transfer, Google Sheets is used as an intermediary. 

In this method, data will first be exported from Airtable and stored in Google Sheets. Google Sheets will then be integrated with Google Data Studio using the in-built connector.

The following steps can be implemented to manually set up Airtable Data Studio Integration:

• Step 1: Creating Google Sheets Template
• Step 2: Adding Tables to be Exported from Airtable
• Step 3: Generating Airtable API Key
• Step 4: Integrating Airtable with Google Sheets
• Step 5: Integrating Google Sheets with Google Data Studio

Step 1: Creating Google Sheets Template

Sign in to your Google account and create a new blank Spreadsheet that will store all the data that will be exported from your Airtable base. 

A suitable name as per requirement can be given to the Spreadsheet.

Step 2: Adding Tables to be Exported from Airtable

Open the Airtable base that you wish to export data from, select the table whose data is to be visualized and click the VIEWS icon located at the top left 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 viw and ends before a question mark (?). In the above case, it is “viwZHOHKL7I2b611N.” 

Step 3: Generating Airtable API Key

The Airtable API key can be generated from the account page of your Airtable account on this URL: https://airtable.com/account

Click on Generate API Key to generate the key. 

You also need the base ID for the base that you wish to export. Open the Airtable API page on this URL: https://airtable.com/api

Click the base that you will want to use. Copy the API key from that page and save it in a secure location. These keys will be required in the next step.

Step 4: Integrating Airtable with Google Sheets

On your Spreadsheet, click the Tools button from the top ribbon and select Script editor. 

This will open the script editor window. 

Delete the default code from it and paste the following code that will allow Google Sheets to sync data from Airtable.

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;
}

////////////////////////////////////

Save the script and reload the Spreadsheet. You will now see a new menu called Sync Airtable to Google Sheets in the top panel.

Click on Sync Airtable to Google Sheets, select Sync all data manually, and give all necessary permissions to allow Google Sheets to access your Airtable data.

Step 5: Integrating Google Sheets with Google Data Studio

• Log in to your Google Data Studio account and create a blank report. 
• Click the Add Data button. 
• From the pop-up, select Google Sheets. 
• Select the Spreadsheet that contains the Airtable data. You can then add the sheets or data tables that you wish to analyze one at a time. 
• Select one of the sheets that you need to use as the data source and then click on Add.
• If you wish to add more sheets, click on Resource, select Manage added Data Sources, and click on Add a Data Source. 

This completes the process of manually setting up Airtable Data Studio Integration.

Limitations of Manually Setting up Airtable Data Studio Integration

The limitations of setting up Airtable Data Studio Integration manually are as follows:

• There is no in-built connector that can be used to set up Airtable Data Studio Integration automatically. Users have to rely on Google Sheets as an intermediary. 
• Coding knowledge is required. This makes it difficult for individuals without coding knowledge and businesses that do not have a skilled technical team to manually set up Airtable Data Studio Integration.
• The process of manually setting up Airtable Data Studio Integration is lengthy and complex and hence, prone to errors.
• It’s impossible to pull data from Airtable into Google Data Studio in real-time.

Conclusion

This article provided you with an in-depth understanding of how you can set up Airtable Data Studio Integration manually or using Hevo allowing you to perform a comprehensive analysis of your business data. There are, however, certain limitations associated with the manual method. If those limitations are not a concern to your use case, then implementing that method would be a good idea. However, if it is causing any issues or inefficiency in your operations, then you should consider using automated Data Integration platforms like Hevo.

Hevo helps you directly transfer data from a source of your choice to a Data Warehouse, Reporting tools such as Google Data Studio, or desired destination in a fully automated and secure manner without having to write the code. It will make your life easier and make data migration hassle-free. It is User-Friendly, Reliable, and Secure.

Pricing details for Hevo can be found here. Give Hevo a try by signing up for the 14-day free trial today.