I have some shared files (Google doc, Google Sheet) with Google Drive, and people can ask for permission to access them.

For example, here is a exemple of google sheet file : https://docs.google.com/spreadsheets/d/13Wy-kLO22Y6stIzV0CjkAFVx0-EOy5hv26n8uo40xck/edit?usp=sharing

It is shared with several users, and you can ask for permission to access it.

Then I can see the list of all the users and their emails, and I can do copy paste.

Now, my question is: how can I get all the emails in a Excel file (or Google sheet, of course) ?

I think there can be a google app script/google sheet macro, to automate the process ? The idea then is to get the users names and email adresses.

CodePudding user response：

It's still not clear how you mean to get the files and how the result should look like. Suppose you have the table where column A contains the list of IDs of the files.

This script will obtain email(s) of an owner, editors and viewers for every file with given ID and put these emails in column B, C and D respectively:

function get_editors_viewers() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const ids = sheet.getRange('a2:a').getValues().flat().filter(String);
  const table = [];
  
  for (let id of ids) {
    try {
      var file    = DriveApp.getFileById(id);
      var owner   = file.getOwner().getEmail();
      var editors = file.getEditors().map(e => e.getEmail()).join('\n');
      var viewers = file.getViewers().map(v => v.getEmail()).join('\n');
      table.push([id, owner, editors, viewers]);
    } catch(e) {
      table.push([id, '', '', '']);
    }
  }

  sheet.getRange(2,1,table.length,table[0].length).setValues(table);
}

The result should be something like this:

If you have URLs instead of IDs all you need is to change

var file = DriveApp.getFileById(id);

with

var file = DriveApp.getFileByUrl(id);.