Create multiple records from JSON Data

[Jack_Coley]

I've been trying to do this in way too many ways. I haven't figured it out ChatGPT and Bard haven't figured it out so I though I would ask actual people 😁

I'm making an api call to usaspending.gov and am able to to get the information I want returned, but I can't figure out how to use the data I'm pulling down to create multiple records based on the objects returned. Has anyone run into this and how did you do it?

let uei = 'PLACE-UEI';
let startDate = '2023-10-01';
let endDate = '2023-12-31';

let data = {
"filters": {
"time_period": [
{
"start_date": startDate,
"end_date": endDate
}
],
"award_type_codes": [
"A",
"B",
"C",
"D"
],
"recipient_search_text": [
uei
]
},
"fields": [
"Award ID",
"Recipient Name",
"Start Date",
"End Date",
"Award Amount",
"Total Outlays",
"Description",
"def_codes",
"COVID-19 Obligations",
"COVID-19 Outlays",
"Infrastructure Obligations",
"Infrastructure Outlays",
"Awarding Agency",
"Awarding Sub Agency",
"Contract Award Type",
"recipient_id",
"prime_award_recipient_id"
],
"page": 1,
"limit": 60,
"sort": "Award Amount",
"order": "desc",
"subawards": false
};

let url = 'https://api.usaspending.gov/api/v2/search/spending_by_award/';

fetch(url, {
method: 'POST',
body: JSON.stringify(data),
headers: {
'Content-Type': 'application/json'
}
})
.then(response => response.json())
.then(data => {
console.log(data);
})
.catch(error => {
console.error(error);
});

 Any help would be greatly appreciated.

[ScottWorld]

I don’t know JavaScript, so hopefully someone will help you with a Javascript answer below.

Since I don’t know scripting, I solve this problem in a no-code way by using Make’s advanced automations and integrations.

use the HTTP module of Make, along with the Parse JSON module and the Airtable modules.

Alternatively, you can use DataFetcher.com.

Hope this helps!

— ScottWorld, Expert Airtable Consultant 

[Jack_Coley]

Just an Update. Here's what I figured out. It seems to work. If anyone knows a simpler way, please feel free to reply.

function getAirtableURL(table) {
    const baseId = 'BASE_ID';
    const tableName = 'TABLE_NAME';
    return `https://api.airtable.com/v0/${baseId}/${tableName}`;
}
let table = base.getTable(TABLE_NAME);
let rid = input.config();
let uei = rid.uei;
let startDate = rid.startDate;
let endDate = rid.endDate;
// Filters and fields for USASpending
let data = {
    "filters": {
        "time_period": [
            {
                "start_date": startDate,
                "end_date": endDate
            }
        ],
        "award_type_codes": ["A", "B", "C", "D"],
        "recipient_search_text": [uei]
    },
    "fields": [
        "Award ID",
        "Recipient Name",
        "recipient_id",
        "Start Date",
        "End Date",
        "Award Amount",
        "Total Outlays",
        "Description",
        "Awarding Agency",
        "Award",
        "Awarding Sub Agency",
        "Contract Award Type",
    ],
    "page": 1,
    "limit": 60,
    "sort": "Award Amount",
    "order": "desc",
    "subawards": false
};

// Get Airtable API endpoint URL
let airtableUrl = table._url || getAirtableURL(table);
// console.log('Airtable API Endpoint:', airtableUrl);
let url = 'https://api.usaspending.gov/api/v2/search/spending_by_award/';
// Fetches the information from USASpending
fetch(url, {
    method: 'POST',
    body: JSON.stringify(data),
    headers: {
        'Content-Type': 'application/json'
    }
})
    .then(response => response.json())
    .then(apiData => {
        console.log('USASpending API Response:', apiData);
        // Assuming data is an array of objects
        const dataArray = apiData.results || [];
        // Iterate over each object in the array and create records
        dataArray.forEach(item => {
            // Create a record in Airtable using the item data
            fetch(airtableUrl, {
                method: 'POST',
                headers: {
                    'Authorization': 'Bearer PLACE_AT_TOKEN',
                    'Content-Type': 'application/json',
                },
                body: JSON.stringify({
                    fields: {
                        "UEI": uei,
                        "Award ID": item["Award ID"],
                        "Recipient Name": item["Recipient Name"],
                        "Start Date": item["Start Date"],
                        "End Date": item["End Date"],
                        "Award Amount": item["Award Amount"],
                        "Total Outlays": item["Total Outlays"],
                        "Description": item["Description"],
                        "Awarding Agency": item["Awarding Agency"],
                        "Awarding Sub Agency": item["Awarding Sub Agency"],
                        "Contract Award Type": item["Contract Award Type"],
                        "Award": "Prime"
                    }
                })
            })
                .then(response => response.json())
                .then(airtableData => {
                    console.log('Airtable API Response:', airtableData);
                    if (airtableData.error) {
                        console.error('Error creating record in Airtable:', airtableData.error);
                    } else {
                        console.log('Record created in Airtable:', airtableData);
                    }
                })
                .catch(error => {
                    console.error('Error creating record in Airtable:', error);
                });
        });
    })
    .catch(error => {
        console.error('Error fetching data from USASpending API:', error);
    });