Script Help - Writing Console.Log to a field

[Acefast]

I have managed to create a get request (I’m 90% no code) and then info is coming back but how to then write it to a field?.

let table = base.getTable("TARIC Code")
let url = "https://www.trade-tariff.service.gov.uk/api/v2/commodities/";
let config = input.config();
let response = await fetch(url + config.commodityCode );
console.log(await response.json())

I want to take the result directly into a field to then sift through it for what I need.

[Vivid-Squid]

Hi @Acefast
Here is an example of an API request that writes to the Airtable base

let table = base.getTable('Table 1');

//Fetch url and translate to json
let url = await fetch('https://reqres.in/api/users');
let json = await url.json();

//Name your columns 'First Name', 'Last Name' and 'Email'
//Loop over the json object (key/value pairs)
for (i=0; i < json.data.length; i++) {
    let recordId = await table.createRecordAsync({
        "First Name": json.data[i].first_name,
        "Last Name": json.data[i].last_name,
        "Email": json.data[i].email
    })
}

https://builtonair.com/script/easy-external-api-example/

[Acefast]

Thanjs for the coming back quickly mate. I’m now almost there with it but…

let table = base.getTable("TARIC Code");

let url = "https://www.trade-tariff.service.gov.uk/api/v2/commodities/";
let config = input.config();
let response = await fetch(url + config.commodityCode );
console.log(await response.json())

//Name your columns 'First Name', 'Last Name' and 'Email'
//Loop over the json object (key/value pairs)
for (i=0; i < json.data.length; i++) {
let recordId = await table.createRecordAsync({
"Included Attribute Document Codes": json.data[i].included,
    })
}

I’m getting this error…

ERROR

ReferenceError: json is not defined

at main on line 11

[Vivid-Squid]

Create a variable after the response variable that is

let json =await response.json();

[Acefast]

I’m still getting a final error with this code…

let table = base.getTable("TARIC Code");

let url = "https://www.trade-tariff.service.gov.uk/api/v2/commodities/";
let config = input.config();
let response = await fetch(url + config.commodityCode );
console.log(await response.json());
let json = await response.json();

//Name your columns 'First Name', 'Last Name' and 'Email'
//Loop over the json object (key/value pairs)
for (i=0; i < json.length; i++) {
let recordId = await table.createRecordAsync({
"included Atribute Document Codes": json.data[i].included
    })
}

Error = TypeError: Body stream already read
at main on line 8

[Acefast]

Thanks for the help with this! Saves me hours of trial and error.

[Vivid-Squid]

Remove the line console.log(await response.json());

You can console.log(json) after you call it to see the JSON fields.

[Acefast]

It kinda works but still wont write to the field for some reason. Could it be those i on line 12?

[Vivid-Squid]

I am unsure what you want to do with the data once you get it. You need to review the JSON data and determine which parts of it you want to put in a cell.

for (let i=0; i < json.included.length; i++) {
let recordId = await table.createRecordAsync({
"HMRC JSON": json.included[i].attributes.title
    })
}

This will get you the records that have title in the JSON and put them in the field. But looking at the API there is a lot more that can be extracted.

[Acefast]

I was actually looking to pull out the lot. It’s around 9000 words. Too much?

[Vivid-Squid]

I have no idea if you will hit any limits. I pulled 1 code and it gave me 500+ records. The thing is, ‘the whole lot’ is a bunch of things that can be a number of different types of data (text, numbers…) so if you want to put that data into airtable the type of field needs to match the type of data you have.
Since you are going through the effort of writing a script, you should review the data you are getting and put it in the appropriate place in airtable.

It looks like each object has an id, type and attributes (amongst other things). Try just putting in the id and see how that data looks. Then add the type and grow it from there.

[Acefast]

Thanks for the advice, I really do appreciate that. I’m on my way with this now.

[Acefast]

Out of interest, if I pulled the lot into a long text and broke it up in formulas what would the script be for that?

[Vivid-Squid]

Airtable unlike excel does not have a native split to cells function. There may be an app that does this but I don’t know.
You can use formulas to FIND() aspects of your text, then you can use LEFT(), RIGHT() or MID() to pull out the text in the location returned from FIND().

I am not sure what your end goal is, but I would guess there is something in that API that you want to keep in Airtable. Instead of using airtable to sort through all of the data, manually keeping only what you want. I would review the data and have my script only gather what I need. I guess the effort would pay off better in the long run.