Hey guys,
I am trying to get structured data from an API call, so I can save it in Airtable. In this case, these are contact details.
However, I am struggling on the first step - which is making any use of the API’s response, as I cannot seem to encode the response in JSON.
Here is the HTTP’s request response data:
It is of content type - “text/html”
[
{
"statusCode": 200,
"headers": [
{
"name": "cache-control",
"value": "private"
},
{
"name": "content-type",
"value": "text/html;charset=UTF-8"
},
{
"name": "server",
"value": "Microsoft-IIS/8.5"
},
{
"name": "set-cookie",
"value": "*********** I have removed this ******; secure; path=/"
},
{
"name": "x-powered-by",
"value": "ASP.NET"
},
{
"name": "date",
"value": "Tue, 09 Apr 2024 04:35:36 GMT"
},
{
"name": "content-length",
"value": "2872"
}
],
"cookieHeaders": [
"*********** I have removed this ******; secure; path=/"
],
"data": "{\"cpage\":1,\"datas\":[{\"vcode\":\"10000427\",\"FullName_e\":\"Dr. Dia A SMITH\",\"FullName_k\":\"Dia A SMITH\",\"Affiliation_e\":\"The University of Queensland \",\"Affiliation_k\":\"The University of Queensland \",\"Depart_k\":\"UQ Dow Centre and School of Civil Engineering \",\"Depart_e\":\"UQ Dow Centre and School of Civil Engineering \",\"mphoto\":\"https://evs37korea.org/file/mphoto/10000427_Main%20PP%20300x300.jpg\",\"Title\":\"Dr.\",\"favorite\":false,\"CountryName\":\"Australia\"},{\"vcode\":\"10000346\",\"FullName_e\":\"Mr. SENONGWOOK CHA\",\"FullName_k\":\"차성욱\",\"Affiliation_e\":\"YOUNGSHIN PRECISION\",\"Affiliation_k\":\"영신정공\",\"Depart_k\":\"Electrification Engineering Design Team 1\",\"Depart_e\":\"Electrification Engineering Design Team 1\",\"mphoto\":\"\",\"Title\":\"Mr.\",\"favorite\":false,\"CountryName\":\"Republic of Korea\"},{\"vcode\":\"10002059\",\"FullName_e\":\"Mr. Unyoung(David) CHAE\",\"FullName_k\":\"채운영\",\"Affiliation_e\":\"Y2Solution\",\"Affiliation_k\":\"와이투솔루션\",\"Depart_k\":\"New Business Planning\",\"Depart_e\":\"New Business Planning\",\"mphoto\":\"\",\"Title\":\"Mr.\",\"favorite\":false,\"CountryName\":\"Republic of Korea\"},{\"vcode\":\"10000706\",\"FullName_e\":\"Ph D. JAESEUNG CHEON\",\"FullName_k\":\"천재승\",\"Affiliation_e\":\"Hyundai Mobis\",\"Affiliation_k\":\"현대모비스\",\"Depart_k\":\"FTCI\",\"Depart_e\":\"FTCI\",\"mphoto\":\"\",\"Title\":\"Ph D.\",\"favorite\":false,\"CountryName\":\"Republic of Korea\"},{\"vcode\":\"10001914\",\"FullName_e\":\"Mr. Keunsoo CHO\",\"FullName_k\":\"조근수\",\"Affiliation_e\":\"HYUNDAI MOTOR COMPANY\",\"Affiliation_k\":\"현대자동차\",\"Depart_k\":\"\",\"Depart_e\":\"\",\"mphoto\":\"\",\"Title\":\"Mr.\",\"favorite\":false,\"CountryName\":\"Republic of Korea\"},{\"vcode\":\"10000888\",\"FullName_e\":\"Ms. Genevieve Cullen\",\"FullName_k\":\"\",\"Affiliation_e\":\"EDTA\",\"Affiliation_k\":\"\",\"Depart_k\":\"\",\"Depart_e\":\"\",\"mphoto\":\"\",\"Title\":\"Ms.\",\"favorite\":false,\"CountryName\":\"USA\"},{\"vcode\":\"10006368\",\"FullName_e\":\"CHOI JAEGI\",\"FullName_k\":\"최재기\",\"Affiliation_e\":\"KOREA ELECTRIC TERMINAL\",\"Affiliation_k\":\"한국단자공업\",\"Depart_k\":\"\",\"Depart_e\":\"\",\"mphoto\":\"\",\"Title\":\"\",\"favorite\":false,\"CountryName\":\"Republic of Korea\"},{\"vcode\":\"10000598\",\"FullName_e\":\"Mr. Jinwon KIM\",\"FullName_k\":\"김진원\",\"Affiliation_e\":\"jlab company\",\"Affiliation_k\":\"제이랩\",\"Depart_k\":\"\",\"Depart_e\":\"\",\"mphoto\":\"\",\"Title\":\"Mr.\",\"favorite\":false,\"CountryName\":\"Republic of Korea\"},{\"vcode\":\"10001845\",\"FullName_e\":\"Ph D. Yeonji KIM\",\"FullName_k\":\"김연지\",\"Affiliation_e\":\"GMEG\",\"Affiliation_k\":\"지엠이지\",\"Depart_k\":\"\",\"Depart_e\":\"\",\"mphoto\":\"\",\"Title\":\"Ph D.\",\"favorite\":false,\"CountryName\":\"Republic of Korea\"},{\"vcode\":\"10007329\",\"FullName_e\":\"LEE KWANGSEOK\",\"FullName_k\":\"이광석\",\"Affiliation_e\":\"A-pro Industrial Co.,LTD.\",\"Affiliation_k\":\"아프로산업\",\"Depart_k\":\"\",\"Depart_e\":\"\",\"mphoto\":\"\",\"Title\":\"\",\"favorite\":false,\"CountryName\":\"Republic of Korea\"}],\"intTotalcount\":28,\"intTotalpage\":3,\"result\":\"success\",\"vcode\":\"10007150\"}",
"fileSize": 2872
}
]
What I am trying to get is something like:
[
{
"vcode": "10000427",
"FullName_e": "Dr. Dia A SMITH",
"FullName_k": "Dia A SMITH",
"Affiliation_e": "The University of Queensland ",
"Affiliation_k": "The University of Queensland ",
"Depart_k": "UQ Dow Centre and School of Civil Engineering ",
"Depart_e": "UQ Dow Centre and School of Civil Engineering ",
"mphoto": "https://evs37korea.org/file/mphoto/10000427_Main%20PP%20300x300.jpg",
"Title": "Dr.",
"favorite": false,
"CountryName": "Australia"
},
{
"vcode": "10000346",
"FullName_e": "Mr. SENONGWOOK CHA",
"FullName_k": "차성욱",
"Affiliation_e": "YOUNGSHIN PRECISION",
"Affiliation_k": "영신정공",
"Depart_k": "Electrification Engineering Design Team 1",
"Depart_e": "Electrification Engineering Design Team 1",
"mphoto": "",
"Title": "Mr.",
"favorite": false,
"CountryName": "Republic of Korea"
},
{
"vcode": "10000706",
"FullName_e": "Ph D. JAESEUNG CHEON",
"FullName_k": "천재승",
"Affiliation_e": "Hyundai Mobis",
"Affiliation_k": "현대모비스",
"Depart_k": "FTCI",
"Depart_e": "FTCI",
"mphoto": "",
"Title": "Ph D.",
"favorite": false,
"CountryName": "Republic of Korea"
},
{
"vcode": "10001914",
"FullName_e": "Mr. Keunsoo CHO",
"FullName_k": "조근수",
"Affiliation_e": "HYUNDAI MOTOR COMPANY",
"Affiliation_k": "현대자동차",
"Depart_k": "",
"Depart_e": "",
"mphoto": "",
"Title": "Mr.",
"favorite": false,
"CountryName": "Republic of Korea"
},
{
"vcode": "10000888",
"FullName_e": "Ms. Genevieve Cullen",
"FullName_k": "",
"Affiliation_e": "EDTA",
"Affiliation_k": "",
"Depart_k": "",
"Depart_e": "",
"mphoto": "",
"Title": "Ms.",
"favorite": false,
"CountryName": "USA"
},
{
"vcode": "10006368",
"FullName_e": "CHOI JAEGI",
"FullName_k": "최재기",
"Affiliation_e": "KOREA ELECTRIC TERMINAL",
"Affiliation_k": "한국단자공업",
"Depart_k": "",
"Depart_e": "",
"mphoto": "",
"Title": "",
"favorite": false,
"CountryName": "Republic of Korea"
},
{
"vcode": "10000598",
"FullName_e": "Mr. Jinwon KIM",
"FullName_k": "김진원",
"Affiliation_e": "jlab company",
"Affiliation_k": "제이랩",
"Depart_k": "",
"Depart_e": "",
"mphoto": "",
"Title": "Mr.",
"favorite": false,
"CountryName": "Republic of Korea"
},
{
"vcode": "10001845",
"FullName_e": "Ph D. Yeonji KIM",
"FullName_k": "김연지",
"Affiliation_e": "GMEG",
"Affiliation_k": "지엠이지",
"Depart_k": "",
"Depart_e": "",
"mphoto": "",
"Title": "Ph D.",
"favorite": false,
"CountryName": "Republic of Korea"
},
{
"vcode": "10007329",
"FullName_e": "LEE KWANGSEOK",
"FullName_k": "이광석",
"Affiliation_e": "A-pro Industrial Co.,LTD.",
"Affiliation_k": "아프로산업",
"Depart_k": "",
"Depart_e": "",
"mphoto": "",
"Title": "",
"favorite": false,
"CountryName": "Republic of Korea"
},
{
"vcode": "10004347",
"FullName_e": "Mr. PARK KWANSUP",
"FullName_k": "박관섭",
"Affiliation_e": "Hyundai Motor Company",
"Affiliation_k": "현대자동차",
"Depart_k": "eP System Test Team 3",
"Depart_e": "eP System Test Team 3",
"mphoto": "",
"Title": "Mr.",
"favorite": false,
"CountryName": "Republic of Korea"
}
]
If I create a module “Transform to JSON”, this is what I get:
"eyJjcGFnZSI6MSwiZGF0YXMiOlt7InZjb2RlIjoiMTAwMDA0MjciLCJGdWxsTmFtZV9lIjoiRHIuIERpYSBBIFNNSVRIIiwiRnVsbE5hbWVfayI6IkRpYSBBIFNNSVRIIiwiQWZmaWxpYXRpb25fZSI6IlRoZSBVbml2ZXJzaXR5IG9mIFF1ZWVuc2xhbmQgIiwiQWZmaWxpYXRpb25fayI6IlRoZSBVbml2ZXJzaXR5IG9mIFF1ZWVuc2xhbmQgIiwiRGVwYXJ0X2siOiJVUSBEb3cgQ2VudHJlIGFuZCBTY2hvb2wgb2YgQ2l2aWwgRW5naW5lZXJpbmcgIiwiRGVwYXJ0X2UiOiJVUSBEb3cgQ2VudHJlIGFuZCBTY2hvb2wgb2YgQ2l2aWwgRW5naW5lZXJpbmcgIiwibXBob3RvIjoiaHR0cHM6Ly9ldnMzN2tvcmVhLm9yZy9maWxlL21waG90by8xMDAwMDQyN19NYWluJTIwUFAlMjAzMDB4MzAwLmpwZyIsIlRpdGxlIjoiRHIuIiwiZmF2b3JpdGUiOmZhbHNlLCJDb3VudHJ5TmFtZSI6IkF1c3RyYWxpYSJ9LHsidmNvZGUiOiIxMDAwMDM0NiIsIkZ1bGxOYW1lX2UiOiJNci4gU0VOT05HV09PSyBDSEEiLCJGdWxsTmFtZV9rIjoi7LCo7ISx7JqxIiwiQWZmaWxpYXRpb25fZSI6IllPVU5HU0hJTiBQUkVDSVNJT04iLCJBZmZpbGlhdGlvbl9rIjoi7JiB7Iug7KCV6rO1IiwiRGVwYXJ0X2siOiJFbGVjdHJpZmljYXRpb24gRW5naW5lZXJpbmcgRGVzaWduIFRlYW0gMSIsIkRlcGFydF9lIjoiRWxlY3RyaWZpY2F0aW9uIEVuZ2luZWVyaW5nIERlc2lnbiBUZWFtIDEiLCJtcGhvdG8iOiIiLCJUaXRsZSI6Ik1yLiIsImZhdm9yaXRlIjpmYWxzZSwiQ291bnRyeU5hbWUiOiJSZXB1YmxpYyBvZiBLb3JlYSJ9LHsidmNvZGUiOiIxMDAwMjA1OSIsIkZ1bGxOYW1lX2UiOiJNci4gVW55b3VuZyhEYXZpZCkgQ0hBRSIsIkZ1bGxOYW1lX2siOiLssYTsmrTsmIEiLCJBZmZpbGlhdGlvbl9lIjoiWTJTb2x1dGlvbiIsIkFmZmlsaWF0aW9uX2siOiLsmYDsnbTtiKzshpTro6jshZgiLCJEZXBhcnRfayI6Ik5ldyBCdXNpbmVzcyBQbGFubmluZyIsIkRlcGFydF9lIjoiTmV3IEJ1c2luZXNzIFBsYW5uaW5nIiwibXBob3RvIjoiIiwiVGl0bGUiOiJNci4iLCJmYXZvcml0ZSI6ZmFsc2UsIkNvdW50cnlOYW1lIjoiUmVwdWJsaWMgb2YgS29yZWEifSx7InZjb2RlIjoiMTAwMDA3MDYiLCJGdWxsTmFtZV9lIjoiUGggRC4gSkFFU0VVTkcgQ0hFT04iLCJGdWxsTmFtZV9rIjoi7LKc7J6s7Iq5IiwiQWZmaWxpYXRpb25fZSI6Ikh5dW5kYWkgTW9iaXMiLCJBZmZpbGlhdGlvbl9rIjoi7ZiE64yA66qo67mE7IqkIiwiRGVwYXJ0X2siOiJGVENJIiwiRGVwYXJ0X2UiOiJGVENJIiwibXBob3RvIjoiIiwiVGl0bGUiOiJQaCBELiIsImZhdm9yaXRlIjpmYWxzZSwiQ291bnRyeU5hbWUiOiJSZXB1YmxpYyBvZiBLb3JlYSJ9LHsidmNvZGUiOiIxMDAwMTkxNCIsIkZ1bGxOYW1lX2UiOiJNci4gS2V1bnNvbyBDSE8iLCJGdWxsTmFtZV9rIjoi7KGw6re87IiYIiwiQWZmaWxpYXRpb25fZSI6IkhZVU5EQUkgTU9UT1IgQ09NUEFOWSIsIkFmZmlsaWF0aW9uX2siOiLtmITrjIDsnpDrj5nssKgiLCJEZXBhcnRfayI6IiIsIkRlcGFydF9lIjoiIiwibXBob3RvIjoiIiwiVGl0bGUiOiJNci4iLCJmYXZvcml0ZSI6ZmFsc2UsIkNvdW50cnlOYW1lIjoiUmVwdWJsaWMgb2YgS29yZWEifSx7InZjb2RlIjoiMTAwMDA4ODgiLCJGdWxsTmFtZV9lIjoiTXMuIEdlbmV2aWV2ZSBDdWxsZW4iLCJGdWxsTmFtZV9rIjoiIiwiQWZmaWxpYXRpb25fZSI6IkVEVEEiLCJBZmZpbGlhdGlvbl9rIjoiIiwiRGVwYXJ0X2siOiIiLCJEZXBhcnRfZSI6IiIsIm1waG90byI6IiIsIlRpdGxlIjoiTXMuIiwiZmF2b3JpdGUiOmZhbHNlLCJDb3VudHJ5TmFtZSI6IlVTQSJ9LHsidmNvZGUiOiIxMDAwNjM2OCIsIkZ1bGxOYW1lX2UiOiJDSE9JIEpBRUdJIiwiRnVsbE5hbWVfayI6Iuy1nOyerOq4sCIsIkFmZmlsaWF0aW9uX2UiOiJLT1JFQSBFTEVDVFJJQyBURVJNSU5BTCIsIkFmZmlsaWF0aW9uX2siOiLtlZzqta3ri6jsnpDqs7Xsl4UiLCJEZXBhcnRfayI6IiIsIkRlcGFydF9lIjoiIiwibXBob3RvIjoiIiwiVGl0bGUiOiIiLCJmYXZvcml0ZSI6ZmFsc2UsIkNvdW50cnlOYW1lIjoiUmVwdWJsaWMgb2YgS29yZWEifSx7InZjb2RlIjoiMTAwMDA1OTgiLCJGdWxsTmFtZV9lIjoiTXIuIEppbndvbiBLSU0iLCJGdWxsTmFtZV9rIjoi6rmA7KeE7JuQIiwiQWZmaWxpYXRpb25fZSI6ImpsYWIgY29tcGFueSIsIkFmZmlsaWF0aW9uX2siOiLsoJzsnbTrnqkiLCJEZXBhcnRfayI6IiIsIkRlcGFydF9lIjoiIiwibXBob3RvIjoiIiwiVGl0bGUiOiJNci4iLCJmYXZvcml0ZSI6ZmFsc2UsIkNvdW50cnlOYW1lIjoiUmVwdWJsaWMgb2YgS29yZWEifSx7InZjb2RlIjoiMTAwMDE4NDUiLCJGdWxsTmFtZV9lIjoiUGggRC4gWWVvbmppIEtJTSIsIkZ1bGxOYW1lX2siOiLquYDsl7Dsp4AiLCJBZmZpbGlhdGlvbl9lIjoiR01FRyIsIkFmZmlsaWF0aW9uX2siOiLsp4Dsl6DsnbTsp4AiLCJEZXBhcnRfayI6IiIsIkRlcGFydF9lIjoiIiwibXBob3RvIjoiIiwiVGl0bGUiOiJQaCBELiIsImZhdm9yaXRlIjpmYWxzZSwiQ291bnRyeU5hbWUiOiJSZXB1YmxpYyBvZiBLb3JlYSJ9LHsidmNvZGUiOiIxMDAwNzMyOSIsIkZ1bGxOYW1lX2UiOiJMRUUgS1dBTkdTRU9LIiwiRnVsbE5hbWVfayI6IuydtOq0keyEnSIsIkFmZmlsaWF0aW9uX2UiOiJBLXBybyBJbmR1c3RyaWFsIENvLixMVEQuIiwiQWZmaWxpYXRpb25fayI6IuyVhO2UhOuhnOyCsOyXhSIsIkRlcGFydF9rIjoiIiwiRGVwYXJ0X2UiOiIiLCJtcGhvdG8iOiIiLCJUaXRsZSI6IiIsImZhdm9yaXRlIjpmYWxzZSwiQ291bnRyeU5hbWUiOiJSZXB1YmxpYyBvZiBLb3JlYSJ9XSwiaW50VG90YWxjb3VudCI6MjgsImludFRvdGFscGFnZSI6MywicmVzdWx0Ijoic3VjY2VzcyIsInZjb2RlIjoiMTAwMDcxNTAifQ=="
If I first set up an “HTML to Text” module, and then try to parse to JSON I get the following:
"{\"cpage\":1,\"datas\":[{\"vcode\":\"10000427\",\"FullName_e\":\"Dr. Dia A SMITH\",\"FullName_k\":\"Dia A SMITH\",\"Affiliation_e\":\"The University of Queensland \",\"Affiliation_k\":\"The University of Queensland \",\"Depart_k\":\"UQ Dow Centre and School of Civil Engineering \",\"Depart_e\":\"UQ Dow Centre and School of Civil Engineering \",\"mphoto\":\"https://evs37korea.org/file/mphoto/10000427_Main%20PP%20300x300.jpg\",\"Title\":\"Dr.\",\"favorite\":false,\"CountryName\":\"Australia\"},{\"vcode\":\"10000346\",\"FullName_e\":\"Mr. SENONGWOOK CHA\",\"FullName_k\":\"차성욱\",\"Affiliation_e\":\"YOUNGSHIN PRECISION\",\"Affiliation_k\":\"영신정공\",\"Depart_k\":\"Electrification Engineering Design Team 1\",\"Depart_e\":\"Electrification Engineering Design Team 1\",\"mphoto\":\"\",\"Title\":\"Mr.\",\"favorite\":false,\"CountryName\":\"Republic of Korea\"},{\"vcode\":\"10002059\",\"FullName_e\":\"Mr. Unyoung(David) CHAE\",\"FullName_k\":\"채운영\",\"Affiliation_e\":\"Y2Solution\",\"Affiliation_k\":\"와이투솔루션\",\"Depart_k\":\"New Business Planning\",\"Depart_e\":\"New Business Planning\",\"mphoto\":\"\",\"Title\":\"Mr.\",\"favorite\":false,\"CountryName\":\"Republic of Korea\"},{\"vcode\":\"10000706\",\"FullName_e\":\"Ph D. JAESEUNG CHEON\",\"FullName_k\":\"천재승\",\"Affiliation_e\":\"Hyundai Mobis\",\"Affiliation_k\":\"현대모비스\",\"Depart_k\":\"FTCI\",\"Depart_e\":\"FTCI\",\"mphoto\":\"\",\"Title\":\"Ph D.\",\"favorite\":false,\"CountryName\":\"Republic of Korea\"},{\"vcode\":\"10001914\",\"FullName_e\":\"Mr. Keunsoo CHO\",\"FullName_k\":\"조근수\",\"Affiliation_e\":\"HYUNDAI MOTOR COMPANY\",\"Affiliation_k\":\"현대자동차\",\"Depart_k\":\"\",\"Depart_e\":\"\",\"mphoto\":\"\",\"Title\":\"Mr.\",\"favorite\":false,\"CountryName\":\"Republic of Korea\"},{\"vcode\":\"10000888\",\"FullName_e\":\"Ms. Genevieve Cullen\",\"FullName_k\":\"\",\"Affiliation_e\":\"EDTA\",\"Affiliation_k\":\"\",\"Depart_k\":\"\",\"Depart_e\":\"\",\"mphoto\":\"\",\"Title\":\"Ms.\",\"favorite\":false,\"CountryName\":\"USA\"},{\"vcode\":\"10006368\",\"FullName_e\":\"CHOI JAEGI\",\"FullName_k\":\"최재기\",\"Affiliation_e\":\"KOREA ELECTRIC TERMINAL\",\"Affiliation_k\":\"한국단자공업\",\"Depart_k\":\"\",\"Depart_e\":\"\",\"mphoto\":\"\",\"Title\":\"\",\"favorite\":false,\"CountryName\":\"Republic of Korea\"},{\"vcode\":\"10000598\",\"FullName_e\":\"Mr. Jinwon KIM\",\"FullName_k\":\"김진원\",\"Affiliation_e\":\"jlab company\",\"Affiliation_k\":\"제이랩\",\"Depart_k\":\"\",\"Depart_e\":\"\",\"mphoto\":\"\",\"Title\":\"Mr.\",\"favorite\":false,\"CountryName\":\"Republic of Korea\"},{\"vcode\":\"10001845\",\"FullName_e\":\"Ph D. Yeonji KIM\",\"FullName_k\":\"김연지\",\"Affiliation_e\":\"GMEG\",\"Affiliation_k\":\"지엠이지\",\"Depart_k\":\"\",\"Depart_e\":\"\",\"mphoto\":\"\",\"Title\":\"Ph D.\",\"favorite\":false,\"CountryName\":\"Republic of Korea\"},{\"vcode\":\"10007329\",\"FullName_e\":\"LEE KWANGSEOK\",\"FullName_k\":\"이광석\",\"Affiliation_e\":\"A-pro Industrial Co.,LTD.\",\"Affiliation_k\":\"아프로산업\",\"Depart_k\":\"\",\"Depart_e\":\"\",\"mphoto\":\"\",\"Title\":\"\",\"favorite\":false,\"CountryName\":\"Republic of Korea\"}],\"intTotalcount\":28,\"intTotalpage\":3,\"result\":\"success\",\"vcode\":\"10007150\"}"
Which, if I ask ChatGPT, it tells me that this is a legit JSON, however, I cannot use any of the data in subsequent modules. Instead, it is seen as a big piece of text.
I would like to be able to iterate through the contact data and save it in Airtable.
Any ideas on how to achieve this?
PS: I have done the job and extracted the data with the help of Google Spreadsheet, and ChatGPT’s scripting ability, so I am not in a hurry to get the data. Instead, I just want to learn how to perform this task in Make.com as I am currently stuck.
