将第一行存储为对象[名称]，获取对应的核心行/列值 [英] Storing the first row as object[name], getting coresponding row/column value

问题描述

我正在寻找一种遍历Google工作表的方式，以将第一行存储为对象键，并将该行/列的相应值放置在object [key] = value中.

I'm looking for a way loop through a Google sheet to store the first row as an object key and place the corresponding value of the row/column in the object[key] = value.

这就是我希望导出的内容

This is what I'm hoping to export

//row 1 values [{objName1:value1_1},    {objName2:value1_2},    {objName3:value1_3},    {objName4:value1_4},    {objName5:value1_5},    {objName6:value1_6},    {objName7:value1_7},    {objName8:value1_8}]
//row 2 values [{objName1:value2_1},    {objName2:value2_2},    {objName3:value2_3},    {objName4:value2_4},    {objName5:value2_5},    {objName6:value2_6},    {objName7:value2_7},    {objName8:value2_8}]
//row 3 values [{objName1:value3_1},    {objName2:value3_2},    {objName3:value3_3},    {objName4:value3_4},    {objName5:value3_5},    {objName6:value3_6},    {objName7:value3_7},    {objName8:value3_8}]

这是我的Google工作表 https://docs.google.com/spreadsheets/d33nXbQ00edit?usp = sharing

Here is my google sheet https://docs.google.com/spreadsheets/d/1nXbQW_UQzY0HA33c3n42A5w6_LGbFB3UmEOg0dxUOd0/edit?usp=sharing

应用脚本功能

function messageToJSON(){

  var sheetName = 'Sheet1';
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);

  var rangeData = sheet.getDataRange();
  var lastColumn = rangeData.getLastColumn();
  var lastRow = rangeData.getLastRow();

  var searchRange = sheet.getRange(1,1, lastRow, lastColumn)
  var rangeValues = searchRange.getValues();

  var messExSs = SpreadsheetApp.getActiveSpreadsheet();
  var messExShName = 'messageExport';
  var messExSheet = messExSs.getSheetByName(messExShName);

  var msgObj = {};
  var result = [];

  var celObj = {};
  var getCel = [];




  for ( i = 0; i < lastColumn; i++){ // columes ➔       

    for (j = 1 ; j < lastRow; j++){ // rows ⬇︎ //  
      var curCol = rangeValues[0][i];
      var curRow = rangeValues[j][0];
      var curCel = rangeValues[j][i];
      getCel.push(curCol);

      if(!(curCel === "")){

          result.push(msgObj[curCol] = curRow);

          messString = JSON.stringify(msgObj);
          messExSheet.getRange(j, 1).setValue(j);
          messExSheet.getRange(j, 1+1).setValue(messString); 
        }
     };      
   };

}




// This is what it exports currently, the third row is overwriting every loop

//Export
//row 1 values [{objName1:value3_1},    {objName2:value3_2},    {objName3:value3_3},    {objName4:value3_4},    {objName5:value3_5},    {objName6:value3_6},    {objName7:value3_7},    {objName8:value3_8}]
//row 2 values [{objName1:value3_1},    {objName2:value3_2},    {objName3:value3_3},    {objName4:value3_4},    {objName5:value3_5},    {objName6:value3_6},    {objName7:value3_7},    {objName8:value3_8}]
//row 3 values [{objName1:value3_1},    {objName2:value3_2},    {objName3:value3_3},    {objName4:value3_4},    {objName5:value3_5},    {objName6:value3_6},    {objName7:value3_7},    {objName8:value3_8}]

推荐答案

我相信您的目标如下.

• 您想要实现以下转换.

• You want to achieve the following conversion.

• 发件人:电子表格上的单元格

• From: cells on Spreadsheet

objName1    objName2    objName3    objName4    objName5    objName6    objName7    objName8
value1_1    value1_2    value1_3    value1_4    value1_5    value1_6    value1_7    value1_8
value2_1    value2_2    value2_3    value2_4    value2_5    value2_6    value2_7    value2_8
value3_1    value3_2    value3_3    value3_4    value3_5    value3_6    value3_7    value3_8

收件人:对象

To: Object

[{objName1:value1_1}, {objName2:value1_2}, {objName3:value1_3}, {objName4:value1_4}, {objName5:value1_5}, {objName6:value1_6}, {objName7:value1_7}, {objName8:value1_8}]
[{objName1:value2_1}, {objName2:value2_2}, {objName3:value2_3}, {objName4:value2_4}, {objName5:value2_5}, {objName6:value2_6}, {objName7:value2_7}, {objName8:value2_8}]
[{objName1:value3_1}, {objName2:value3_2}, {objName3:value3_3}, {objName4:value3_4}, {objName5:value3_5}, {objName6:value3_6}, {objName7:value3_7}, {objName8:value3_8}]

为此，该修改如何?

function messageToJSON(){

  var sheetName = 'Sheet1';
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);

  var rangeData = sheet.getDataRange();
  var lastColumn = rangeData.getLastColumn();
  var lastRow = rangeData.getLastRow();

  var searchRange = sheet.getRange(1,1, lastRow, lastColumn)
  var rangeValues = searchRange.getValues();

  var messExSs = SpreadsheetApp.getActiveSpreadsheet();
  var messExShName = 'messageExport';
  var messExSheet = messExSs.getSheetByName(messExShName);


  // I modified below script.
  // 1. Retrieve the header row.
  const header = rangeValues.shift();

  // 2. Create an object from the header row and rangeValues.
  const object = rangeValues.map((row, i) => row.map((col, j) => ({[header[j]]: col})));

  // 3. Put the values to the Spreadsheet.
  const res = object.map((row, i) => ([i + 1, JSON.stringify(row)]));
  messExSheet.getRange(messExSheet.getLastRow() + 1, 1, res.length, res[0].length).setValues(res);
}

参考文献:

• map()
• shift()

这篇关于将第一行存储为对象[名称]，获取对应的核心行/列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！