May 15, 2020
Fast, operational, and awesome prototypes with Google Apps Scripts and the Airtable API
If your teams are using Gsuite tools and Airtable, the building blocks to build secure and operational web application prototypes may be closer than you think. In this article, I’ll show you how to get started and offer tips along the way.
TL;DR
You can super power your Airtable with google app scripts. If you’re here for a code snippet — jump ahead here. Or head to this tutorial’s Github Repo.Update — December 2020
Google Apps Script’s IDE has gotten a complete and awesome facelift. As a result some of the images here look a little different. The functions outlined in this guide are still present and work and I will update the steps to match once I have time. In the meantime if the new you’re unable to follow this guide with the new UI, use “legacy mode” in Google Apps Scripts which will bring the UI back to where it was at time of writing.
I have fallen in love with Airtable to the point that it may be considered unhealthy. Not just because I have a thing for good spreadsheets, and not because I get unreasonably happy seeing a cleanly defined database schema. But because Airtable’s combination of both has allowed me to break down the communication barriers between data engineers and the teams of users who need data to work.
I’ve often used Airtable to sketch out and distribute database schema’s to non-technical teams — and inversely, have used a team’s home grown Airtable to get a deep picture of what they use data for. Airtable, for me, has turned out to be as good a tool for communication as for productivity.
So why not use the prototype schema in Airtable form to drive the prototype application feature? and is there an equally well integrated, accessible and secure option for this? Enter Google Apps Scripts.
Google Apps Scripts is the development environment for creating GSuite Add-ons and adding scripted functions to anything in the Gsuite environment. In this case it offers a few key features:
- Scripts act like documents in the Gsuite ecosystem and use Google’s user permission scopes and access rights
- An “Okay” Integrated Development Environment (IDE)
- The UrlFetchApp Service
- A Properties Service to store script parameters
- A URL Endpoint and HTML Service
- Cloud Functions and Scheduler
I’ll go into why these are important here, but if you’re familiar with Google App Scripts, you can skip straight to the guide.
Notoriously, authentication and permissions are the hardest part of starting a web app. Even with modern web application platforms like Firebase and Netlify, adding authentication mechanisms and protecting access to your app is an intense step. Google Apps Scripts use the same permissions for access as Google Drive. So in your organization only people with access to a drive location will have access to view your script. As for deployment, the same goes, a user will run the script’s functions under their own rights and on first run must grant the script access to the permission scopes it needs (just like Google Add-Ons).
All Google Apps Scripts are edited in the Google Apps Scripts IDE and are run using Google’s Runtime Environment. Although limiting in a lot of ways, this works out well for our purposes. It means we don’t have to worry about deployment dependencies or server side compatibility, and it also means that Gsuite services are built in. It also means our script is cloud based, and can be shared with anyone in your org like a Google document. Code for this article will be assumed to be completely written in the IDE. Multiple documents can be created in a single project, but they are limited to Google Script (.gs) and HTML (.html) documents. The Google Script document stores functions run by Google services in the cloud using the credentials specified on deploy, and HTML documents are for delivery to a user’s client using the Apps Scripts’ HTML Service.
The IDE is only okay, and is workable. Once you get started though, I strongly recommend learning about and using The Apps Script CLI. It still doesn’t allow you to test dynamically or emulate the runtime, but allows you to code in the comfort of your own editor.
As of writing Google App Scripts and functions in the code.gs documents use the V8 Runtime which generally supports ES6 syntax. This wasn’t true historically, and Google Scripts was limited ES5 syntax. This was a significant limitation. You’ll likely still find a lot of tutorials for Google App Scripts using ES5 and related workarounds, keep in mind there may be more modern ways to do the same thing now.
The UrlFetchApp Service allows your script to make HTTP requests and move information across the internet. UrlFetchApp Service requires the “External Service” permission scope be granted by the user to the script in order to run. This is annoying, but it’s a good thing.
The URL endpoint, HTML Service, and Cloud Functions are all examples of tools available specifically to Google cloud products that are at your fingertips when using Google Apps Scripts to activate your data into actions. The rabbit hole goes deep and we’ll scratch the surface here.
Getting Started
NB
This guide was written for beginners to Google Apps Scripts, Javascript, and Airtable API. Its correspondingly lengthy and the code examples written to be basic.
For this tutorial I’m going to use this Animal Crossing: New Horizons Critter Tracking Table, retrieve the Critter’s page, and return the Critters available on the current day. Feel free to copy that table if you just need something for this guide. With some extra time at the end, we’ll quickly show an example turning that data pipeline into a web app.
Want an even more comprehensive Animal Crossing Base? I got you. This table was created from this base available on Airtable Universe.
To get started, you’re going to need a target Airtable and have created a Google Apps Script Project.
Gather your Airtable API Key, Base ID, Target Table
To form a working HTTP request to the Airtable API you’ll need to have an Airtable API Key, your target Base ID, and the name of a target table.
Most of this information is available in the Airtable API’s automatically generated documentation page. Navigate to your base’s pageand take note of the above items in the Introduction and Authentication sections. If you haven’t generated an API key for your Airtable account, you will need to do so from your account page. The name of the target table must be identical to what appears in your airtable.
Your Airtable API Keys are considered sensitive data! Having it means anyone can use and edit any Airtable your account has access to. Keep it safe, and do not share it publicly — more on how to secure your API key in google app scripts to follow.
Start a Google Apps Script Project and Write the HTTP Request
1.Create or Open a Google App Scripts Project from your Apps Scripts Homepage
2.Write the following code in the code.gs document. Be sure to replace '[YOUR API KEY]','[YOUR AIRTABLE BASE]', and'[YOUR AIRTABLE TABLE NAME]'.
function getAirtable() {
//Form the request method and authorization header
const options = {
'method': 'get',
'headers': {
'Authorization': 'Bearer ' + '[YOUR API KEY]'
}
}; //Form the request URL
const url = 'https://api.airtable.com/v0/' + '[YOUR AIRTABLE BASE ID]' + '/' + '[YOUR AIRTABLE TABLE NAME]'; //Make the Request and Store the Response
const response = UrlFetchApp.fetch(url, options); //Log the response for debugging
Logger.log(response); return response
}
We can test the above function in the IDE by selecting the function from a dropdown and running it. Then inspect the result in the Logger.
On the first run of a function Google will prompt you to “Review Permissions” for the app. This is great, it means that users will need to grant the app permissions to run — remember all that about security out of box?. You should review the permissions and approve them to test your script. If you are using a consumer/personal account Google may flag your app as unverified, as it should since your script was just written just now by you. Click on ‘advanced’ to get the link to proceed anyways, if you’re worried you can remove permissions scopes to this app later in your google account settings.
Congratulations! you have working communication between your Google Apps Script Project and Airtable! Keep going though, we’re only halfway to the cool stuff.
The Airtable API Response
Digging into the structure of our API response clues us into how to begin using our data. Your response from Airtable will always we structures like this:
{
offset: "itr0C2cKucO6PGGK8/recZ7SUKnVWgRqBEK"
records:[
{createdTime: 2020-05-12T18:23:01.000Z,
id: "rec0oE1S6p2z18TYT",
fields:{
Name: "Arapaima",
Type: "Fish",
...
},
{createdTime: 2020-05-12T18:23:16.000Z,
id:"rec15NoRlLJ5z7GDE",
fields:{
...
}
}
]
}Your response will have a one parent object with an array property records. records is populated your Airtable objects included their createdTime, and id. The fields property of the record contains your field names and values. If a field is left blank, null, or false in Airtable, the field will be excluded from the fields response. But what’s with that offset property? We’ll have to dig into that.
Airtable’s HTTP responses are pretty informative, make a practice of reporting them and use them for error handling. This will help down the line if your Airtable structure changes and you need to adjust your calls.
Secure Your API Credentials, Handle Offsets, and Parse the HTTP Response
It works! but now we have to handle some cleanup. On close inspection there’s a few things that aren’t quite right.
My credentials are showing? My goodness!
— your script, right now
First and foremost — our API secrets are hardcoded in the script. As mentioned above, your API credentials are sensitive data and hardcoding them, as we just have is generally a bad thing, even if most users won’t be looking at the code. We can help hide things away a bit but using the Apps Scripts Properties Service.
Now, Remember that offset parameter in our first response above?
If we throw a Logger to monitor the length of our records response we can see that we only received 100 records. But our table has 160. Airtable API’s HTTP request features limit response to 100 records, if a request doesn’t cover the entirety of the data available, the response will contain an offset token in the header. This token can be appended to our request url as a query parameter to make another request for the next 100 items in our payload.
To solve this we can use a tiny bit of recursion and break out our fetch function to re-run with the offset token whenever an offset token is received.
Putting these solutions together, we get code like this:
function getAllAirtable(baseId, tableName) { let response = fetchAirtable(baseId, tableName);
let records = response['records']; while (response.hasOwnProperty('offset')) {
response = fetchAirtable(baseId, tableName, response['offset']);
records = records.concat(response['records']);
} Logger.log(records.length + ' Total Records Received');
return records;
}function fetchAirtable(baseId, tableName, offset) { const options = {
'method': 'get',
'headers': {
'Authorization': 'Bearer ' + PropertiesService.getScriptProperties().getProperty('airtableKey')
}
}; let url = 'https://api.airtable.com/v0/' + baseId + '/' + tableName; if (typeof (offset) != "undefined") {
url = url + '?offset=' + offset;
}; const response = JSON.parse(UrlFetchApp.fetch(url, options)); Logger.log(response.records.length + ' records received in batch'); return response;
};
In the above code example, we’ve replaced our API credentials with a Properties Service Call and pulled the baseId, and tableName out of the function and made the argument for portability and security. We also are recursively looking for the existence of an offset token in our response to trigger a successive API call to get the next batch of records until the entire result is retrieved. For the sake of portability, we’ve also pulled out the base ID and table name as arguments for the function. Now if we run our test function passing Base Id and Table name arguments to our getAirtable() function we should see a count of all records in our Critters Airtable, and the corresponding payload of data.
To test our function, we can call it in a test function with our baseId, and tableName:
function myfunction(){
let baseId = PropertiesService.getScriptProperties.getProperty('airtableBaseId'); let result = getAllAirtable(baseid, 'Critters'); return result;
}
This should return a Logger response and pick up the logs of the nested functions:
[20-05-14 19:40:53:179 PDT] 100 records received in batch
[20-05-14 19:40:53:363 PDT] 60 records received in batch
[20-05-14 19:40:53:366 PDT] 160 Total Records ReceivedClose the Loop
Now that we have data coming in to the apps script, how do we get data flowing back to Airtable? The Airtable API gives us functions for this too and they’re simple variations of the request we just made.
To update an existing record, we need to direct a patch request to a particular record using the id of a records from the above data in the url, and include a payload containing the fields we want to update. For example:
function catchCritter(recordId) {
let payload = {
"records": [
{
"id": recordId,
"fields": {
"Caught": true
}
}
]
};let baseId = PropertiesService.getScriptProperties().getProperty('airtableBaseId');
let response = airtableUpdate(baseId, 'Critters', payload);
return response;
}
We can test them using our test function and pointing the update actions at a random recordID:
To create records, we’ll direct a post request to the table with a payload of new record items and corresponding fields, but don’t need to specify a record id.
Why would we return data from this function if its just pushing an update to Airtable? When a create or update request is completed, Airtable will return the up to date record. This is immediately useful for debugging, but it’s also useful in your app as you can use the response body to replace the existing data in session. This keeps your app supplied with relatively fresh data without having to refresh the app or run an entirely new GET request.
Do Something With Your Data
Additional Data Processing
There’s a lot more work that can be done with the URL requests. In Airtable’s API documentation are instructions for adding field filters, sorting, and even formula filters to your request in the same method we added our offset token. But since you’re complete dataset is already here, let’s use our Google Apps Script to do our explore our data and perform the preprocessing we need for now.
Say we want to filter for only Critters available this month; in code.gs we can extend the test function we have above to do that:
function getTodaysCritters() {let baseId = PropertiesService.getScriptProperties().getProperty('airtableBaseId');
let data = getAllAirtable(baseId, 'Critters');let today = new Date();
let monthNow = today.toLocaleString('default', { month: 'long' });data = data.filter(critter => critter['fields']['Months Available'].includes(monthNow))Logger.log(data.length + ' Critters Found');return data;
}
The above function calls in the Critters record in the typical way. It then identifies the long string form of the current month and filter’s the response data by checking if the current month is contained in the Critter’s Months Available property. Also, notice that this is an example when the intended use of a data field informs the way the field is composed in Airtable. The way an app feature may shape its backend database schema.
Your Apps Script Project is now completely set up to consume data from Airtable, Process it, And you can start using that data to drive almost anything accessible to Gsuite — given the right permissions scopes — and use the results to write back to Airtable!
Deploy a Web App
You now have a pretty powerful platform for Airtable driven scripts to live. However with regards to prototypes, serving HTML is the most powerfully simple things Google Apps Scripts can do. Rendering HTML documents is also how Google Add-Ons manifest custom user interfaces on Google Documents, Sheets, and Slides. What’s more? it can be accomplished in 3 steps:
1.Create an index.html document in your Apps Script Project and make it give it a Hello World:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
Hello World
</body>
</html>2. In your code.gs document write a doGet() function and render index.html as a template:
function doGet() {
return HtmlService.createTemplateFromFile('index').evaluate();
}3. Publish and Deploy as Web App
Deploying as a Web App will give your project two URL endpoints. One production endpoint, or Current web app URL, that is always attached to the project version at time of deployment, and a second development endpoint that always renders the latest code. The production endpoint will end in .../exec and the development endpoint in .../dev.
Html documents in google app scripts are special and have the built in ability to call and run function in code.gs using google.script.run(). When used in combination with a javascript framework, this data can be handled to start wrapping up the interaction you’ve developed in your Airtable API calls, for example in this quick Vue.js and bulma.io powered one pager written into your index.html document:
<html><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bulma@0.8.2/css/bulma.css"><head>
<base target="_top">
</head><body>
<div class="section" id="mainApp"><div v-for="critter in todaysCritters" class="container" style="padding-top: 1em;">
<div class="card">
<header class="card-header">
<p class="card-header-title">{{critter.fields.Name}}</p>
</header>
<div class="card-content"><p>Months Availabe: {{critter.fields['Month Text']}}</p>
<p>Time Active: {{critter.fields['Time Text']}}</p>
<p>Location: {{critter.fields.Location}}</p>
<p>Selling Price: {{critter.fields.Bells}}</p></div>
<footer class="card-footer">
<a class="card-footer-item">
<div v-if="critter.fields.Caught" class="button is-static">
Critter Caught!
</div>
<div v-else v-on:click="catchCritter(critter.id), critter['fields']['Caught'] = true" class="button">
Catch the Critter
</div>
</a>
<a class="card-footer-item">
<div v-if="critter.fields.Donated" class="button is-static">
Critter Donated!
</div>
<div v-else v-on:click="donateCritter(critter.id), critter['fields']['Donated'] = true" class="button">
Donate the Critter
</div>
</a>
<a class="card-footer-item">
<div v-if="critter.fields.Modeled" class="button is-static">
Critter Modeled!
</div>
<div v-else v-on:click="modelCritter(critter.id), critter['fields']['Modeled'] = true" class="button">
Model the Critter
</div>
</a>
</footer>
</div>
</div></div></body><script type="text/javascript" src="https://cdn.jsdelivr.net/npm/vue@2.5.21/dist/vue.js"></script>
<script src="https://unpkg.com/vue-async-computed@3.7.0"></script>
<script>var mainApp = new Vue({
el: '#mainApp',
data: function () {
return {
}
},
computed: {
},
asyncComputed: {
todaysCritters() {
return new Promise((resolve, reject) => {
google.script.run
.withSuccessHandler(resolve)
.withFailureHandler(function (arg) {
console.log(arg)
alert("Failed to Run Google App Script Function - getTodaysCritters");
}).getTodaysCritters();
});
}
},
methods: {
catchCritter(recordId) {
google.script.run.catchCritter(recordId);
},
donateCritter(recordId) {
google.script.run.donateCritter(recordId);
},
modelCritter(recordId) {
google.script.run.modelCritter(recordId);
},
}
});</script>
</html>
In the above example, I’m using the app script function we created to get currently available Critters this month and then use Vue to dynamically display that data. I then attach buttons with click events that trigger Vue methods to run our catch, donate, and model updates.
Deploying a Web App with doGet() and the above html results in this:
In a few hundred lines of code, we have a working web application driving data entry in Airtable!
Of course there’s other ways you can use your Airtable data once in Google Apps Scripts.
— Want google calendar reminders about what uncaught critters are available today? — Add the Google Calendar Advanced Service and get reminding!
— Want a weekly report on progress in a Google Slide? — Add the Google Slides Service and start placing text!
— Want to have that happen every week? — Use Google’s Cloud Scheduler and let it run!
Do consider that every additional service potentially means deeper permissions scopes for your script and the more heavily your user credentials will be used. Consider creating a service account to keep away from unintended actions, and sensitive information.
Wrapping up —
A well designed App Script can be a home for a wide array of general automations and chaining them together creatively can really create the feel of a well integrated service. But remember our IDE and runtime environment? That makes organizing and maintaining an increasingly large code base really difficult.
There are way more features in the Airtable API using HTTP requests and Airtable is actively improving it. It can be used creatively and efficiently and isn’t limited to the methods in this writeup.
Although far from perfect, the apps script in browser IDE presents a great, collaborative way to practice using javascript akin to in browser editors proliferating around the inter-webs. Given its built in extensibility, immediate usefulness, and now support for standard javascript syntax, I’m comfortable point new coders there. Being a cloud platform they can have the benefit of being in on live, remote, code partnering sessions. I’ve used it as a teaching tool for members of my user teams who want to try developing a feature prototype themselves, or just learning how to script.
The cloud based platform also helps my teams collaborate on a script as a solution quickly. Once there, we can all have a conversation around the solution mechanisms and really validate it in the context of the problem we’re trying to address.
Handling permissions within Gsuite and seeing permissions scopes at work is comforting when developing functions you are not 100% sure about. But it does get in the way when if you intend to seamlessly distribute the app or handle authentication levels. When you run into this its a good sign you’re not dealing with a prototype anymore and it’s time to move on to a higher level platform.
Want to quickly superpower that Airtable data you’ve lovingly crafted to keep track of your island life? Just add GAS.
Thank you to the Airtable Community, and the creator of this template base, and to Row and Table for this article on Airtable API.
