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.
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?
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 @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.
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.
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?
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!
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!
@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.
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!
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!
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!
Hi Adam, Thank you so much. I will look into codeAcademy!
To make sure I follow, I will have to manually put the below piece into my airtable fields and the script? Also, the "A_Salesrep ID" is the same thing as the P21 Salesrep ID. They are the same thing so I need to find the sales rep id in airtable and match it with the data in the screenshot and update the YTD sales field. Ex for the picture: Salesrep ID 66902 is in airtable and when this automation is triggered it will find the salesrep ID in airtable and map in & updated the YTD sales of "1010899.750..."
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!
I need to get this data into the script. With the script above when I ran it updated the fields to "5.1", 3.08, etc.. instead of the data from the consol log. Sorry! I know I am super green. When starting Code Academy is it something I can do here and there and in free time or is something I need to be able to dedicate a lot of time too when I start it?
I need to get this data into the script. With the script above when I ran it updated the fields to "5.1", 3.08, etc.. instead of the data from the consol log. Sorry! I know I am super green. When starting Code Academy is it something I can do here and there and in free time or is something I need to be able to dedicate a lot of time too when I start it?
re: Also, the "A_Salesrep ID" is the same thing as the P21 Salesrep ID. They are the same thing so I need to find the sales rep id in airtable and match it with the data in the screenshot and update the YTD sales field. Ex for the picture: Salesrep ID 66902 is in airtable and when this automation is triggered it will find the salesrep ID in airtable and map in & updated the YTD sales of "1010899.750..."
Yeap, on the same page!
re: To make sure I follow, I will have to manually put the below piece into my airtable fields and the script?
Oh nah, the `jsonData` variable in my script is used as an example and needs to be replaced by the result of your API call, and yeap you'll need to update it with your field names and table names!
And so your code should look a little like below:
let jsonData = await [FETCH CODE]
let table = base.getTable("Sales Team");
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);
}
There's going to be a tricky bit here where you need to return the JSON from your API call; if you can DM me a link to a duplicated example base I could install the script for you real quick! I totally understand if there's a security concern, and if it works better we could hop on a quick call with screenshare and we could set it up together!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.