I would like to modify the following script so that all email addresses of a column google drive file called Finance is added to the group email called test@test.de. How should I change the script?

function addToGroup() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1');
  var row = sheet.getLastRow();
  var column = 7;
  var range = sheet.getRange(row, column, sheet.getLastRow())
  var email = range.getValue();

  addGroupMember(email, 'test@test.de')

function addGroupMember(userEmail, groupEmail) {
  var member = {
    email: userEmail,
    role: 'MEMBER'

  AdminDirectory.Members.insert(member, groupEmail);


I am receiving an error

var sheet = ss.getSheetByName('Sheet1'); 

and the error is

TypeError: Cannot call method "getSheets" of null. (line 54, file "Code")

and the line 54 is

var sheet = ss.getSheets()[0];  

Regarding the error, it means that the variable ss is null and that is very likely that occurred because the script isn't bounded to a spreadsheet.

One way to solve this error is to replace the following line

var ss = SpreadsheetApp.getActiveSpreadsheet();

to something like

var ss = SpreadsheetApp.openById(id)

where id is the file ID of the spreadsheet.

Checkout https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet to learn other options.

  • Dear Ruben. greetings if the day. i replaced the whole code with this – maher khader Jan 2 '18 at 3:01
  • @maherkhader: Do mean this? – Rubén Jan 2 '18 at 17:03
  • yes it is the updated on – maher khader Jan 2 '18 at 20:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.