处理GoogleSheets上IMPORTJSON脚本返回的多行 [英] Handling multiple rows returned by IMPORTJSON script on GoogleSheets

问题描述

我正在尝试使用API​​填充Google工作表.但是，对于单个查询，该API有多个行要返回.以下是API返回的JSON.

I am trying to populate a google sheet using an API. But the API has more than one row to be returned for a single query. Following is the JSON returned by API.

# https://api.dictionaryapi.dev/api/v2/entries/en/ABANDON
[
    {
        "word": "abandon",
        "phonetics": [
            {
                "text": "/əˈbændən/",
                "audio": "https://lex-audio.useremarkable.com/mp3/abandon_us_1.mp3"
            }
        ],
        "meanings": [
            {
                "partOfSpeech": "transitive verb",
                "definitions": [
                    {
                        "definition": "Cease to support or look after (someone); desert.",
                        "example": "her natural mother had abandoned her at an early age",
                        "synonyms": [
                            "desert",
                            "leave",
                            "leave high and dry",
                            "turn one's back on",
                            "cast aside",
                            "break with",
                            "break up with"
                        ]
                    },
                    {
                        "definition": "Give up completely (a course of action, a practice, or a way of thinking)",
                        "example": "he had clearly abandoned all pretense of trying to succeed",
                        "synonyms": [
                            "renounce",
                            "relinquish",
                            "dispense with",
                            "forswear",
                            "disclaim",
                            "disown",
                            "disavow",
                            "discard",
                            "wash one's hands of"
                        ]
                    },
                    {
                        "definition": "Allow oneself to indulge in (a desire or impulse)",
                        "example": "they abandoned themselves to despair",
                        "synonyms": [
                            "indulge in",
                            "give way to",
                            "give oneself up to",
                            "yield to",
                            "lose oneself in",
                            "lose oneself to"
                        ]
                    }
                ]
            },
            {
                "partOfSpeech": "noun",
                "definitions": [
                    {
                        "definition": "Complete lack of inhibition or restraint.",
                        "example": "she sings and sways with total abandon",
                        "synonyms": [
                            "uninhibitedness",
                            "recklessness",
                            "lack of restraint",
                            "lack of inhibition",
                            "unruliness",
                            "wildness",
                            "impulsiveness",
                            "impetuosity",
                            "immoderation",
                            "wantonness"
                        ]
                    }
                ]
            }
        ]
    }
]

通过IMPORTJSON使用以下调用，

By using the following calls via IMPORTJSON,

=ImportJSON(CONCATENATE("https://api.dictionaryapi.dev/api/v2/entries/en/"&$A2), "/phonetics/text", "noHeaders")
=ImportJSON(CONCATENATE("https://api.dictionaryapi.dev/api/v2/entries/en/"&$A2), "/meanings/partOfSpeech", "noHeaders")
=ImportJSON(CONCATENATE("https://api.dictionaryapi.dev/api/v2/entries/en/"&$A2), "/meanings/definitions/definition", "noHeaders")
=ImportJSON(CONCATENATE("https://api.dictionaryapi.dev/api/v2/entries/en/"&$A2), "/meanings/definitions/synonyms", "noHeaders")
=ImportJSON(CONCATENATE("https://api.dictionaryapi.dev/api/v2/entries/en/"&$A2), "/meanings/definitions/example", "noHeaders")

我能够在GoogleSheets中获得以下信息，

I am able to get the following in GoogleSheets,

鉴于此，根据JSON的实际输出应为

Whereas, the actual output according to JSON should be,

您可以看到完整的行被覆盖.该如何解决?

As you can see a complete row is being overwritten. How can this be fixed?

编辑

以下是指向表的链接，仅供查看

Following is the link to sheet for viewing only.

推荐答案

我相信您的目标如下.

• 您想在问题上在Google Spreadsheet上获得最底下的形象.

不幸的是，我找不到使用ImportJson直接检索底部图像的方法.因此，在这个答案中，我想提出一个示例脚本，以使用Google Apps脚本检索您期望的值.我认为创建示例脚本来直接实现目标可能比修改ImportJson更为简单.

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.

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;
}

• 使用此脚本时，请将=SAMPLE(CONCATENATE("https://api.dictionaryapi.dev/api/v2/entries/en/"&$A2))作为自定义公式放在单元格中.
• 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

• 在此示例脚本中，当更改JSON对象的结构时，可能无法使用它.所以请注意这一点.
• UrlFetchApp类
• Google表格中的自定义功能
• Class UrlFetchApp
• Custom Functions in Google Sheets

这篇关于处理GoogleSheets上IMPORTJSON脚本返回的多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！