一尘不染

从Google表格创建JSON对象

json

我正在尝试学习AppScript，并以Google表格为例。我想使用工作表中填充的一些数据创建一个简单的JSON对象。

表例

名称编号价格数量
ABC 123100 1
防守342 56 2
HIJ 233 90 3
IJK 213 68 5

我希望JSON像这样

[
  {
    "Name": "ABC",
    "ID": "123",
    "Price": 100,
    "Qty": 1
  },
  {
    "Name": "DEF",
    "ID": "342",
    "Price": 56,
    "Qty": 2
  },
  {
    "Name": "HIJ",
    "ID": "233",
    "Price": 90,
    "Qty": 3
  },
  {
    "Name": "IJK",
    "ID": "213",
    "Price": 68,
    "Qty": 5
  }
]

watch？v

TQzPIVJf6-w](https://www.youtube.com/watch?v=TQzPIVJf6-w)。但是，该视频谈到了将每个列标题创建为一个对象。如我所希望的，列名是key和行值是value。

这是我当前的AppScript代码

function doGet() {
  var result={};
  var rewards = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')
  .getDataRange()
  .getValues();

  result.rewardObj = makeObject(rewards);
  //Logger.log(result.rewardObj);
  return ContentService.createTextOutput(JSON.stringify(result))
  .setMimeType(ContentService.MimeType.JSON)


}

function makeObject(multiArray)
{
  var obj = {}; 
  var colNames = multiArray.shift();
  var rowNames = multiArray.slice(0,1);
  var rowCount = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getLastRow();
  var colCount = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getLastColumn();

  for(var j=0;j<4;j++)
  {
    for(var i=0;i<4;i++)
      {
        //obj[colNames] = rowNames.map(function(item){return item[i];});
        obj[colNames[j][i]] = multiArray[j][i];
      }
  }
  Logger.log(rowCount)
  Logger.log(colCount)
  Logger.log(multiArray[57][12]);
  return obj;
}

输出是单个对象

{"rewardObj":{"Name":"ABC","ID":"123","Price":"100","Qty":"1"}}

PS：我不是程序员，我只是以一种hackey的方式学习一些脚本。抱歉，不了解基本知识:)

阅读 249

2020-07-27

共1个答案

一尘不染

请尝试：

function getJsonArrayFromData(data)
{

  var obj = {};
  var result = [];
  var headers = data[0];
  var cols = headers.length;
  var row = [];

  for (var i = 1, l = data.length; i < l; i++)
  {
    // get a row to fill the object
    row = data[i];
    // clear object
    obj = {};
    for (var col = 0; col < cols; col++) 
    {
      // fill object with new values
      obj[headers[col]] = row[col];    
    }
    // add object in a final result
    result.push(obj);  
  }

  return result;

}

测试功能：

function test_getJsonArrayFromData()
{
  var data =   
    [
      ['Planet', 'Mainland', 'Country', 'City'],
      ['Earth', 'Europe', 'Britain', 'London'],
      ['Earth', 'Europe', 'Britain', 'Manchester'],
      ['Earth', 'Europe', 'Britain', 'Liverpool'],
      ['Earth', 'Europe', 'France', 'Paris'],
      ['Earth', 'Europe', 'France', 'Lion']
    ];

    Logger.log(getJsonArrayFromData(data));

    // =>  [{Mainland=Europe, Country=Britain, Planet=Earth, City=London}, {Mainland=Europe, Country=Britain, Planet=Earth, City=Manchester}, {Mainland=Europe, Country=Britain, Planet=Earth, City=Liverpool}, {Mainland=Europe, Country=France, Planet=Earth, City=Paris}, {Mainland=Europe, Country=France, Planet=Earth, City=Lion}]

}

2020-07-27