如何解析Google中的Telnyx JSON

Question

我正在寻找一种方法来解析Google电子表格中使用的单元公式中从Telnyx (通过web钩子)接收到的JSON。理想的结果是将每一行的数据放在单独的列中(包括没有数据的行)，并带有正确的标题。联合来文也是一种选择。

我尝试过使用FILTER和REGEXMATCH，并取得了一些结果，但我无法获得一个不错的输出。

任何帮助都将不胜感激。

从Telnyx收到的数据如下所示：

"{
 ""data"": {
 ""event_type"": ""message.received"",
 ""id"": ""0d9c22"",
 ""occurred_at"": ""2022-07-23T04:52:08.642+00:00"",
 ""payload"": {
 ""cc"": [],
 ""completed_at"": null,
 ""cost"": null,
 ""direction"": ""inbound"",
 ""encoding"": ""GSM-7"",
 ""errors"": [],
 ""from"": {
 ""carrier"": """",
 ""line_type"": """",
 ""phone_number"": ""+447""
 },
 ""id"": ""eb17"",
 ""media"": [],
 ""messaging_profile_id"": ""4001"",
 ""organization_id"": ""8ab"",
 ""parts"": 2,
 ""received_at"": ""2022-07-20T04:52:08.464+00:00"",
 ""record_type"": ""message"",
 ""sent_at"": null,
 ""subject"": """",
 ""tags"": [],
 ""text"": ""SMS goes here.\nThanks"",
 ""to"": [
 {
 ""carrier"": ""Telnyx"",
 ""line_type"": ""Wireless"",
 ""phone_number"": ""+447"",
 ""status"": ""webhook_delivered""
 }
 ],
 ""type"": ""SMS"",
 ""valid_until"": null,
 ""webhook_failover_url"": null,
 ""webhook_url"": ""https://script.google.com/XXXXX""
 },
 ""record_type"": ""event""
 },
 ""meta"": {
 ""attempt"": 1,
 ""delivered_to"": ""https://script.google.com/""
 }
}"

Answer 1

尝试这个递归函数(A1包含数据)

=functionTelnyx(A1)

加入你的脚本编辑器

function functionTelnyx(json) {
  const telnyx = JSON.parse(json.slice(1,-1).replace(/""/g,'"').replace(/(\r\n|\n|\r|\t|  )/gm, ""))
  let result = [];
  recursion(telnyx)
  function recursion(obj, path) {
    if (path == undefined) { path = 'telnyx' }
    const regex = new RegExp('[^0-9]+');
    for (let p in obj) {
      let newPath = (regex.test(p)) ? path + '.' + p : path + '[' + p + ']';
      if (obj[p] != null) {
        if (typeof obj[p] != 'object' && typeof obj[p] != 'function') {
          result.push([ p, obj[p]]);
        }
        if (typeof obj[p] == 'object') {
          recursion(obj[p], newPath);
        }
      }
    }
  }
  return result
}

​

​

编辑：

用于多个json和水平布局。

• 对于标头，放入B1 =telnyx_headers(A2)
• 对于内容，输入B2并拖到=telnyx_contents(A2)下面，然后拖动=telnyx_contents(A3)等等。

使用

function telnyx_headers(json) {
  const telnyx = JSON.parse(json.slice(1, -1).replace(/""/g, '"').replace(/(\r\n|\n|\r|\t|  )/gm, ""))
  let result = [];
  recursion(telnyx)
  function recursion(obj, path) {
    if (path == undefined) { path = 'telnyx' }
    const regex = new RegExp('[^0-9]+');
    for (let p in obj) {
      let newPath = (regex.test(p)) ? path + '.' + p : path + '[' + p + ']';
      if (obj[p] != null) {
        if (typeof obj[p] != 'object' && typeof obj[p] != 'function') {
          result.push(p);
        }
        if (typeof obj[p] == 'object') {
          recursion(obj[p], newPath);
        }
      }
    }
  }
  return [result]
}
function telnyx_contents(json) {
  const telnyx = JSON.parse(json.slice(1, -1).replace(/""/g, '"').replace(/(\r\n|\n|\r|\t|  )/gm, ""))
  let result = [];
  recursion(telnyx)
  function recursion(obj, path) {
    if (path == undefined) { path = 'telnyx' }
    const regex = new RegExp('[^0-9]+');
    for (let p in obj) {
      let newPath = (regex.test(p)) ? path + '.' + p : path + '[' + p + ']';
      if (obj[p] != null) {
        if (typeof obj[p] != 'object' && typeof obj[p] != 'function') {
          result.push(obj[p]);
        }
        if (typeof obj[p] == 'object') {
          recursion(obj[p], newPath);
        }
      }
    }
  }
  return [result]
}

可选:启用数组的版本

要在列A2:A中处理更多的JSON字符串，请使用以下公式：

=telnyx(A2:A)

telnyx()函数在观察自定义函数最佳实践时使用telnyx_headers()和telnyx_contents()。

/**
* Converts one or more JSON strings to a horizontal table.
* Row 1 will show field names and subsequent rows the
* data in each field.
*
* @param {A2:A} json The JSON strings to convert to a table.
* @customfunction
*/
function telnyx(json) {
  'use strict';
  if (!Array.isArray(json)) {
    json = [json];
  }
  json = json.flat();
  let result = telnyx_headers(json[0]);
  json.forEach(j => result = result.concat(j ? telnyx_contents(j) : [null]));
  return result;
}