Solved: Get result from web API from into Sheets - Page 2

Darryl_Baker · 11-06-2020 12:46 AM

Get the definition from free API online dictionary into Sheets

url = “https://api.dictionaryapi.dev/api/v1/entries/en/” & wordToDefine
where WordToDefine = “Cow”
I am new to Sheets and moving from VBA .

I need to call this url (via creating a WinHTTP object and request) and parse the return Json data into the wordToDefine - Definition and its Parts of Speech (eg N,V etc). I can easily do this in VBA and I suspect it is much easier in Google scripts.

VBA code (function) extract:
Set WinHttpReq = CreateObject(“WinHttp.WinHttpRequest.5.1”)
'Get the definition from API online dictionary:
url = “https://api.dictionaryapi.dev/api/v1/entries/en/” & wordToDefine
WinHttpReq.Open “GET”, url, False
’ Send the HTTP Request.
WinHttpReq.Send
'Get the definition Json file in response
myDefinition = StrConv(WinHttpReq.ResponseBody, vbUnicode)

LeventK

@Darryl_Baker

APPS SCRIPT CODE

/**
 * TranslateWord
 * Returns a words dictionary definition
 * @param {string} word string for definition lookup
 * @param {string=} optlang language for definition lookup(default EN)
 * @param {boolean=} optdefonly Definition only (default TRUE)
 * @param {boolean=} optsynonyms Synonyms returned(default FALSE)
 * @param {boolean=} optantonyms Antonyms returned(default FALSE)
 * @return {string} dictionary definition
 */
function TranslateWord(word, lang, defonly, synonyms, antonyms) {
  var optlang = (typeof lang == 'undefined') ? "en" : lang;
  var optdef = (typeof defonly == 'undefined') ? true : defonly;
  var optsyno = (typeof defonly !== 'undefined' && defonly == false && typeof synonyms == 'undefined') ? true : synonyms;
  var optanto = (typeof defonly !== 'undefined' && defonly == false && typeof antonyms == 'undefined') ? true : antonyms;
  var baseURL = "https://api.dictionaryapi.dev/api/v2/entries/"+optlang+"/"+encodeURIComponent(word);
  var response = UrlFetchApp.fetch(baseURL)
  response = JSON.parse(response);
  var parts = response[0].meanings
  var result = ""
  
  if (optdef == true) {
    for (var i = 0; i < parts.length; i++) {
      result = result + "Part of Speech: " + parts[i].partOfSpeech + "\nDefinition: " + parts[i].definitions[0].definition + "\n";
    }
  } else {
    for (var i = 0; i < parts.length; i++) {
      result = result + "Part of Speech: " + parts[i].partOfSpeech + "\nDefinition: " + parts[i].definitions[0].definition + "\nExample: "+ parts[i].definitions[0].example + "\n"
      if (optsyno == true) {
        if (parts[i].definitions[0].synonyms !== undefined) {
          var syno = parts[i].definitions[0].synonyms
          result = result + "Synonims: " + syno + "\n"
        } else {
          result = result + "Synonims: -" + "\n"
        }
      } else {
        result = result
      }
      if (optanto == true) {
        if (parts[i].definitions[0].antonyms !== undefined) {
          var anto = parts[i].definitions[0].antonyms
          result = result + "Antonyms: " + anto + "\n"
        } else {
          result = result + "Antonyms: -" + "\n"
        }
      } else {
        result = result
      }
    }
  }
  result = result.replace(/\n$/gm, "");
  return result
}

PROOF OF CONCEPT

SPREADSHEET FUNCTION USEAGE

=TranslateWord("string"/range, "language", -optional/defonly {boolean}, -optional/synonyms {boolean}, -optional/antonyms {boolean})

For the “string”/range parameter you can enter a string inside the function or enter a valid range
For the language parameter, you should enter a valid 2 letter language code defined by the REST API service. Default is EN. If omitted, default value is cosidered.
defonly parameter is an optional boolean value. Default is TRUE and only the definitions are returned. If omitted, default value is cosidered.
synonyms parameter is an optional boolean value. Default is FALSE and the synonyms are returned in addition. If omitted, default value is cosidered.
antonyns parameter is an optional boolean value. Default is FALSE and the antonyms are returned in addition. If omitted, default value is cosidered.

EXAMPLE FUNCTION USE CASES

=TranslateWord("Hello")
=TranslateWord("Hello","en")
=TranslateWord("Hellor","en",true)

Returns the definitions only for the string “Hello” without synonyms and antonyms if any

=TranslateWord("Hello","en",false)
=TranslateWord("Hello",en",false,true,true)

Returns the definitions for the string “Hello” with the synonyms and antonyms if any

View solution in original post