Handling multiple rows returned by IMPORTJSON script on GoogleSheets

I believe your goal as follows.

  • You want to achieve the bottom image in your question on Google Spreadsheet.

Unfortunately, I couldn’t find the method for directly retrieving the bottom image using ImportJson. So in this answer, I would like to propose a sample script for retrieving the values you expect using Google Apps Script. I thought that creating a sample script for directly achieving your goal might be simpler rather than modifying ImportJson.

Sample script:

function SAMPLE(url) {
  var res = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
  if (res.getResponseCode() != 200) return res.getContentText();

  var obj = JSON.parse(res.getContentText());
  var values = obj[0].meanings.reduce((ar, {partOfSpeech, definitions}, i) => {
    definitions.forEach(({definition, example, synonyms}, j) => {
      var v = [definition, Array.isArray(synonyms) ? synonyms.join(",") : synonyms, example];
      var phonetics = obj[0].phonetics[i];
      ar.push(j == 0 ? [(phonetics ? phonetics.text : ""), partOfSpeech, ...v] : ["", "", ...v]);
    return ar;
  }, []);
  return values;
  • When you use this script, please put =SAMPLE(CONCATENATE("https://api.dictionaryapi.dev/api/v2/entries/en/"&$A2)) to a cell as the custom formula.


When above script is used, the following

enter image description here


  • In this sample script, when the structure of the JSON object is changed, it might not be able to be used. So please be careful this.


CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top