从Google表格创建JSON对象 [英] Creating a JSON object from Google Sheets
问题描述
我试图学习AppScript,并以Google表格为例。我想创建一个简单的JSON对象,使用工作表中的一些数据。
表格示例
姓名ID价格数量
ABC 123 100 1
DEF 342 56 2
HIJ 233 90 3
IJK 213 68 5
我想让JSON成为像
[
{
姓名:ABC,
ID:123,
价格:100,
数量:1
} ,
{
姓名:DEF,
ID:342,
价格:56,
数量:2
$ bName:HIJ,
ID:233,
价格:90,
数量:3
},
{
姓名:IJK,
ID:213,
价格:68,
数量:5
}
]
此Youtube教程: https://www.youtube.com/watch?v=TQzPI VJf6-W 。但是,该视频讲述了如何将每个列标题创建为对象。我希望列名是键
,Row值是值
。
这是我目前的AppScript代码
函数doGet(){
var result = {};
var rewards = SpreadsheetApp.getActiveSpreadsheet()。getSheetByName('Sheet1')
.getDataRange()
.getValues();
result.rewardObj = makeObject(奖励);
//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(); (var i = 0; i <4; i ++)
{
为(var j = 0; j <4; j ++)
{
$ b // 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:我不是程序员,我只是以一种简单的方式学习一些脚本。对于不知道基础的道歉:)
请尝试:
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{
//获取一行以填充对象
row = data [i];
//清除对象
obj = {};
for(var col = 0; col< cols; col ++)
{
//用新值填充对象
obj [headers [col]] = row [col] ;
}
//在最终结果中添加对象
result.push(obj);
}
返回结果;
$ b $ / code>
测试功能:
函数test_getJsonArrayFromData()
{
var data =
[
['Planet ','大陆','国家','城市'],
['地球','欧洲','英国','伦敦'],
['地球','欧洲', '英国','曼彻斯特'],
['Earth','Europe','Britain','Liverpool'],
['Earth','Europe','France','Paris '],
['Earth','Europe','France','Lion']
];
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表格创建JSON对象的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!