Google Drive: get the list of users with whom the file is shared

Question

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.

Answer 1

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.

ID	Owner	Editors	Viewers
1kO__q9CTKFeXJZYcp9oHJ0at0_Z
13ROPW5dJwnccidEQ4kYY8k4Wnif
...etc

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:

ID	Owner	Editors	Viewers
1kO__q9CTKFeXJZYcp9oHJ0at0_Z	[email protected]	[email protected]	[email protected]
1PwzEEU9CncV8N0Xh1oe5JYk_t-X	[email protected]		[email protected]
13ROPW5dJwnccidEQ4kYY8k4Wnif	[email protected]	[email protected]
1nw5eU4-EpBWAmqy-cv2UR0OWPcS	[email protected]
1nQfGvAhWeXVQkI1WkCUFMRHCuVl	[email protected]		[email protected] [email protected] [email protected]
1jKCdKeasQR_RSNLkTZj9P1V_qTa	[email protected]	[email protected] [email protected]	[email protected] [email protected] [email protected]
10404WvWgX_lanb5OMtjLYm6N9Ia	[email protected]	[email protected] [email protected]

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

var file = DriveApp.getFileById(id);

with

var file = DriveApp.getFileByUrl(id);.