Google Apps Script: how to copy array of objects to range?

Question

I have an array of objects called membership:

[{name: 'Linus Pauling', address: '1805 Main Street', phone: '(615) 555-1010', 
  email: 'linus.pauling@gmail.com' },

 {name: 'Maury Povich', address: '382 North Street', phone: '(423) 555-1997', 
  email: 'maury@themauryshow.org'}]

(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.

Maybe this will help to insert a row of data from JSON objects: https://gist.github.com/mhawksey/1442370 — Sujay Phadke, Dec 09 '16 at 05:08

Bardy · Accepted Answer · 2016-12-09T10:04:02.463

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: '(615) 555-1010', 
    email: 'linus.pauling@gmail.com'
  },
  {
    name: 'Maury Povich', address: '382 North Street', phone: '(423) 555-1997', 
    email: 'maury@themauryshow.org'
  }
];

// 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 :

Thank you. I was completely unaware of the `map` method, which is precisely what was needed here. — Suzanne, Dec 09 '16 at 16:42

score 1 · Answer 2 · answered Dec 09 '16 at 05:37

How about following script? This script is a container bound script of spreadsheet. There are 2 patterns. The arrangement of data is different for between pattern 1 and 2.

Pattern 1 is

Pattern 2 is

function main() {
  var data = [{name: 'Linus Pauling', address: '1805 Main Street', phone: '(615) 555-1010', 
  email: 'linus.pauling@gmail.com' }, {name: 'Maury Povich', address: '382 North Street', phone: '(423) 555-1997', 
    email: 'maury@themauryshow.org'}];
  var sheet = SpreadsheetApp.getActiveSheet();

  var result = pattern1(data);
  var result = pattern2(data);

  sheet.getRange('a1').offset(0, 0, result.length, result[0].length).setValues(result);
}

function pattern1(data){
  var ar = [];
  for (var i in data){
    for (var key in data[i]){
      ar.push([key, data[i][key]]);
    }
  }
  return ar;
}

function pattern2(data){
  var ar = [];
  var keys = [];
  var values = [];
  for (var i in data){
    for (var key in data[i]){
      if (i == 0) keys.push(key);
      values.push(data[i][key]);
    }
    if (i == 0){
      ar.push(keys);
      keys = [];
    }
    ar.push(values);
    values = [];
  }
  return ar;
}

If my understanding for your questions was wrong, I apologize.

score 0 · Answer 3 · answered Dec 09 '16 at 17:41

See the script at the bottom of the Simple Mail Merge Tutorial for some useful code to retrieve data. Copy all the code from this comment down:

//////////////////////////////////////////////////////////////////////////////////////////
//
// The code below is reused from the 'Reading Spreadsheet data using JavaScript Objects'
// tutorial.
//
/////////

/////////////////////////////////////////////////////////////////////////////////

// getRowsData iterates row by row in the input range and returns an array of objects.

Once you have, the getRowsData returns not only the data, but a second item which is a list of the Headers. Using this, you can modfy the code from @Bardy to allow for the Headers in any order, and also other possible columns in between.

Google Apps Script: how to copy array of objects to range?

3 Answers3

Linked