google-apps-script - 将数据绘制到各自的行

Question

有一组数据需要显示在电子表格中。我的意思是数据将有每一行的值（在本例中为第 1 行、第 2 行等）。我需要在电子表格中显示它们。我如何将这些数据显示到它们各自的行名。我只能找到将绘制数据的范围。

这是我的做法

function plotData() {
 var data = [
  {
    "Row1": "Row1 Data",
    "Row7": 5,
    "WillNotBeInOther": 0.75
  },
  {
    "Row2": 5000,
    "Row3": 0,
    "Row4": 5000,
    "Row5": null,
    "Row6": 5,
    "Row7": null,
    "Row8": 0.25,
    "Row9": 0,
    "Row10": 0
  },
  {
    "Row2": 6000,
    "Row3": 20000,
    "Row4": 26000,
    "Row5": null,
    "Row6": null,
    "Row7": null,
    "Row8": null,
    "Row9": 0,
    "Row10": 0
  },
  {
    "Row2": 7000,
    "Row3": 1000,
    "Row4": 8000,
    "Row5": null,
    "Row6": null,
    "Row7": null,
    "Row8": null,
    "Row9": 0,
    "Row10": 0
  }
]

  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "PlotData";
  var sheet = activeSheet.getSheetByName(sheetname);
  var startRow = 4;
  var lastRow = sheet.getLastRow();
  var plottableSheetRange = sheet.getRange(startRow, 3, lastRow-startRow+1, data.length);
  plottableSheetRange.setValue(10)
}

这是我正在处理的电子表格

https://docs.google.com/spreadsheets/d/1tLNZv4F4lpBAnmHN5H0pBiirW4MVIfTexll9jPA03hI/edit#gid=0

code.gs 是脚本。

它应该看起来像这样

https://docs.google.com/spreadsheets/d/1tLNZv4F4lpBAnmHN5H0pBiirW4MVIfTexll9jPA03hI/edit#gid=1869973739

标签： google-apps-scriptgoogle-sheets

Answer 1

• 您想使用 Google Apps Script 实现从以下对象到图像。

  • 从：

    var data = [
      {"Row1":"Row1Data","Row7":5,"WillNotBeInOther":0.75},
      {"Row2":5000,"Row3":0,"Row4":5000,"Row5":null,"Row6":5,"Row7":null,"Row8":0.25,"Row9":0,"Row10":0},
      {"Row2":6000,"Row3":20000,"Row4":26000,"Row5":null,"Row6":null,"Row7":null,"Row8":null,"Row9":0,"Row10":0},
      {"Row2":7000,"Row3":1000,"Row4":8000,"Row5":null,"Row6":null,"Row7":null,"Row8":null,"Row9":0,"Row10":0}
    ];
    

  • 至：

    

• 您想输入单元格“B4”中的值。

• 作为您对上述值的规范，您只想在第一个索引处使用Row1from的值。{"Row1":"Row1Data","Row7":5,"WillNotBeInOther":0.75}

如果我的理解是正确的，这个答案怎么样？请认为这只是几个答案之一。

在使用以下脚本之前，请在 Advanced Google services 中启用 Sheets API。当数组的所有列长度不一样时，Spreadsheets.Values.update的方法很容易把数组放到Spreadsheet中。所以我使用了 Sheets API。

模式一：

在此模式中，还放置了 from Row1to的键名。Row 10

示例脚本：

function plotData() {
  var data = [
    {"Row1":"Row1Data","Row7":5,"WillNotBeInOther":0.75},
    {"Row2":5000,"Row3":0,"Row4":5000,"Row5":null,"Row6":5,"Row7":null,"Row8":0.25,"Row9":0,"Row10":0},
    {"Row2":6000,"Row3":20000,"Row4":26000,"Row5":null,"Row6":null,"Row7":null,"Row8":null,"Row9":0,"Row10":0},
    {"Row2":7000,"Row3":1000,"Row4":8000,"Row5":null,"Row6":null,"Row7":null,"Row8":null,"Row9":0,"Row10":0}
  ];

  // Convert "data" to an array for putting to Spreadsheet.
  var values = data.reduce(function(ar, e, i) {
    if (i === 0) e = {"Row1": e["Row1"]}; // From your replying, the first index is modified for your specification.
    Object.keys(e).forEach(function(f) {
      var n = Number(f.replace("Row", "")) - 1;
      if (!Array.isArray(ar[n])) ar[n] = ["Row " + (n + 1)];
      ar[n][i + 1] = e[f];
    });
    return ar;
  }, []);

  // Put the converted array to Spreadsheet.
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "PlotData";
  var sheet = activeSheet.getSheetByName(sheetname);
  var startRow = 4;
  var range = "'" + sheetname + "'!B" + startRow;
  Sheets.Spreadsheets.Values.update({values: values}, activeSheet.getId(), range, {valueInputOption: "USER_ENTERED"});
}

模式二：

在此模式中，不放置键名 from Row1to 。Row 10

示例脚本：

function plotData() {
  var data = [
    {"Row1":"Row1Data","Row7":5,"WillNotBeInOther":0.75},
    {"Row2":5000,"Row3":0,"Row4":5000,"Row5":null,"Row6":5,"Row7":null,"Row8":0.25,"Row9":0,"Row10":0},
    {"Row2":6000,"Row3":20000,"Row4":26000,"Row5":null,"Row6":null,"Row7":null,"Row8":null,"Row9":0,"Row10":0},
    {"Row2":7000,"Row3":1000,"Row4":8000,"Row5":null,"Row6":null,"Row7":null,"Row8":null,"Row9":0,"Row10":0}
  ];

  // Convert "data" to an array for putting to Spreadsheet.
  var values = data.reduce(function(ar, e, i) {
    if (i === 0) e = {"Row1": e["Row1"]}; // From your replying, the first index is modified for your specification.
    Object.keys(e).forEach(function(f) {
      var n = Number(f.replace("Row", "")) - 1;
      if (!Array.isArray(ar[n])) ar[n] = [];
      ar[n][i] = e[f];
    });
    return ar;
  }, []);

  // Put the converted array to Spreadsheet.
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "PlotData";
  var sheet = activeSheet.getSheetByName(sheetname);
  var startRow = 4;
  var range = "'" + sheetname + "'!C" + startRow;
  Sheets.Spreadsheets.Values.update({values: values}, activeSheet.getId(), range, {valueInputOption: "USER_ENTERED"});
}

参考：

• 方法：电子表格.values.update