How do I use filterByFormula in Airtable API with Google App Scripts?

Question

I am able to retrieve records using views, but I am not sure how to filterByFormula in Google App Scripts

function mwe() {

    API_KEY = SECRET;
    BASE_URL = BASE;


    var url = "https://api.airtable.com/v0/" + BASE_URL + "/TRNO?api_key=" + API_KEY + '&maxRecords=3&view=All%20not%20entered';

    var response = UrlFetchApp.fetch(url);

    var result = JSON.parse(response.getContentText());
    var myResult = result["records"]
    Logger.log(myResult);
}

Answer 1

There are a few things that can be done to try to achieve the formula field via the API. The more in-depth and entwined the data and in the formulas are naturally it becomes harder to scheme JSON from retrieving or query of the base and records. i.e.

• lookups
• rollups
• Nested Ifs with rollups lookups & links
• Or concatenates string from link to other formulas in lookups or rollups.

In your case, there is only the need to use an encoded URL try this tool will help you correctly establish the correctly, encoded URL needed. Plus save you some time in the future.

https://codepen.io/airtable/full/rLKkYB

The Endpoint should maybe look something like this below once you play around with it.

var url = "https://api.airtable.com/v0/YOUR_BASE_ID/filterByFormula=NameOfYourFormulaField"/TRNO?api_key=" + API_KEY + '&maxRecords=3&view=All%20not%20entered'";

But when trying to filterByRecords and retrieve records using views the value for filterByFormula should be a string. You can test the formula by creating a formula field from the Airtable UI.

  {
       pageSize: 50,
       filterByFormula: '{Record1} = TRUE()',
       sort: [{field: 'Record1 Date',direction: 'desc' }]
    }

And just one last thing,If you are wanting to use filterByFormula with record IDs in a linked table, you can create a formula in the linked table that is RECORD_ID(), then add a lookup to that formula field in the main table. and list NameFormalfield

[https://api.airtable.com][2] goes a lot more in-depth into the String formatting etc. Hope this helps Good Luck.