Airtable & Cryptocurrency: How to Track and Store Prices in Airtable

Cover Image for Airtable & Cryptocurrency: How to Track and Store Prices in Airtable
Marko K.

Fetching Cryptocurrency Prices with Next.js and Coinbase API, and Storing Them in Airtable

Cryptocurrency's popularity is soaring, and developers often need to integrate real-time price data into apps. In this guide, we'll fetch current cryptocurrency prices using the Coinbase API in a Next.js API route and save the data in Airtable.

📌 Quick Note for Our Readers:

If you're interested in medium or low-code solutions to this problem, scroll down to the sections titled "Medium-Code Alternatives" and "No-Code Solutions" respectively. We've covered various approaches to meet different needs!

1. Initial Setup

First, we import Axios, a library to make HTTP requests.

import axios from "axios";

2. Specify the Cryptocurrencies

For this example, we're interested in Bitcoin, Ethereum, and Litecoin.

const currencies = ["BTC", "ETH", "LTC"];

3. Getting the Prices from Coinbase

We'll call the Coinbase API for each cryptocurrency. Using promises helps us handle these multiple requests.

const pricePromises = currencies.map((currency) =>
  axios.get(`https://api.coinbase.com/v2/prices/${currency}-USD/spot`)
);
const responses = await Promise.all(pricePromises);

4. Format the Data

Once we have the prices, we'll structure the data with the price and the time we got it.

const prices = {};
const timestamp = new Date().toISOString();

currencies.forEach((currency, index) => {
  prices[currency] = {
    price: responses[index].data.data.amount,
    timestamp: timestamp,
  };
});

5. Send the Data Back to the Client

Now we can send the fetched prices back to whoever asked for it:

res.status(200).json(prices);

But, things can go wrong, so we'll add some error handling:

catch (error) {
  res.status(500).json({
    error: "Unable to fetch cryptocurrency prices.",
  });
}

6. Save the Data in Airtable

Next, we'll modify the script so that instead of sending the data back to the client, we store it in Airtable. Our Airtable setup has a "Crypto Tracker" base and a "Spot Prices" table with fields:

  • ID (Autonumber)
  • Ticker (Single line text)
  • Price (Currency, double-precision)
  • Timestamp (Single line text)

Screenshot of Airtable base setup

You'll need to get your personal access token from Airtable.

const ACCESS_TOKEN = "Your_Airtable_Token";
const BASE_ID = "appRdMT624OqxcQUE";
const TABLE_ID = "tbl55LzHWh2iujFfo";

To insert the data, you can use Airtable's JavaScript client:

npm install airtable

Then, configure it:

const Airtable = require("airtable");
Airtable.configure({ apiKey: ACCESS_TOKEN });

Airtable expects the data in a specific format:

const payload = Object.entries(prices).map(([ticker, data]) => ({
  fields: {
    Ticker: ticker,
    Price: parseFloat(data.price),
    Timestamp: data.timestamp,
  },
}));

const base = Airtable.base(BASE_ID);
const result = await base(TABLE_ID).create(payload, { typecast: true });

Final Code

Here's all the code combined:

import axios from "axios";
const Airtable = require("airtable");

const ACCESS_TOKEN = "Your_Airtable_Token";
const BASE_ID = "appRdMT624OqxcQUE";
const TABLE_ID = "tbl55LzHWh2iujFfo";

Airtable.configure({ apiKey: ACCESS_TOKEN });

export default async function handler(req, res) {
  const currencies = ["BTC", "ETH", "LTC"];

  try {
    const pricePromises = currencies.map((currency) =>
      axios.get(`https://api.coinbase.com/v2/prices/${currency}-USD/spot`)
    );

    const responses = await Promise.all(pricePromises);
    const prices = {};
    const timestamp = new Date().toISOString();

    currencies.forEach((currency, index) => {
      prices[currency] = {
        price: responses[index].data.data.amount,
        timestamp: timestamp,
      };
    });

    const payload = Object.entries(prices).map(([ticker, data]) => ({
      fields: {
        Ticker: ticker,
        Price: parseFloat(data.price),
        Timestamp: data.timestamp,
      },
    }));

    const base = Airtable.base(BASE_ID);
    await base(TABLE_ID).create(payload, { typecast: true });

    res.status(200).json(prices);
  } catch (error) {
    res.status(500).json({
      error: "Unable to fetch cryptocurrency prices.",
    });
  }
}

