Okay - filterByFormula (I think) uses exactly the syntax that you would apply if doing so in a formula field. As such, I believe null values are treated as empty strings, so something like:
{Milestone 1} = “”
… would be all you’d need; no IF() would be required because you aren’t trying to apply logic within the filter query; the query itself applies the logic to the entire data set.
Example… give this table:
The following code returns only the incomplete milestones indicated by null values:
//
// query airtable records by filter
//
function testQueryTableByFilter()
{
// set the filter
var thisFilter = '{Milestone 1} = ""';
// fetch the data
var result = atQueryTableByFilter_("appAvzbF1dJ9OkgMn", "Airdrop", thisFilter);
// parse the data
var oData = JSON.parse(result).records;
// enumerate the items returned based on the filter
for (var i in oData)
{
Logger.log(oData[i].id + " :: " + ((oData[i].fields["Milestone 1"] == null) ? "Milestone 1 Incomplete" : oData[i].fields["Milestone 1"]));
}
}
//
// query by filter
//
function atQueryTableByFilter_(baseID, tableName, filter)
{
const options = {
method: 'GET',
headers: {
'Authorization' : 'Bearer ' + cMyAirtableAPIKey,
'Content-type': 'application/json'
},
muteHttpExceptions : true
};
var response = UrlFetchApp.fetch(cAirtableAPIEndpoint + baseID + "/" + encodeURIComponent(tableName) + "?filterByFormula=" + encodeURIComponent(filter), options).getContentText();
return(response);
}
As expected, the logged output includes only the incomplete items.
As expected, the result set is void of any reference to Milestone 1.