Google Apps脚本：如何将对象数组复制到范围？ [英] Google Apps Script: how to copy array of objects to range?

问题描述

我有一个名为 membership 的对象数组：



  [ {姓名：'Linus Pauling'，地址：'1805 Main Street'，电话：'[phone]'，
电子邮件：'[e-mail]'}，
 
 {姓名：'Maury Povich'，地址：'382 North Street'，电话：'[phone]'，
 email：'[e-mail]'}] 
  

（尽管这里只显示了4个，但每个成员真的有10个键/值对。）



将此数组的各个位复制到Google表格中一系列单元格的最佳方法是什么？我试图找到一种直接调用对象值的方法，并使用.SetValues方法一次性复制它们，而不是一次一个。

要在列A中获得所有成员的名字，我试过了：

$ p $ sheet.getRange（1,1，membership.length ，1）.setValues（会员[{member.name}]）;


...它给出缺少：属性ID后。 $ b

或者：

$ $ $ $ $ $ $ sheet.getRange（1， 1，membership.length，1）.setValues（[成员[名称]]）;


...它给出了ReferenceError：name没有定义。

或者：

  sheet.getRange（1,1，membership.length，1）.setValues （[成员名字]）; 
  

...这会导致无法将数组转换为对象[] []错误。 p>

对于新手问题，我表示歉意。我已经看到

I have an array of objects called membership:

[{name: 'Linus Pauling', address: '1805 Main Street', phone: '[phone]', 
  email: '[e-mail]' },

 {name: 'Maury Povich', address: '382 North Street', phone: '[phone]', 
  email: '[e-mail]'}]

(Although only 4 are shown here, I really have ten key/value pairs per member.)

What is the best way to copy bits of this array to a range of cells in Google Sheets? I am trying to find a method to call the object values directly, and use the .SetValues method to copy them en masse, as opposed to one at a time.

To get all the members' names in column A, I've tried:

sheet.getRange(1,1,membership.length,1).setValues(membership[{member.name}]);

...which gives Missing : after property ID.

Or:

sheet.getRange(1,1,membership.length,1).setValues([membership[name]]);

...which gives ReferenceError: "name" is not defined.

Or:

sheet.getRange(1,1,membership.length,1).setValues([member.name]);

...which gives the "Cannot convert Array to Object[][]" error.

I apologize for the newbie question. I have seen answers about how to copy values from a multidimensional array to a sheet's range, but not an array of objects.

解决方案

Are you looking to produce a table in the form:

name | Address | phone | email
-----+---------+-------+------
.... | ....    | ....  | ....

etc?

If so, then the following snippet may help. The key thing to point out here is that you can't expect a given order when iterating through an object. i.e. Just because your representation of membership lists name first, doesn't mean that if you were to use a for ... in loop you could guarantee that name would come back first - Objects in JavaScript are unordered.

To ensure a given order, the snippet I list defines an array headings in which you specify the order of the columns you want in the Sheet. This is used to guarantee the column order in the output:

var membership = [
  {
    name: 'Linus Pauling', address: '1805 Main Street', phone: '[phone]', 
    email: '[e-mail]'
  },
  {
    name: 'Maury Povich', address: '382 North Street', phone: '[phone]', 
    email: '[e-mail]'
  }
];

// Headings in the column order that you wish the table to appear.
var headings = ['name', 'address', 'phone', 'email'];
var outputRows = [];

// Loop through each member
membership.forEach(function(member) {
  // Add a new row to the output mapping each header to the corresponding member value.
  outputRows.push(headings.map(function(heading) {
    return member[heading] || '';
  }));
});

// Write to sheets
if (outputRows.length) {
  // Add the headings - delete this next line if headings not required
  outputRows.unshift(headings);
  SpreadsheetApp.getActiveSheet().getRange(1, 1, outputRows.length, outputRows[0].length).setValues(outputRows);
}

Output is :

这篇关于Google Apps脚本：如何将对象数组复制到范围？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！