1D Array to fill a spreadsheet column

问题:

I'm using the built-in group service to pull a list of usernames...

function getUsersInGroup(group) {
  var usersInGroup = GroupsApp.getGroupByEmail(group).getUsers();
  return usersInGroup;
}

...and it returns an array, like this...

[ [e-mail], [e-mail], [e-mail] ]

What I would like is for those emails to show up in a spreadsheet column like this...

It seems that Apps Script's .setValues() wants a 2D array, even when the data is "flat", so I get the impression from my tests that my data has to be in a format like this...

var data = [[[e-mail]],[[e-mail]],[[e-mail]]]

If I want it to show up as rows of data instead of one item in a column.

If I'm correct, I think I'm looking for the best of two (maybe three) possible answers to my question.

What I think are Possible solutions

1. Transform the array and make each item in the array a one item array with probably a for loop

2. Find out there is a better built-in function that will take the array and turn it into a row of data

3. There's something else I could do that I didn't even consider here

回答1:

You can also use the sheets API, If you don't want to loop through.

Use Advanced Google services and switching majorDimension as columns:

  var data = [ '[e-mail]', '[e-mail]', '[e-mail]' ];
  var request = {
    range: 'Sheet1!A1',
    majorDimension: 'COLUMNS',
    values: [data], //still 2D
  };
  //API must be enabled before use. 
  //https://developers.google.com/apps-script/advanced/sheets
  Sheets.Spreadsheets.Values.update(request, ss.getId(), request.range, {
    valueInputOption: 'USER_ENTERED',
  });

Or loop through:

data = data.map(function(e){return [e];});

回答2:

As @TheMaster pointed out, a simple loop with the map function worked well. Here's what I did:

function writeArrayToColumn(array) {
  var mainSheet = SpreadsheetApp.getActiveSheet(); 

  var newArray = array.map(function (el) {
    return [el];
  });

  var range = mainSheet.getRange(2, 1, newArray.length, 1);

  range.setValues(newArray);
}