从Google Sheets创建一个JSON对象

Question

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

表格示例

名称    ID    价格   数量
ABC   123   100     1
DEF   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
  }
]

我开始跟着这个Youtube教程： 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：我不是一个程序员，我只是以一种拙劣的方式学习一些脚本。不懂基础知识，对此表示歉意：）

- maverick340

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

}

- Max Makhrov

---

太棒了，它完美地运行了。非常感谢你。我正在学习代码以理解它的作用。如果您能添加一些注释，那将非常有帮助。我认为我犯的主要错误是返回对象 obj 而不是数组，对吗？ - maverick340

@maverick340 它正在从第一行（您的列标题）中获取值并将其设置为键。然后它会获取您所在行的值，并将其设置为值。希望这可以帮助！ - Jordan Rhea

@maverick340，你可以在每个步骤中使用Logger.log()，运行脚本并按下Ctrl + Enter以查看日志。这可能有助于查看中间结果。 - Max Makhrov

@JordanRhea：感谢您的初始视频教程和解释。 - maverick340

这太棒了！我已经完成了同样代码的一半，但是这个代码片段正好符合我的需求。感谢您提供如此简单易懂的代码 :) - Praveen Jayapal

Answer 2

我知道这有点晚了，但我想到了一个更加灵活的解决方案。我创建了一个名为 "aptly named" 的函数，它可以在给定一组标题和一组行的情况下，将电子表格数据转换为对象。

function convertToObjects(headers, rows)
{
  return rows.reduce((ctx, row) => {
    ctx.objects.push(ctx.headers.reduce((item, header, index) => {
      item[header] = row[index];
      return item;
    }, {}));
    return ctx;
  }, { objects: [], headers}).objects;
}

你可以像这样调用上面的函数：

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('YourSheetName');
var [headers, ...rows] = sheet.getDataRange().getValues();
var objects = convertToObjects(headers, rows);

如果你需要忽略第一行并从第二行获取表头（就像我需要的那样），那么你可以采用以下方法：

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('YourSheetName');
var [_, headers, ...rows] = sheet.getDataRange().getValues();
var objects = convertToObjects(headers, rows);

如果您对此有任何改进意见，请在下面的评论中告诉我。

- Ryan Haney

---

非常有帮助！谢谢！ - Ryan

Answer 3

编辑 2022 年：列标题和行标题如下

你的Json名称	列1	列2	列3	列4
行1	1	2	3
行2	4		6	4
行3	7	8	9	8

尝试

function myJson() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('YourSheetName');
  var [headers, ...rows] = sheet.getDataRange().getValues();
  var jsonString = `var ${sheet.getRange(1,1).getValue()} = ${JSON.stringify(data2json(headers, rows))}`
  Logger.log (jsonString)
}
function data2json(headers, rows) {
  var obj = {},items = {};
  for (var i in rows) {
    obj = {};
    for (var col = 1; col < headers.length; col++) {
      if(rows[i][col]!='') obj[headers[col]] = rows[i][col];
    }
    items[rows[i][0]] = obj
  }
  return items;
}

var [headers, ...rows] = [['yourJsonName', 'col1', 'col2', 'col3', 'col4'], ['row1', 1.0, 2.0, 3.0, ], ['row2', 4.0, , 6.0, 4.0], ['row3', 7.0, 8.0, 9.0, 8.0]]
console.log (`var ${headers[0]} = ${JSON.stringify(data2json(headers, rows))}`)
function data2json(headers, rows) {
  var obj = {},items = {};
  for (var i in rows) {
    obj = {};
    for (var col = 1; col < headers.length; col++) {
      if(rows[i][col]!='') obj[headers[col]] = rows[i][col];
    }
    items[rows[i][0]] = obj
  }
  return items;
}

保存到驱动器中...根据需要调整名称（YourSheetName，YourFolderName，YourFileName）

function myJson() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('YourSheetName');
  var [headers, ...rows] = sheet.getDataRange().getValues();
  var jsonString = `var ${sheet.getRange(1,1).getValue()} = ${JSON.stringify(data2json(headers, rows))}`
  var folders = DriveApp.getFoldersByName('YourFolderName');
  if (folders.hasNext()) {
    var folder = folders.next();
    saveData(folder, 'YourFileName.json',jsonString);
  }
}
function data2json(headers, rows) {
  var obj = {},items = {};
  for (var i in rows) {
    obj = {};
    for (var col = 1; col < headers.length; col++) {
      if(rows[i][col]!='') obj[headers[col]] = rows[i][col];
    }
    items[rows[i][0]] = obj
  }
  return items;
}
function saveData(folder, fileName, content) {
  var children = folder.getFilesByName(fileName);
  var file = null;
  if (children.hasNext()) {
    file = children.next();
    file.setContent(content);
  } else {
    file = folder.createFile(fileName, content);
  }
  Logger.log(file.getUrl())
}

- Mike Steelson