Get result from web API from into Sheets

[Darryl_Baker]

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]

How do you want to trigger the Google Apps Script you have created? Via app entry and sync?

[Darryl_Baker]

I want to call it via a function from a cell in Google sheets; passing the Word to define as the single parameter, for example "=TranslateWord(“The WordToDefine”, “DefinitionOnly”)

Something like the GoogleTranslate function which is called from sheets as =googletranslate(“test word”, “en”,“vi”).

[LeventK]

Gotcha. You want to make it work like a UDF. I will construct the script for you and post it here.

[LeventK · Accepted solution]

@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

[Darryl_Baker]

Thank you. Works 100%. Everything I asked for and more. Your complete documentation is greatly appreciated.