I am trying to check for emails based on domain names stored in a column in Google Sheets. I have the API call setup and working, but the data gets pasted into the selected cell. I would like to paste additional values ("value") from the JSON API response into adjacent cells. Is this possible?
Here's the script I currently use:
// Function
function GetEmail(domain) {
// URL and API info
var url = "https://api.hunter.io/v2/domain-search?domain="
var key = "&api_key=[REDACTED]"
var response = UrlFetchApp.fetch(url+domain+key);
// Parse the JSON reply
var json = response.getContentText();
var data1 = JSON.parse(json);
// Select value from JSON and return to cell
return (data1.data.emails[0]["value"]);
}
Edit: The API I'm using will return data that looks like this:
{
"data": {
"domain": "intercom.io",
"disposable": false,
"webmail": false,
"pattern": "{first}",
"organization": "Intercom",
"emails": [
{
"value": "ciaran@intercom.io",
"type": "personal",
"confidence": 92,
"sources": [
{
"domain": "github.com",
"uri": "http://github.com/ciaranlee",
"extracted_on": "2015-07-29",
"last_seen_on": "2017-07-01",
"still_on_page": true
},
{
"domain": "blog.intercom.io",
"uri": "http://blog.intercom.io/were-hiring-a-support-engineer/",
"extracted_on": "2015-08-29",
"last_seen_on": "2017-07-01",
"still_on_page": true
},
...
],
"first_name": "Ciaran",
"last_name": "Lee",
"position": "Support Engineer",
"seniority": "senior",
"department": "it",
"linkedin": null,
"twitter": "ciaran_lee",
"phone_number": null
},
...
]
},
Image of the result I'd like to appear is below. I want the highlighted data to be provided by the API. The first column is my own data.
So B2 contains a function =GetEmail (A2)
, which currently fetches the email in the JSON response: data.emails[0]("value")
and places it in the active cell (B2).
I would like the same function to also return data.emails.[0]("confidence")
into C2, and the data.emails[0]("type")
into D2 and so on for each domain.
I have seen a function about offsetting the active cell which could help, but have not yet found a way to be able to successfully map out which data I'd like to be returned into which cell.
data.emails.[0]("confidence")
inC2
anddata.emails[0]("type")
inD2
instead ofB3
andB4
? I added=TRANSPOSE
to match the "everything in one row" behavior – pjmg Aug 7 '19 at 21:27