Remember to keep your access tokens private. Instead of hardcoding them, use environment variables. If someone gets your token, they can access your Airtable base.

Here's our table after a few runs:

Screenshot of Airtable base with crypto data populated

Deploying and Automating with Cron Jobs

Given that our solution is written in JavaScript and runs on Node.js, it can be easily deployed and automated on any platform that supports cron jobs. Here's a brief overview:

  1. Package the Script: Ensure your script, along with its dependencies (e.g., axios and airtable), is encapsulated within a project directory. Use package.json to manage these dependencies.

  2. Deploy to Your Preferred Platform: Choose a cloud provider or a hosting solution that supports Node.js and cron job scheduling. Upload your packaged script.

  3. Schedule as a Cron Job: On your chosen platform, set up a cron job to execute the script at your desired interval. For example, you might opt to run it every minute: * * * * *.

In essence, the flexibility of Node.js combined with the power of cron allows for efficient and automated data updates. This approach ensures you always have the latest cryptocurrency prices in your Airtable base.

Medium-Code Approaches: DataFetcher & Airtable Automations

If you're looking to elevate your implementation while maintaining simplicity, several mid-tier solutions can help you automate data fetching and population without diving deep into code. Let's explore a couple of these methods:

1. Using DataFetcher

DataFetcher is an app specifically designed to connect Airtable to various APIs without the need for coding. It's a more visual way of managing API requests. Here's a brief overview of using DataFetcher for our purposes:

  1. Set Up DataFetcher: After installing DataFetcher in your Airtable workspace, create a new API request.

  2. Configure API Request: Use the Coinbase API URL to fetch the desired cryptocurrency prices. Ensure you format the request to pull the exact data you need.

  3. Schedule the Request: DataFetcher allows you to automate the API calls. Set it to run at your preferred intervals, such as every minute.

  4. Map API Data to Airtable Fields: Once the data is fetched, direct it to the appropriate fields in your Airtable base.

Using this method, you bypass the need for external scripts, bringing all your automation into the Airtable ecosystem.

2. Airtable Automations with HTTP Request

Airtable's built-in automation feature can also make external HTTP requests. You can leverage this to fetch data from the Coinbase API:

  1. Create a New Automation: Inside your Airtable base, click on "Automations" and set up a new automation.

  2. Trigger Setup: Use a time-based trigger, like "At scheduled time", to define when you want the automation to run.

  3. Add Action to Make an HTTP Request: After setting the trigger, add an action and choose "Run a script" or "Webhook" based on what's available in your Airtable plan. Configure the action to make an HTTP request to the Coinbase API.

  4. Handle API Response: Parse the response from the Coinbase API and map it to the desired fields in your Airtable table.

  5. Finalize and Activate: Save and activate the automation, ensuring that it runs at the desired intervals.

Both DataFetcher and Airtable Automations offer a balance between low-code and high-code solutions. They provide a more integrated way of managing your data flow while offering flexibility in customization.

No-Code Solution: Subsystem's Managed Services

For those who prefer a hands-off approach that requires minimal intervention once set up, there are specialized solutions tailored to your needs.

At Subsystem, we understand that not everyone has the time, expertise, or inclination to tinker with code or even semi-automated solutions. You might be looking for a solution that you can simply "set and forget," while it consistently, reliably does its job in the background.

Here's what we offer:

  • Custom Interfaces: A user-friendly dashboard tailored to your requirements, giving you a straightforward way to monitor cryptocurrency prices.

  • Dedicated Data Storage: No more worries about data limits, integrity, or security. We provide bespoke data storage solutions ensuring your data remains intact, accurate, and readily accessible.

  • ETL Pipelines: Our Extract, Transform, Load (ETL) pipelines handle the complex tasks of fetching data from various sources, transforming it into a usable format, and then loading it into your storage or interface. This ensures real-time, accurate updates without any manual intervention.

If you're facing challenges with tracking cryptocurrency prices or looking for a robust yet simple solution, reach out to our team at Subsystem. We're here to provide solutions directly addressing your concerns in a cost-effective manner.

Get in touch with us for more details or questions.