CODEWITHSUNDEEP
elasticsearchpostgoogle-apps-scriptgoogle-sheets
Imas
Imas

Reputation: 177

Querying ElasticSearch from Google Apps Script?

I am trying to populate a Google Sheet with data from ElasticSearch (ES). I haven’t found any resources that show how to go from ElasticSearch -> Google Sheets, but I thought I’d try it out. Using Apps Script, I am attempting to make a request to my ES index, but have been failing to do so. I am approaching the query as such:

// my .gs file - I call readFromTable() to run

var payload = {
  "size": 1000,
  "query": {
    "match_all": {}
  }
}

var options = {
  "method": "post",
  "contentType": "application/json",
  "payload": JSON.stringify(payload),
  "headers": {
    "Authorization": "Basic " + Utilities.base64Encode(username + ':' + password)
  },
};

function readFromTable() {
  Logger.log(options);
  var response = UrlFetchApp.fetch("https://" + ES_URL + ":9243/" + index_name + "/_search", options);
  Logger.log('Done.');
  Logger.log(response);
}

Is there anything I am missing here? I continue getting a DNS error, but I am unsure where my mistake may be. Has anyone done this before?

I really appreciate your help. Thanks!

Upvotes: 1

Views: 612

Answers (1)

chuckx
chuckx

Reputation: 6937

Apps Script executes on Google servers and the network requests initiated by UrlFetchApp.fetch() originate from Google servers. If the resource you want to access isn't publicly accessible, then you won't be able to reach it via UrlFetchhApp.

For reference, here's an excerpt from the UrlFetchApp documentation:

This service allows scripts to communicate with other applications or access other resources on the web by fetching URLs. A script can use the URL Fetch service to issue HTTP and HTTPS requests and receive responses. The URL Fetch service uses Google's network infrastructure for efficiency and scaling purposes.

Requests made using this service originate from a set pool of IP ranges. You can look up the full list of IP addresses if you need to whitelist or approve these requests.

An alternative approach would be writing a program that runs within your environment (i.e. on a system that has access to your Elastic Search instance). This program could then use the Sheets API to output results to a Google Sheet.

There are client libraries provided to facilitate writing such a program in a variety of languages. For instance, if you want to stick to Javascript, you can utilize the Node.js client libraries.

Upvotes: 1

Related Questions