If you’re an avid Airtable user, you’re probably familiar with Data Fetcher, the popular Airtable extension for importing data from web services. Data Fetcher allows Airtable users to run, save, and schedule API requests, and it can be used with almost any API to retrieve data and integrate it into your Airtable base. 

People often use Data Fetcher to get up-to-date from various systems, or to enrich existing data they have in their table. Data Fetcher is a popular and capable solution, but it has its limitations. In this blog we’ll look at various alternatives to Data Fetcher and consider the pros and cons. 

Data Fetcher Limitations:

• Number of Rows – Data Fetcher is restricted by Airtable’s 1,200, 5,000 and 50,000 row counts per table (Free, Plus, and Pro plans respectively), and large requests often hang or fail. 
• Number of Columns – Data Fetcher plans allow imports of 50 to 150 columns. If you need to import more columns, you’ll need a different approach. 
• Usability – It can be difficult to figure out how to work with complex APIs. Data Fetcher is a very powerful solution, but the trade off is a myriad of UI options that aren’t always intuitive to work with.
• Results Formatting – It can be tricky to correctly format or flatten deeply nested JSON results.
• Cost – If you are looking to work with Data Fetcher at scale, you’ll need to upgrade your Airtable subscription as well as subscribe to a larger Data Fetcher plan.

Gigasheet

One alternative to Data Fetcher is Gigasheet, a spreadsheet-like web app that specializes in big data. Gigasheet supports up to 1 billion rows in a single sheet, and up to 17,000 columns, allowing users to work with larger datasets than Airtable. It is a good option for those who require more scalability in a no-code environment. Additionally, Gigasheet has built-in support for flattening JSON data, which simplifies the process of importing and formatting results from web-based APIs.

Like Data Fetcher, Gigasheet’s Custom Enrichments support nearly any API, but it does require that the service be reachable via a cURL request. The Gigasheet Custom Enrichment interface is at times more intuitive than Data Fetcher, but it also has fewer advanced options. Also, Gigasheet doesn’t have the same level of integration with Airtable as Data Fetcher does, which may be a dealbreaker for some users. Of the alternatives we review here, Gigasheet offers the best no-code solution at scale. 

Gigasheet can be more cost-effective than Data Fetcher, as it doesn’t require a paid subscription to both Airtable and Data Fetcher, and they have a free plan to get started. However, it is worth noting that premium plans do get more expensive. 

Excel with VBA

Another alternative to Data Fetcher is using Microsoft Excel with Visual Basic for Applications (VBA). VBA is a programming language that allows users to automate tasks in Excel, including importing data from web services. This solution gives users more control over the data they’re importing and how it’s formatted. Additionally, Excel has robust formatting capabilities, which makes it easier to analyze and visualize data. 

However, Excel with VBA is not as user-friendly as Data Fetcher or Gigasheet’s no-code solution, and it requires a working knowledge of programming.

cURL requests can be made in Excel using a combination of VBA code and the WinHTTP library, which is included in all modern versions of Windows.

Here are the general steps to make a cURL request in Excel:

1. Open a new Excel workbook and press Alt + F11 to open the VBA Editor.
2. In the VBA Editor, go to Tools > References and check the box next to “Microsoft WinHTTP Services”.
3. In the VBA Editor, create a new module and define a function that will make the cURL request. The function should take a URL and any necessary parameters as arguments.
4. In the function, create a new WinHttpRequest object and set its properties, such as the HTTP method (e.g., GET or POST), headers, and request body.
5. Send the request using the Send method of the WinHttpRequest object.
6. Parse the response using the ResponseText property of the WinHttpRequest object.

Here’s an example of what the VBA code might look like:

Function makeCurlRequest(url As String, data As String) As String
    Dim httpRequest As New WinHttpRequest
    
    ' Set the HTTP method, URL, and any headers
    httpRequest.Open "POST", url, False
    httpRequest.SetRequestHeader "Content-Type", "application/json"
    
    ' Set the request body
    httpRequest.Send data
    
    ' Get the response and return it as a string
    makeCurlRequest = httpRequest.ResponseText
