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 :)
See Question&Answers more detail:os