Google-sheets – Offset called JSON into the next column

google sheetsgoogle-apps-script

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.

Best Answer

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

Related Topic