I wanted to take a break from my CRM series and go through a good scripting example. My next CRM series post will utilize a more complex scripting example than I am doing here, so this is a good one to get started and is an interesting use case.
Airtable Scripting
As Airtable puts it…
Scripting lets you write short scripts to reduce time spent on repetitive tasks and uncover deeper insights with advanced queries and custom reporting.
Learn more on Airtable’s Developer Page
To get started, you can see some example scripts, install scripts from the marketplace, collaborate with others in the community, or keep reading this post for a primer.
Base Setup
Our goal is to have Airtable get current quotes for crypto. To get started, create a table called Coins. My table looks like below. If you want the script to work for you, please use the same field names.
A few nuances to this setup.
- Name & Ticker are Single Line Text fields
- Market is a formula field of
Ticker & "-USD". This is the market we will use in the API to obtain the price against the US Dollar. Here’s an example URL for Bitcoin. - Latest Price is a currency field using the symbol $ and having a precision of 7 decimals (the max)
- Latest Price (As Of) will be a timestamp from when we obtain the pricing. This is a DAte field with the Time value turned on.
Automation
Next, we will create a custom automation so that we can schedule code to run.
I am going to name the automation Get Coin Prices and the trigger will be “at a scheduled time”. In this sample base, I am setting it to daily, but you can make it happen more often like every few hours.
Our Action will be to Run a script. As long as you setup your table like I showed you up above, you should be able to paste this code into your script. In this code, we are querying the Coins data, looping through each one, and using the Coinbase Pro API (which is free and doesn’t require authentication for price data) to get the market prices.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
|
let updateArray = []; // array of records we will be updating at the end// Query the Coins tablelet table = base.getTable( "Coins" );let query = await table.selectRecordsAsync({ sorts: [ {field: "Latest Price (As Of)" , direction: "asc" } ], // this helps in case any records error in prior runs. They will be at the top of the next run. fields: [ "Ticker" , "Name" , "Market" , "Latest Price (As Of)" , "Latest Price" ] // it's important to name the specific fields you want to pull back.});// Loop through the records, one at a timefor ( let record of query.records) { // Use Fetch() in JavaScript to contact the Coinbase API for the price let response = await fetch('https: //api.pro.coinbase.com/products/' + record.getCellValueAsString("Market") + '/ticker'); // This is not critical, but it helps to have an execution log you can look back on later. let log = ' '; log += ' \n ' + ' Name: ' + record.getCellValueAsString("Name"); log += ' \n ' + ' Ticker: ' + record.getCellValueAsString("Ticker"); log += ' \n ' + ' Market: ' + record.getCellValueAsString("Market"); log += ' \n ' + "Response Status: " + response.status; if (response.status === 200) { // 200 is a response of OK. That' s what we want. // get the JSON data from the response let data = await response.json(); // make sure we obtained a number for the price and build an update object to update the coin record if (isNaN(Number(data.price)) == false ){ let updateObject = {}; updateObject[ "id" ] = record.id; updateObject[ "fields" ] = {}; updateObject[ "fields" ][ "Latest Price" ] = Number(data.price); updateObject[ "fields" ][ "Latest Price (As Of)" ] = new Date(); updateArray.push(updateObject); log += '\n' + 'Price: ' + updateObject[ "fields" ][ "Latest Price" ]; } } console.log(log);}// Make sure we have records to update and, if so, update themif (updateArray.length > 0){ console.log(updateArray); await table.updateRecordsAsync(updateArray);} |
You can run a Test within the code editor. When you do, it should look like this.
Review Results
As you can see, our table has been populated when we ran our test.
As you can see, we now have a price populated on each record and a timestamp for when we last obtained the price.
Debugging
As you probably noticed in the image above, we did not get a price for Avalanche (AVAX). Why not? Well, if you go into the Automation, click on the script node and then work your way to the execution logs, you can find out.
The benefit of having some console.log lines in your script is that you can review your execution logs and debug your script. As you can see, Avalanche is returning a 404 response rather than a 200. We are using the Coinbase Pro API. As it turns out, Coinbase does not support the AVAX coin. Thus, they are not able to obtain a quote for it.
I hope this was a good example for you. All in all, the script was pretty simple, but it needs to get tied into a framework for automating it to run. That is where the scheduled automations from Airtable come into play. Cool stuff.
Comments