End Function

To use this function in Excel, you can simply call it from a cell or another VBA function, passing in. For example:

=makeCurlRequest("https://api.example.com/users", "{""name"": ""John Smith""}")

This would make a POST request to the specified URL with the JSON data in the request body, and return the response as a string in the cell.

Obvious drawbacks to this approach are the complexity, and that it is not directly integrated in Airtable (though you can import Excel to Airtable). This alternative is more powerful and offers endless customization, and it’s also more scalable than Airtable, but less than Gigasheet: Excel’s max row limit of just over 1 million rows. 

Google Sheets with Apps Scripts

Google Sheets with Apps Scripts is another option for importing data from web services. Apps Scripts is a scripting language that allows users to automate tasks in Google Sheets. Like VBA, Apps Scripts gives users more control over the data they’re importing and how it’s formatted. Additionally, Google Sheets has built-in support for JSON and XML data, which simplifies the process of importing data from web services. However, like VBA, Apps Scripts requires a working knowledge of programming, and it’s not as user-friendly as Data Fetcher or other no-code solutions like Gigasheet. Google Sheets scale is also limited to 10 million cells per sheet, but in our experience scripts and formulas make the practical limits much lower (i.e., Google Sheets start to hang and crash).

Here are the general steps to make a cURL request in Google Sheets:

1. Open a new or existing Google Sheets document.
2. Go to Tools > Script Editor to open the Apps Script editor.
3. In the Apps Script editor, create a new function that will make the cURL request. The function should take a URL and any necessary parameters as arguments.
4. In the function, create a new UrlFetchApp object and use it to make the cURL request. Set any necessary options, such as the HTTP method (e.g., GET or POST), headers, and request body.
5. Parse the response and return it.

Here’s an example of what the Apps Script code might look like:

function makeCurlRequest(url, data) {
  var options = {
    'method': 'post',
    'contentType': 'application/json',
    'payload': data
  };
  var response = UrlFetchApp.fetch(url, options);
  return response.getContentText();
}

To use this function in Google Sheets, you can simply call it from a cell or another function, passing in the URL and data as arguments. For example:

=makeCurlRequest("https://api.example.com/users", '{"name": "John Smith"}')

This would make a POST request to the specified URL with the JSON data in the request body, and return the response as a string in the cell.

Note that before you can use UrlFetchApp in Apps Script, you may need to enable the “Google Apps Script API” in the Google Cloud Console, and also authorize the script to access external APIs.

Rows

Rows is a new breed of online spreadsheet that integrates with many platforms out of the box (from Salesforce to databases), and appears to be positioned as an Airtable competitor. Rows supports connecting to a custom API to fetch data via their built in spreadsheet GET or POST functions. This is a solid low-code approach that’s much easier than the Google Sheets and Excel options, but it’s not as nicely packaged as Data Fetcher or Gigasheet’s Custom Enrichments. 

A possible drawback with Rows: you’ll need to have a solid working knowledge of HTTP requests, as well as JSON. Another issue is scale. Rows scale is closer to Airtable in their support for only moderately sized data sets, and much more limited than all of the alternatives we’ve listed above. 

A benefit to Rows if you won’t need to purchase bundles of credits to make these requests like with Data Fetcher or Gigasheet, but you will need to upgrade to a paid version to exceed 10,000 requests (they currently show the paid plan includes 1M requests).

Conclusion

There are many alternatives to Data Fetcher for importing data from web services into a spreadsheet interface, but none are as tightly integrated with Airtable. Each alternative has its own strengths and weaknesses, and the best solution for you depends on your specific use case. 

Gigasheet is a good option for those who require more scalability, while Excel with VBA and Google Sheets with Apps Scripts are virtually free and better for users who require more control over the data they’re importing. Rows is another option, but it has some of the same limitations as Airtable and Data Fetcher. 

Regardless of which alternative you choose, it’s important to consider the scalability, usability, results formatting, and cost of each solution before making a decision.

The post Data Fetcher Alternatives For Big Data Enrichment appeared first on Big Data Analytics News.