Help

Mapping Json api data into a certain field in my airtable base

Topic Labels: API Scripting
1747 12
cancel
Showing results for 
Search instead for 
Did you mean: 
Anna_Marie_Wats
6 - Interface Innovator
6 - Interface Innovator

Hello,

 

I am pulling in data via json API and need to know where to go from here?  I have a table (sales team) that has the "salesrep id" and I need to map in the ytd sales data for each salesrep id. I am not sure what to do next to loop through the json object data and map them accordingly.

  

Screenshot 2024-03-11 at 09.08.08.png

Screenshot 2024-03-11 at 09.11.17.png

12 Replies 12

Hmm, you're going to need to grab all the records from the "Sales Team" table and find a way to create a link between the `P21 Salesrep ID` value and its record ID.  After that you can loop through your data to create an array of record updates, and finally actually do the update

May I know how much development experience you have so that I know how to tailor my responses?

ScottWorld
18 - Pluto
18 - Pluto

Hi @Anna_Marie_Wats,

Where are you bringing in your JSON data from?

In general, the quickest and easiest way to automate bringing JSON data into Airtable — with absolutely no programming code knowledge at all — is by using Make’s automations and integrations for Airtable

Make will automatically;

1. Parse the JSON file for you.
2. Loop through all the JSON data for you.
3. Create or update all the necessary data in Airtable.

You can even use Make’s HTTP module to automatically connect to any app on the web that has an API, but Make natively supports almost 2,000 apps already.

It can also parse a JSON data file that you provide it in a cloud storage location.  

If you’ve never used Make before, I’ve assembled a bunch of Make training resources in this thread: https://air.tableforums.com/t/make-com-basic-navigation-tips/277

I also give live demonstrations of how to use Make in many of my Airtable podcast appearances here: https://www.youtube.com/playlist?list=PLqssva4liHRwHhQIpTXekG8WObEoyC2F1

p.s. If you have a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld  

Hi Scott! I am bringing the JSON data from Easy Insight. I wanted to try and do it in airtable via airtable script but I am not a programmer. I was hoping someone else had done something similar already and I could piggyback.

I will for sure check out Make to get this accomplished if I can't find an example script to work with! 🙂

FYI, you have partially exposed your API key in your screen capture. The screen capture does not show the entire API key, so it probably is still safe if this is the only leak. However, you might want to change your API key and take additional steps to make sure that you do not accidentally reveal it in the future. Keep in mind that anyone with access to the full base will be able to see the API key.

As far as next steps, it depends on how fluent you are with JavaScript and Airtable scripting. Because you are not sure how to loop through the array, I'm guessing that you are new to coding. (Please forgive me if I am mistake.) I wrote this article for people who are not sure how to start learning Airtable scripting.

Thank you so much got pointing that out! Yes, you are correct. I am very very new. I want to get started but really do not know where to begin and need the foundation first to be able to understand coding and the science behind it.

Basically none. I can make changes to scripts and run them if provided the tools (like how airtable provides the tool to run scripts) but that is about it. Let me know if you have any suggestions on where I could go to begin learning and understanding developing at a very beginner level! 

 

Hi @Anna_Marie_Wats,

Is this the Easy Insight website? It looks like they have a very easy API, which is documented at this link.

I don't know scripting myself, but their API is so simple that you wouldn't even need to run a script or learn JavaScripting at all to handle this for you.

You might be able to set this up with the DataFetcher.com extension, but you can definitely set this up with Make's HTTP module and Make's Airtable modules.

It would take me way too long to type up the entire process of setting all of this up for you, but if you're looking to hire someone to train you on all of this & set it all up for you, feel free to contact me through my website!


@Anna_Marie_Wats wrote:

Basically none. I can make changes to scripts and run them if provided the tools (like how airtable provides the tool to run scripts) but that is about it. Let me know if you have any suggestions on where I could go to begin learning and understanding developing at a very beginner level! 

 


This is just my opinion, but I think that it will be challenging for someone with no coding background to do what you describe, especially if the script needs to run within the limitations of an automation script with many records.

However, you do say that you can make changes to scripts, so I might be underestimating your scripting abilities. If you want to do this yourself, don't let my opinion stop you! Everyone starts somewhere, and being able to write code is powerful. I also list my thoughts on how to get started with Airtable scripting in the link in my previous post.

I actually used CodeAcademy's JavaScript course way back when and really liked it, so you could check that out if you want!  It's been awhile though, so I don't know whether it's still as good as I remember, you know what I mean? 

Personally, I learn faster when I'm working on something that I actually need to use instead of a more abstract course, and a lot faster when I've got something to poke at heh, and in that spirit I threw something together for you here which should do what you want (You should be able to view the code in the automation I created). It doesn't handle the case of the JSON including a sales rep ID that doesn't exist in your table though!

And here's the code itself:

let jsonData = [
    { "A_Salesrep ID": "A1", "YTD Sales": "5.1" },
    { "A_Salesrep ID": "B2", "YTD Sales": "3.08" },
    { "A_Salesrep ID": "C3", "YTD Sales": "81" }
];

let table = base.getTable("Table 1");
let query = await table.selectRecordsAsync();

let salesRepRecords = {};
query.records.forEach(record => {
    salesRepRecords[record.getCellValue("P21 Salesrep ID")] = record.id;
});

let updates = jsonData.map(data => ({
    id: salesRepRecords[data["A_Salesrep ID"]],
    fields: {
        "YTD Sales": parseFloat(data["YTD Sales"])
    }
}));

while (updates.length > 0) {
    await table.updateRecordsAsync(updates.slice(0, 50));
    updates = updates.slice(50);
}

I'm sure there's a more elegant way to handle this / write it and would love any feedback too!