Google Apps Script Execution Time Optimization

Question

I have written the following function that grabs the members of a Google Group, and then iterates through them gathering their first and last name information before writing that as well as their email address into a spreadsheet row. This works well in small groups, but some of the groups that I need to iterate through contain 2,000+ members. When I run the function against these groups, the execution time is exceeded, and the script fails.

I have seen some references to storing data and using time triggers to pick up where the script left off, but I haven't been able to put together a method to do this so far... Still pretty green on the Google Apps Script front.

Could anyone provide any insight as to how I might optimize this, or leverage creative methods to get around the execution time limitations?

function listGroupMembersExt() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var inputsheet = ss.getSheetByName("GroupAddress");
  var outputsheet = ss.getSheetByName("Members");
  var outputactive = outputsheet.getActiveRange();
  outputsheet.clear(outputactive);
  var j = 1
  var grouprange = inputsheet.getRange(1,2);
  var groupemail = grouprange.getValue();
  var group = GroupsApp.getGroupByEmail(groupemail);
  var users = group.getUsers();
  for (var i = 0; i < users.length; i++) { 
    var user = users[i];
    s = user.getEmail();
    var useracct = AdminDirectory.Users.get(s);
    var firstName = useracct.name.givenName;
    var lastName = useracct.name.familyName;
    Logger.log(lastName);
    var cell = outputsheet.getRange(j, 1, 1, 3)
    cell.setValues([[s,firstName,lastName]]);
    j++;
  }
}

Serge insas · Answer 1 · 2014-08-21T19:17:45.460

You can save a lot of time by storing results in an array and write this array in one setValues() call after the loop instead of writing each row separately.

Your code becomes as follows (the calls to group service are not specially fast so it is not 100% sure that it will solve the issue but it should be a lot better)

function listGroupMembersExt() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var inputsheet = ss.getSheetByName("GroupAddress");
  var outputsheet = ss.getSheetByName("Members");
  var outputactive = outputsheet.getActiveRange();
  outputsheet.clear(outputactive);
  var grouprange = inputsheet.getRange(1,2);
  var groupemail = grouprange.getValue();
  var group = GroupsApp.getGroupByEmail(groupemail);
  var users = group.getUsers();
  var output = [];
  for (var i = 0; i < users.length; i++) { 
    var user = users[i];
    s = user.getEmail();
    var useracct = AdminDirectory.Users.get(s);
    var firstName = useracct.name.givenName;
    var lastName = useracct.name.familyName;
    Logger.log(lastName);
    output.push([s,firstName,lastName]);
  }
  outputsheet.getRange(1,1,output.length,output[0].length).setValues(output);
}

Google Apps Script Execution Time Optimization

1 Answers1