Google Spreadsheet -- get sharing permissions by script

Question

Is it possible to get list of users and permissions from google spreadsheet by javascript?

I have several spreadsheets on shared google drive and I want to check , how are set sharing permissions by script. I am sharing these spreadsheets with more than 20 another users, so it is difficult to check every spreadsheet manually.

Is it possible?

And is it possible to run some script also above whole google drive? For example if I would like to get list of all google spredsheets and folders on drive with sharing permissions list, can I write some javascript above google drive?

Thank you very much

score 1 · Accepted Answer · edited May 23 '17 at 11:57

Disclaimer - This is my first Goolge Apps script, so I'm not 100% sure this is what you want.

I used the DocsList API to list the owner, the editors and the viewers of every spreadsheet in a given Google Drive folder:

function start() {
  var folder = DocsList.getFolderById("...put folder ID here..."),
      files = folder.getFilesByType(DocsList.FileType.SPREADSHEET), file = null, spreadsheet = null,
      owner = null, editors = null, editor = null, viewers = null, viewer = null,
      i = 0, j = 0;

  for (i = 0; i < files.length; i += 1) {
    file = files[i];
    Logger.log("===== Spreadsheet " + (i + 1) + "/" + files.length + ": " + file.getName() + " =====");
    spreadsheet = SpreadsheetApp.openById(file.getId());
    owner = spreadsheet.getOwner();
    Logger.log("- Owner: " + owner.getEmail());
    editors = spreadsheet.getEditors();
    for (j = 0; j < editors.length; j += 1) {
      editor = editors[j];
      Logger.log("- Editor " + (j + 1) + "/" + editors.length + ": " + editor.getEmail());
    }
    viewers = spreadsheet.getViewers();
    for (j = 0; j < viewers.length; j += 1) {
      viewer = viewers[j];
      Logger.log("- Viewer " + (j + 1) + "/" + viewers.length + ": " + viewer.getEmail());
    }
  }
}

Note that the list of viewers contains the commenters too, these two groups are not seprated by this API. I tested this code, and it works for me. You can easily generalize it to iterate over every folder.

Thank you, but If I have in folder files other than Spreadsheet and it throws an error. Is it possible to skip these files?? — Martin Fedy Fedorko, Oct 01 '14 at 13:05
In the folder I tested this code on I have files of other type and I didn't get any errors. What error message do you get? The `getFilesByType(DocsList.FileType.SPREADSHEET)` call should skip non-spreadsheet files. — kol, Oct 01 '14 at 13:24
The error could be caused if there is a file in the folder where Martin does not have access to view the Share permissions. You could use a try catch block inside your for loop and log the file name when it catches an error. Long term, I would suggest using DriveApp instead of DocsList (DocsList is older, but still experimental). — Cyrus Loree, Oct 01 '14 at 20:38
You should replace DocsList with DriveApp or Drive API, because of this sunset announcement: https://developers.google.com/google-apps/documents-list/terms — Andrew Roberts, Oct 01 '14 at 21:32

Google Spreadsheet -- get sharing permissions by script

1 Answers1

Linked