Fast, operational, and awesome prototypes with Google Apps Scripts and the Airtable API

  • 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

Getting Started

Gather your Airtable API Key, Base ID, Target Table

A screenshot of an Airtable base’s API documentation page.
Any Airtable base you have access to will come with an automatically generated Airtable API Documentation page which will contain your base ID, the schema of your base, and examples API requests and responses.

Start a Google Apps Script Project and Write the HTTP Request

Your Google Apps Script Homepage and Integrated Development Environment.
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
}
Animated screen capture of running a test function and viewing the Logger.Log() output.
Use Logger.Log() to report to Google Apps Script’s Log Viewer. It will also report to the script’s execution log, even when not using the IDE to run the function.

The Airtable API Response

{
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:{
...
}
}
]
}

Secure Your API Credentials, Handle Offsets, and Parse the HTTP Response

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;
};
function myfunction(){
let baseId = PropertiesService.getScriptProperties.getProperty('airtableBaseId');
let result = getAllAirtable(baseid, 'Critters'); return result;
}
[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 Received

Close the Loop

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

Do Something With Your Data

Additional Data Processing

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

Deploy a Web App

<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
Hello World
</body>
</html>
function doGet() {
return HtmlService.createTemplateFromFile('index').evaluate();
}
<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>
Google Apps Script deployed Web App to the left. Live view of the target Airtable to the right.

Wrapping up —

--

--

--

Maker of Things for Makers of Things

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Egnyte chooses Movebot

Egnyte migration tool

Agave Development Update #14 / 04–10 July, 2021

An Introduction to Multithreading

Best practices to follow while using Terraform

Know your Domain — The Cynefin Framework

The Collab.Land Command Center is Here!

Let’s talk about interfaces in game development

My Perfect Bug Bounty Docker Setup

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Mondrian Hsieh

Mondrian Hsieh

Maker of Things for Makers of Things

More from Medium

Recipe website using complex data structure and Gatsby starter

Significance of Progressive Web Apps for E-commerce business development

Create company directory Alexa skill for your business — No coding required — Convoworks

How to Create and Use Backend Workflow that Works with React and Bubble.io