I have a Group with 6000 members. I need to extract their email addresses to an Excel file in my Google Drive called, for Example, "ABC". The email address of the Group is abc@dgate.com.

I tried the code below but it is not working. Also I cannot find how to get the document ID. For example, I have Google Sheet called "MyFile", I click file and then "Publish to web" then I found this link: https://docs.google.com/spreadsheets/d/e/2PACX-1vR2V4CUjhfxUQYRZH4DZEJxJma6KMYzu395ShUzD5uqNaX4-pFKe9yMEYVocNOjspJPmUPX_u_kjLNr/pubhtml. Which part is the ID?

The code is:

function writeToSpreadsheet(){
   var values = [];
   var GROUP_EMAIL = "abc@dgate.com";
   var group = GroupsApp.getGroupByEmail(GROUP_EMAIL);
   var users = group.getUsers();
   var s = "Group " + GROUP_EMAIL + " has " + users.length + " members: ";
  for (var i=0; i<users.length; i++){
    var user = users[i];
  var spreadsheetUrl = 'https://docs.google.com/a/dgate.com/spreadsheets/d/e/2PACX-1vSoOVdRPk4V23-VHR0-g9ZC-tG6-cFXrioimIIp4jsDrQlUN2iAVuTGA3q-rQQrLl5w79QWvO-LzhEX';
  SpreadsheetApp.openByUrl(spreadsheetUrl).getSheets()[0].getRange(1, 1, values.length, values[0].length).setValues(values);

When I run the above code it gives me this error:

Bad value (line 49, file "Code")

Line 49 is

SpreadsheetApp.openByUrl(spreadsheetUrl).getSheets()[0].getR‌​ange(1, 1, values.length, values[0].length).setValues(values);

  • Which line is line 49? – Rubén Dec 27 '17 at 5:05
  • Dear Rubin thanks for your response. line 49 is SpreadsheetApp.openByUrl(spreadsheetUrl).getSheets()[0].getRange(1, 1, values.length, values[0].length).setValues(values); – maher khader Dec 27 '17 at 7:26
  • Here's a clue how to find the document-id: productforums.google.com/forum/#!topic/docs/3STOEukh1pU – ale Dec 27 '17 at 14:04
  • I'm fairly certain that Google can't manipulate Excel files. You need to use a Google Sheet. – ale Dec 27 '17 at 14:05

On the following line:

var spreadsheetUrl = 'https://docs.google.com/a/dgate.com/spreadsheets/d/e/2PACX-1vSoOVdRPk4V23-VHR0-g9ZC-tG6-cFXrioimIIp4jsDrQlUN2iAVuTGA3q-rQQrLl5w79QWvO-LzhEX';

The problem is that https://docs.google.com/a/dgate.com/spreadsheets/d/e/2PACX-1vSoOVdRPk4V23-VHR0-g9ZC-tG6-cFXrioimIIp4jsDrQlUN2iAVuTGA3q-rQQrLl5w79QWvO-LzhEX isn't a valid spreadsheet URL. You should replace it by a valid one.

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.