CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Ruby
Ruby

Reputation: 17

Set values in a column on a sheet from a 1d array

I have a script that gathers Full Names contained within an email group The idea is to have an up to date list of members of a team to use within a drop down.

It is working correctly but I can't seem to get it to setValues() Everything I have tried, either only lists one name from the list, all values in one cell or doesn't work. I think it's to do with the fact it's a 1 dimensional array but I could be wrong.

Here's what I have so far: 

function listGroupMembers() {
  var GROUP_EMAIL = "[email protected]";
  var group = GroupsApp.getGroupByEmail(GROUP_EMAIL);
  var users = group.getUsers();
  var str = "Group " + GROUP_EMAIL + " has " + users.length +  " members: ";
  var valueRange=[];
  valueRange[0]=[];
for (var i = 0; i < users.length; i++) {
    var user = users[i];
    var email = user.getEmail();
    var contact =  ContactsApp.getContact(email);
    if(contact!=null){
    valueRange[i]=[];
      valueRange[i].push (contact.getFullName());
      }
  }
  var sheet = SpreadsheetApp.getActive().getSheetByName("sheet name");
  var numberRows = valueRange.length;
  var range = sheet.getRange(1, 1, numberRows, 1);
  range.setValues(valueRange);
}

I am struggling with out to get these values, into Column1, Row1, on my sheet like follows in seperate cells:

Name 1
Name 2
Name 3
Name 4

The array is as follows: [Name 1, Name 2, Name 3, Name 5, etc]

Please help, hope this all makes sense!

Upvotes: 0

Views: 1933

Answers (1)

ziganotschka
ziganotschka

Reputation: 26796

To output your values into a value range and assign this value range to a row:

Based on the code you provided, perform the following modification:

function listGroupMembers() {
  var GROUP_EMAIL = "[email protected]";
  var group = GroupsApp.getGroupByEmail(GROUP_EMAIL);
  var users = group.getUsers();
  var str = "Group " + GROUP_EMAIL + " has " + users.length +  " members: ";
  var valueRange=[];
  valueRange[0]=[];
  for (var i = 0; i < users.length; i++) {
    var user = users[i];
    var email = user.getEmail();
    var contact =  ContactsApp.getContact(email);
valueRange[i]=[];
    if(contact!=null){
      valueRange[i].push (contact.getFullName());
    }else{
      valueRange[i].push ("");
    }
  }
  var sheet = SpreadsheetApp.getActive().getSheetByName("INSERT HERE THE NAME OF YOUR SHEET");
  var numberColumns = valueRange[0].length;
  var range = sheet.getRange(1, 1, 1, numberColumns);
  range.setValues(valueRange);
}

Explanation:

  • setValues() expect a 2-D value range, that is a nested array with the dimensions [numberRows][numberColumns]. For a row, it is a 2-D array with the height of 1 and the width corresponding to the number of your values. For a column, it would be exactly the opposite.
  • push() is a Javascript method useful for populating the array elements of the value range with your values

To insert values vertically (in one column) instead of horizontally - loop through rows instead of columns. Sample:

for (var i = 0; i < users.length; i++) {
    var user = users[i];
    var email = user.getEmail();
    var contact =  ContactsApp.getContact(email);
valueRange[i]=[];
    if(contact!=null){
      valueRange[i].push (contact.getFullName());
    }else{
      valueRange[i].push ("");
    }
  }
  var sheet = SpreadsheetApp.getActive().getSheetByName("INSERT HERE THE NAME OF YOUR SHEET");
  var numberRows = valueRange.length;
  var range = sheet.getRange(1, 1, numberRows, 1);
  range.setValues(valueRange);

Upvotes: 1

Related Questions