javascript - 谷歌工作表到键控 JSON

Question

我有一个 Google 表格（https://docs.google.com/spreadsheets/d/1sUJkIcxm44idG8pe81IYU6nN9cWgCe_d7ofmYE0OwAo/edit#gid=0），表格中有我想从 javascript 脚本访问的数据。

我制作了一个可以访问工作表并获取数据的代码笔：https ://codepen.io/flennstrom/pen/powzJYb

const spreadsheetId = '1sUJkIcxm44idG8pe81IYU6nN9cWgCe_d7ofmYE0OwAo';
let json="";
fetch('https://docs.google.com/spreadsheets/d/'+spreadsheetId+'/gviz/tq?tqx=out:json&sheet=JSON')
    .then(res => res.text())
    .then(text => {
        json = JSON.parse(text.substr(47).slice(0, -2));
        console.log(json)
    })

但我想将 JSON 结构重新格式化为：

{
   "monosuit_regular": [
      {
         "size": "2XS",
         "LM": 169.85,
         "WM": 62.11,
         "order": 1
      },
      {
         "size": "XS",
         "LM": 173.92,
         "WM": 70.22,
         "order": 2
      },
      {
         "size": "S",
         "LM": 178,
         "WM": 78.32,
         "order": 3
      },
      {
         "size": "MEDIUM",
         "LM": 182.08,
         "WM": 86.43,
         "order": 4
      },
      {
         "size": "LARGE",
         "LM": 186.15,
         "WM": 94.54,
         "order": 5
      },
      {
         "size": "XL",
         "LM": 190.87,
         "WM": 103.29,
         "order": 6
      },
      {
         "size": "2XL",
         "LM": 194.59,
         "WM": 111.04,
         "order": 7
      },
      {
         "size": "3XL",
         "LM": 190.87,
         "WM": 112.16,
         "order": 8
      },
      {
         "size": "4XL",
         "LM": 190.87,
         "WM": 117,
         "order": 9
      }
   ],
   "monosuit_athletic": [
      {
         "size": "2XS",
         "LM": 170.77,
         "WM": 56.85,
         "order": 1
      },
      {
         "size": "XS",
         "LM": 174.85,
         "WM": 64.95,
         "order": 2
      },
      {
         "size": "S",
         "LM": 178.92,
         "WM": 73.06,
         "order": 3
      },
      {
         "size": "M",
         "LM": 183,
         "WM": 81.17,
         "order": 4
      },
      {
         "size": "L",
         "LM": 187.07,
         "WM": 89.28,
         "order": 5
      },
      {
         "size": "XL",
         "LM": 191.79,
         "WM": 98.03,
         "order": 6
      },
      {
         "size": "2XL",
         "LM": 196.51,
         "WM": 106.78,
         "order": 7
      }
   ]
}````

标签： javascriptjsongoogle-sheets

Answer 1

const spreadsheetId = '1sUJkIcxm44idG8pe81IYU6nN9cWgCe_d7ofmYE0OwAo';
fetch('https://docs.google.com/spreadsheets/d/'+spreadsheetId+'/gviz/tq?tqx=out:json&sheet=JSON&header=1')
  .then(res => res.text())
  .then(text => {
    const json = JSON.parse(text.substr(47).slice(0, -2));
    const cols = json.table.cols.slice(1).map(e => e.label);
    const rows = json.table.rows
    const o = {};
    for (const r of rows) {
      const k = r.c[0].v;
      if(!o[k]) { o[k] = []; }
      const v = r.c.slice(1).map((e, i) => { return {[cols[i]]:e.v}; });
      o[k].push(v);
    }
    console.log(o);
  });