Help

Google app script web app as front end for Airtable

1591 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Josh_Cooper
6 - Interface Innovator
6 - Interface Innovator

Hi all!

I am not very good at JS, but I have to figure out a way to built a robust front end for an Airtable base and I have zero budget (I might as well start working on world peace while I’m at it). I have figured out how to retrieve and log (Logger.log) all the data in a particular table (believe it or not I can return all 3500+ records in a couple of seconds), but does anyone know how to display the data that is returned as raw JSON in the HTML page created when I deploy the google script as a web app?

I guess it would be helpful if I explained the use case. I have a table, “PartNoInfo”, that is linked to a “Concentration” table. The “PartNoInfo” table contains the metadata and calculations. The “Concentration” table has the individual concentrations, as well as lookup data from the linked table “ComponentData”, which has the information required for the calculations in the “PartNoInfo” table. I am trying to set up an UI where the user can enter a part number and retrieve the “PartNoInfo” data and a table of the components and concentrations.

I really just need a place to start with this. I can only find information on how to move information back and forth from Airtable and Google Sheets with app script, not looking up and displaying data as html on a web app.

Thanks in advance!

Below is the code I have so far.

  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 ' + keyAPI
      }
    };
  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 getPartNoInfo(){
  //let baseId = baseId;
  let result = getAllAirtable(baseId, 'PartNoInfo');
  let partVal = 'xxxxxx';
  let data = result.filter(function(partNo){return partNo['fields']['PartNo']===(partVal)});
  Logger.log(data);
  return data;
}

function doGet() {
  return HtmlService.createTemplateFromFile('index').evaluate();
}

Josh

1 Reply 1

You have to transform the JSON data into HTML data and serve it. This is typically done with a number of parts:

  1. JSON Parser
  2. HTML + CSS
  3. HTMLService (start here)

And by “robust front end”, I assume you mean CRUD operations, security, and a whole host of other crap that comes along with the requirement, right?

Other companies have done this for their own use and for rent as solutions. Just a wild guess – not a single one of them was developed for less than $20k; some were likely more than $100k in total investment.

Opinion…

You can either be the next company to do it poorly or half-bakedly for $10k (in time and blood) or you can license a ready-made platform for the price of two Venti lattes and ten pack of tacos. Please let us know which you picked and the outcome.