遍历Google Apps脚本中的对象并打印到Google表格 [英] Iterating over an object in a Google Apps script and printing to Google Sheets

问题描述

通过此循环，我无法在我的Google工作表上打印多行.

I'm having trouble printing more than one row to my google sheet with this loop.

第一行追加很好，但是我希望函数追加数据变量中的所有对象.

The first row appends fine, but I want the function to append all objects from the data var.

当我使用Logger进行验证时，数据对象已从Firebase正确提取.

The data object is properly pulling from Firebase when I verify with a Logger.

var firebaseUrl = "https://test.firebaseio.com/alerts"; 
var secret = "sssssssssssssssssssss"; 
var base = FirebaseApp.getDatabaseByUrl(firebaseUrl, secret); 
var data = base.getData();

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("feed");
var selection = sheet.getActiveRange();
var range = sheet.getActiveRange();
var values = range.getValues();
var columns = selection.getNumColumns();
var rows = selection.getNumRows();
var num = 2;

function writeToSheets() {  
  for(var i in data) {
    var values = [
      [ data[i].id, data[i].two, data[i].three, data[i].four ]
    ];
    var keys = Object.keys(values[0]);
    var sheetRow = [];
    var entryKeys;
    for (j in keys) {
      sheetRow = [];
      entryKeys = Object.keys(values[keys[j]])
      for (k in entryKeys) {
        sheetRow.push(values[keys[j]][entryKeys[k]]);
      }
      sheet.appendRow(sheetRow);                            
    }
  }
}

推荐答案

我刚刚尝试了这段代码(假设我猜对了数据结构):

I've just tried this code (assuming that I guessed the data structure correctly):

function myFunction() {
  var data = [ 
    {'id': 1, 'two': 'test2', 'three': 'test3', 'four': 'test4'},
    {'id': 2, 'two': 'test2-2', 'three': 'test3-2', 'four': 'test4-2'}
  ]

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("feed");
  var selection = sheet.getActiveRange();
  var range = sheet.getActiveRange();
  var values = range.getValues();
  var columns = selection.getNumColumns();
  var rows = selection.getNumRows();
  var num = 2;

  function writeToSheets() {  
    for(var i in data) {
      var values = [
        [ data[i].id, data[i].two, data[i].three, data[i].four ]
      ];
      var keys = Object.keys(values[0]);
      var sheetRow = [];
      var entryKeys;
      for (j in keys) {
        sheetRow = [];
        entryKeys = Object.keys(values[keys[j]])
        for (k in entryKeys) {
          sheetRow.push(values[keys[j]][entryKeys[k]]);
        }
        sheet.appendRow(sheetRow);                            
      }
    }
  }

  writeToSheets();
}

当我运行它时，它在打印第一行后出现错误TypeError: Expected argument of type object, but instead had type undefined. (line 26, file "Code")，但失败. 而且很容易看到如果在调试模式下运行会发生什么情况:

When I run it, it fails after printing the first line with an error TypeError: Expected argument of type object, but instead had type undefined. (line 26, file "Code"). And it is easy to see what exactly happens if you run it in debug mode:

• 您有一个元素包含values数组(第18行)
• var keys = Object.keys(values[0]);变为[0,1,2,3](在values数组的第一个元素内有4个值)
• 然后，让j从0到3，我们得到entryKeys = Object.keys(values[keys[j])
  • 当j = 0时，values [keys [j]] = values [0]-我们从values
  中获得第一个元素
  • 当j = 1时，values [keys [j]] = values [1]-我们失败了，因为在values中只有1个元素
  • You have values array with one element (line 18)
  • The var keys = Object.keys(values[0]); becomes [0,1,2,3] (we have 4 values inside the first element of values array)
  • Then, having j from 0 to 3 we get entryKeys = Object.keys(values[keys[j])
    • When j = 0, values[keys[j]] = values[0] - we get the first element from values
    • When j = 1, values[keys[j]] = values[1] - here we fail, because there is only 1 element in values

    我不太确定您要使用所有这些键在这里做什么，但是如果您只想打印数据，则可以更简单地完成操作:

    I am not really sure what you are trying to do here with all these keys, but if you just want to print the data, it can be done simpler:

      function writeToSheets() {  
        for(var i in data) {
          var item = data[i];
          sheetRow = [];
          for (key in item) {
            sheetRow.push(item[key]);
          }
          sheet.appendRow(sheetRow);                            
        }
      }
    

    这篇关于遍历Google Apps脚本中的对象并打印到Google表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！