Google 表格 - 解析一个单元格中包含的 JSON 字符串并将特定值提取到另一个单元格

我有一张表,其中 Z 列中的每一行都有一个通过 TAGS 从 Twitter 恢复的 JSON 字符串。

enter image description here

Z 列中的 JSON 字符串都具有类似的结构:

{
  "hashtags": [
    {
      "text": "Negev_Summit",
      "indices": [
        172,
        185
      ]
    }
  ],
  "symbols": [],
  "user_mentions": [
    {
      "screen_name": "JY_LeDrian",
      "name": "Jean-Yves Le Drian",
      "id": 1055021191,
      "id_str": "1055021191",
      "indices": [
        69,
        80
      ]
    }
  ],
  "urls": [],
  "media": [
    {
      "id": 1513588335893258200,
      "id_str": "1513588335893258240",
      "indices": [
        271,
        294
      ],
      "media_url": "http://pbs.twimg.com/media/FQFYknkXoAAxgYd.jpg",
      "media_url_https": "https://pbs.twimg.com/media/FQFYknkXoAAxgYd.jpg",
      "url": "https://twitter.com/yairlapid/status/1513588345468825605",
      "display_url": "pic.twitter.com/dA4cBepIh2",
      "expanded_url": "https://twitter.com/yairlapid/status/1513588345468825605/photo/1",
      "type": "photo",
      "sizes": {
        "medium": {
          "w": 1024,
          "h": 576,
          "resize": "fit"
        },
        "thumb": {
          "w": 150,
          "h": 150,
          "resize": "crop"
        },
        "large": {
          "w": 1024,
          "h": 576,
          "resize": "fit"
        },
        "small": {
          "w": 680,
          "h": 383,
          "resize": "fit"
        }
      }
    }
  ]
}

我需要为 Z 列中的每个 JSON 字符串提取特定值,并将它们放在 AA、AB 和 AC 列(标签、用户提及和 URL)中。

我已经设法通过一个非常肮脏的多重 REGEXREPLACE 公式来实现这一点,但似乎没有办法更有效地实现这一点似乎不合逻辑:

=IFERROR("@"&JOIN(" @",SPLIT(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXEXTRACT(INDIRECT("Y"&ROW()),".*user_mentions"":[(.*)],""urls.*"),"(,""indices"":[d+,d+])",""),"(,""id_str"":""d+"")",""),"(,""id"":d+)",""),"({""screen_name"":"")",""),""",""name"":""(.){1,50}""}",""),",")),"")

理想情况下,我正在寻找一个脚本来解析 JSON 字符串并从 JSON 的每个部分中提取 1 个或多个值。例如:

对于主题标签(AA 栏):

=PARSEJSON(Z1, "hashtags")

结果:

#hashtag1 #hashtag2

对于 user_mentions(AB 列):

=PARSEJSON(Z1, "user_mentions/screen_name")

结果:

@username1 @username2

将不胜感激任何帮助我朝着正确的方向前进。

stack overflow Google Sheets - Parse JSON string contained in one cell and extract specific values to another cell
原文答案

答案:

作者头像

如果您的主要目的是仅获取 screen_name 中的值,我将修改我的 script 并使用 =IMPORTJSON(url, "user_mentions/screen_name")

/**
* Imports JSON data to your spreadsheet Ex: IMPORTJSON("http://myapisite.com","city/population")
* @param url URL of your JSON data as string
* @param xpath simplified xpath as string
* @customfunction
*/
function IMPORTJSON(url,xpath){

  try{
    var res = UrlFetchApp.fetch(url);
    var content = res.getContentText();
    var json = JSON.parse(content);

    var patharray = xpath.split("/");

    for(var i=0;i<patharray.length;i++){
      json = json[patharray[i]];
    }

    if(typeof(json) === "undefined"){
      return "Node Not Available";
    } else if(typeof(json) === "object"){
      var tempArr = [];

      for(var obj in json){
        tempArr.push([obj,json[obj]]);
      }
      return tempArr;
    } else if(typeof(json) !== "object") {
      return json;
    }
  }
  catch(err){
      return "Error getting data";  
  }

}
作者头像

我设法用我发现的另一个脚本 here 来做到这一点。

这是脚本:

function getData(json, path) {
  const obj = JSON.parse(json);
  const keys = path.split('.');
  let current = obj;
  for( key of keys ){
    current = current[key];
  }
  return current;
}

然后,您将使用 =getData(Z1, "hashtags") 输入单元格

作者头像

@Yiddy 的回答对我不起作用。所以我对它做了一些修改并想出了这个。

function getData(range, path, sheet_name) {
    var sprsheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = sprsheet.getSheetByName(sheet_name);

    var string = sheet.getRange(range).getValue();

    var json = JSON.parse(string);
    const keys = path.split('.');

    var current = json;
    for (key of keys) {
        current = current[key];
    }
    return current;
}

相关问题