如何将值映射到其对应的行名 [英] how to map values to its corresponding row name

问题描述

有一组数据需要在电子表格中显示.我的意思是数据将具有每一行的值(在这种情况下为Housing，CapitalRaised，SizePerSquare等).如何以以下格式在电子表格中绘制以下数据?

There is a collection of data which needs to be shown in the spreadsheet. I mean data will have values for each row(in this case Housing, CapitalRaised, SizePerSquare etc). How to plot below data in the spreadsheet in following format?

数据采用这种格式

[
  {
    "Housing": "Before housing price",
    "Price": 5,
    "Rate": 0.75
  },
  {
    "CapitalRaised": 5000,
    "SizePerSquare": 12,
    "Price": null,
    "RatePerSquare": 1.25
  },
  {
    "CapitalRaised": 6000,
    "SizePerSquare": 24,
    "Price": null,
    "RatePerSquare": 1
  },
  {
    "CapitalRaised": 7000,
    "SizePerSquare": 24,
    "Price": null,
    "RatePerSquare": 0.75,
  }
]

这是代码

function plotData() {
  var data =[
    {
        "Housing": "Before Capital Raised",
        "Price": 5,
        "Rate": 0.75
    },
    {
        "CapitalRaised": 5000,
        "SizePerSquare": 12,
        "Price": null,
        "RatePerSquare": 1.25
    },
    {
        "CapitalRaised": 6000,
        "SizePerSquare": 24,
        "Price": null,
        "RatePerSquare": 1
    },
    {
        "AmountRaised": 7000,
        "SizePerSquare": 24,
        "Price": null,
        "RatePerSquare": 0.75,
    }
  ]

  var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "PlotData2";
  var sheet = activeSheet.getSheetByName(sheetname);
  var startRow = 4;
  var range = "'" + sheetname + "'!B" + startRow;

}

这是电子表格的链接

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

推荐答案

• 您想要使用Google Apps脚本实现以下情况.

  • You want to achieve the following situation using Google Apps Script.

    • 发件人:

    • From:

    var data = [
      {"Housing":"Before housing price","Price":5,"Rate":0.75},
      {"CapitalRaised":5000,"SizePerSquare":12,"Price":null,"RatePerSquare":1.25},
      {"CapitalRaised":6000,"SizePerSquare":24,"Price":null,"RatePerSquare":1},
      {"CapitalRaised":7000,"SizePerSquare":24,"Price":null,"RatePerSquare":0.75}
    ];
    

  • 收件人:

  • To:

    行的标题是恒定的.

    如果我的理解是正确的，那么这个答案如何?在这个答案中，从您的问题来看，我认为行的标题是恒定的.我用这种情况.

    If my understanding is correct, how about this answer? In this answer, from your question, I thought that the header titles of rows are constant. I used this situation.

    在使用以下脚本之前，请在高级处启用Sheets API Google服务.如果数组的所有列长度都不相同，则Spreadsheets.Values.update的方法很容易将数组放入Spreadsheet.所以我使用了Sheets API.

    Before you use the following scripts, please enable Sheets API at Advanced Google services. When the all column length of the array is not the same, the method of Spreadsheets.Values.update is easy to put the array to Spreadsheet. So I used Sheets API.

    function plotData() {
      var data = [
        {"Housing":"Before housing price","Price":5,"Rate":0.75},
        {"CapitalRaised":5000,"SizePerSquare":12,"Price":null,"RatePerSquare":1.25},
        {"CapitalRaised":6000,"SizePerSquare":24,"Price":null,"RatePerSquare":1},
        {"CapitalRaised":7000,"SizePerSquare":24,"Price":null,"RatePerSquare":0.75}
      ];
    
      // Convert "data" to an array for putting to Spreadsheet.
      var rowHeaders = ["Housing", "Price", "CapitalRaised", "RatePerSquare", "SizePerSquare"];
      var values = data.reduce(function(ar, e, i) {
        rowHeaders.forEach(function(g, n) {
          if (!Array.isArray(ar[n])) ar[n] = [g];
          ar[n][i + 1] = e[g];
        });
        return ar;
      }, []);
    
      // Put the converted array to Spreadsheet.
      var activeSheet = SpreadsheetApp.getActiveSpreadsheet();
      var sheetname = "PlotData2";
      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"});
    }
    

    • 在上面的脚本中，如果data对象的键中包含rowHeaders中未包含的键，则不使用这些键的值.请注意这一点.
      • 例如，不使用data的第一个元素的Rate.
      • In above script, if there are the keys, which are not included in rowHeaders, in the keys of object of data, the values of the keys are not used. Please be careful this.
        • For example, Rate of the first element of data is not used.
        • 在您的数据中，似乎the data comes in this format的数据与Here is the code的数据不同. "CapitalRaised": 7000和"AmountRaised": 7000不同.从您预期结果的图像来看，我认为您想使用the data comes in this format的数据.如果我误解了您的目标，我深表歉意.
        • In your data, it seems that the data of the data comes in this format is different from the data of Here is the code. "CapitalRaised": 7000, and "AmountRaised": 7000 are different. From your image of the expected result, I supposed that you want to use the data of the data comes in this format. If I misunderstood your goal, I apologize.
        • 方法:sheets.values.update

        如果我误解了你的问题，而这不是你想要的方向，我深表歉意.

        If I misunderstood your question and this was not the direction you want, I apologize.

        这篇关于如何将值映射到其对应的行名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！