1

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.

enter image description here

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.

  • 1
    Hey Jack, can you provide a sample showing us the desired result ? thanks. – pjmg Aug 7 at 15:52
  • Hey @PaulJ thanks for the response! I added some more details, I hope this covers what I mean :) – Jack Aug 7 at 20:16
  • Just answered :) Just to be sure, you want data.emails.[0]("confidence") in C2 and data.emails[0]("type") in D2 instead of B3 and B4 ? I added =TRANSPOSEto match the "everything in one row" behavior – pjmg Aug 7 at 21:27
  • Ah yes good catch! This is why I shouldn't answer so late :) – Jack Aug 8 at 6:37
0

You can stock the data in several var then return them in your function with an array;

Here's a dummy function to illustrate :

  function returndata(data){
  var email = "user@" + data;
  var confidence = 90;
  var type = "personnal";
  return ([email,confidence, type]);

}

Here's what you get:

illustration of the return of an array with a custom google apps script

In your case instead of return (data1.data.emails[0]["value"]);, you can try something like this:

var email = data1.data.emails[0]["value"] ; 
var confidence = data1.data.emails[0]["confidence"];
var type = data1.data.emails[0]["type"];

return ([email, confidence, type]);

Edit: Solution with any random JSON file.

function parsejson(){
  var text = '{ "employees" : [' +
  '{ "firstName":"John" , "lastName":"Doe" },' +
  '{ "firstName":"Anna" , "lastName":"Smith" },' +
  '{ "firstName":"Peter" , "lastName":"Jones" } ]}';

  var obj = JSON.parse(text);

  var result = obj.employees[0]["firstName"];
  var result2 = obj.employees[1]["firstName"];
  var result3 = obj.employees[1]["lastName"];
  return [result, result2,result3];
}

Result:

general use case

  • 1
    This worked perfectly, mapping the data to different variables and then listing the results lists the data in one column, then using the transpose function as suggested returns the data on the same row. This has really helped me, thank you!! – Jack Aug 8 at 6:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.