Airtable Script: Assigning Variables to API Response

I’m relatively new to coding, and I was able to call a REST API, and record the information to the console log.

Unfortunately, where I am having trouble, is then taking that response and doing literally anything with it. Here is the code and the response below

let response = await fetch(‘https://myapiendpoint.com’,

{
method: ‘GET’,
headers: {
‘apikey’: ‘123456789’
},
})

console.log(await response.text());

The response I get is in XML it seems. A small excerpt of the response below:

<User><Id>USER ID</Id><UserName>USER NAME</UserName><FirstName>FIRST NAME</FirstName><LastName>LAST NAME</LastName><Active>true</Active><Email>EMAIL</Email></User>

I want to take the objects in this response (for example FirstName and LastName), and assign them to variables so that records can be created in later steps. I appreciate any assistance that can be given!

So if you use .json() instead of .text() on response, you’ll get an object, structured roughly like

{
  "records": [
    { // first record
      "id": "rec***********"
      "createdTime": "2022-11-23T********"
      "fields": { //fields use name instead of id
        }
    },
    { // second record
      ...
    },
    ...
  ]
}

I’m not sure how your data is structured, but the data you’re looking for will be in the fields key of each object under the records array.

So, using something like:

let recordArray = (await response.json()).records;

You can then iterate over the array with forEach or map to assign your variables.

There is an alternative method to accessing it via the airtable library:

const AIRTABLE_API_KEY = 'key**********' //insert your key here

var Airtable = require('airtable');
Airtable.configure({
    endpointUrl: 'https://api.airtable.com',
    apiKey: AIRTABLE_API_KEY
});
var base = Airtable.base('app***********');

In theory, that should provide you with commands similar to the Scripting extension inside Airtable, but I haven’t played around with it too much.

Hope that helps! I’m fairly new to JS myself and never formally studied it.

I’m not sure it’s that simple. Using the example XML snippet provided, .json is unable to parse the response. Maybe I missed something in my test, but this is the error and the code I used. If you change Content-Type to application/xml, the same error occurs. I think that simply asserting a response is .json doesn’t make is parsable. Please show me where I erred if I did.

IMPORTANT: for expediency, I hosted the xml snippet in an attachment field whose URL will not likely work in a few hours. You’ll need to change the URL to your actual XML API.

let url = "https://dl.airtable.com/.attachments/d426b76fb27a8a8415b4fc120157d9b6/51d374da/test.xml"

let options   = {
    "method": "GET",
    "headers": {
        "Content-Type"  : "application/json",
    }
}

await remoteFetchAsync(url, options)
  .then((resp) => resp.json())
  .then(function(data) {
      output.inspect(data);
  })
  .catch(function(e) {
      output.markdown("ERROR!");
      output.markdown(e.message);
  });

If (and only if) your XML API is generating shallow XML responses, you might just want to muscle through it using a simple parser based on Split(). Example…

await remoteFetchAsync(url, options)
  .then((resp) => resp.text())
  .then(function(data) {
      output.inspect(data);

      let xmlString = data;
      // parse the user id
      let userID = xmlString.split("<Id>")[1].split("</Id>")[0];
      output.text("userID: " + userID);
      // parse the user name
      let userName = xmlString.split("<UserName>")[1].split("</UserName>")[0];
      output.text("userName: " + userName);

  })
  .catch(function(e) {
      output.markdown("ERROR!");
      output.markdown(e.message);
  });
1 Like

You will need to use method to parse the XML. However, you may also be running into another issue.

Note that the response variable itself is not the XML. You had to use the method .text() to get the XML from the response, which you are doing inside the console.log(), but not outside of it. You cannot treat the response variable as if it holds the XML directly,

const data = await response.text()

You might also want to refer to the API documentation to see if you can request data in json format in your request header. If the API will send data json format, it is much easier to parse.

1 Like