从 Google Sheets 创建一个 JSON 对象 [英] Creating a JSON object from Google Sheets

问题描述

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

表格示例

<前>名称 ID 价格 数量ABC 123 100 1防御 342 56 2HIJ 233 90 3213 68 5

我希望 JSON 类似于

<预><代码>[{"名称": "ABC","ID": "123",价格":100，数量":1},{"名称": "DEF","ID": "342",价格":56，数量":2},{"名称": "HIJ","ID": "233",价格":90，数量":3},{"名称": "IJK","ID": "213",价格":68，数量":5}]

我从这个 Youtube 教程开始:https://www.youtube.com/看?v=TQzPIVJf6-w.但是，该视频谈到将每个列标题创建为对象.因为我希望列名是 key，而行值是 value.

这是我当前的 AppScript 代码

function doGet() {变量结果={};var Rewards = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1').getDataRange().getValues();result.rewardObj = makeObject(rewards);//Logger.log(result.rewardObj);返回 ContentService.createTextOutput(JSON.stringify(result)).setMimeType(ContentService.MimeType.JSON)}函数 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]);返回对象；}

输出是单个对象

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

PS:我不是程序员，我只是在以一种黑客的方式学习一些脚本.抱歉不了解基础知识:)

解决方案

请尝试:

函数 getJsonArrayFromData(data){var obj = {};var 结果 = [];var 标头 = 数据 [0];var cols = headers.length;var 行 = [];for (var i = 1, l = data.length; i 

测试功能:

函数 test_getJsonArrayFromData(){变量数据 =[['行星'，'大陆'，'国家'，'城市']，['地球'，'欧洲'，'英国'，'伦敦']，['地球'，'欧洲'，'英国'，'曼彻斯特']，['地球'，'欧洲'，'英国'，'利物浦']，['地球'，'欧洲'，'法国'，'巴黎']，['地球'、'欧洲'、'法国'、'狮子']];Logger.log(getJsonArrayFromData(data));//=>[{大陆=欧洲，国家=英国，行星=地球，城市=伦敦}，{大陆=欧洲，国家=英国，行星=地球，城市=曼彻斯特}，{大陆=欧洲，国家=英国，行星=地球，城市=利物浦}，{大陆=欧洲，国家=法国，行星=地球，城市=巴黎}，{大陆=欧洲，国家=法国，行星=地球，城市=狮子}]}

I am trying to learn AppScript and I am using Google Sheets as an example. I want to create a simple JSON object using some data populated in the Sheet.

Table Example

Name    ID   Price   Qty
ABC     123  100      1
DEF     342   56      2
HIJ     233   90      3
IJK     213   68      5

I want the JSON out to be something like

[
  {
    "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
  }
]

I started by following this Youtube tutorial : https://www.youtube.com/watch?v=TQzPIVJf6-w. However that video talks about creating each column header as a the object. Where as i want the Column name to be the key and the Row value to be the value.

Here is my current AppScript Code

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

The output is a single object

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

PS: I am not a programmer, I am just learning some scripting in a hackey way. Apologies for not knowing the basics :)

解决方案

Please try:

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;  

}

Test function:

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

}

这篇关于从 Google Sheets 创建一个 JSON 对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！