Get Google Drive Shared Files' Info with GAS

0xkoji - Jan 18 '21 - - Dev Community

I have used Google Drive for a long time because the school I graduated from allows me to keep using it lol.

Google Drive is easy to use and to share a file with people, however, there isn't an easy way to check whom I shared files.
Actually, there are some services to do that like below.
I used to use google-drive-permission-search because at that time I used Ruby as the main language. But, I haven't used it for a while 😂 and recently I use js/ts and python mainly. Also, my goal is to pass Google Drive's permission check functionality to people who are non-techies. So I decided to use GAS(Google Apps Script) since it won't need to install any software on their machine and GAS doesn't need to set
Google API configs.

GAS(Google Apps Script)
https://developers.google.com/apps-script

Services that allow people to check permissions easily

[What I built]

Alt Text

  1. A user put a link to a folder on Google Drive
  2. Click the Click me button
  3. Check all folders and files under the folder that he/she put in the P2 cell.
  4. List folders and files with the name, path, how many people have access to a file, the names of editors, and the names of viewers.

Steps to create a spreadsheet

Step 1 Create a new spreadsheet

Go to your Google Drive and click New > Google Sheet

Step 2 Insert a shape

In this case, I put a rectangle, but if you don't like it, you can put anything you want

Step 3 Connect a script to the button

In this step, we need to connect the button we put to a script we will write.
Tools > Script editor

Step 4 Edit GAScript

In the code your can see while (folders.hasNext()) {} because the return of folder.getFolders() isn't array so we need to while.

In terms of editors and viewers will get null if an editor or viewer doesn't use Google App which means he/she doesn't use a Google account or doesn't belong to your Google App's org. However, .getEditors() and .getViewers() count null as one account.

function getId() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  const folderURL = sheet.getRange('P2').getValue();
  const pathArr= folderURL.split('/'); 
  const folderId = pathArr[pathArr.length-1];
  return folderId;
}

function checkSharedInfo(prefix = null, id) {
  const targetId = id ===undefined ? getId() : id;
  // specify the target sheet
  const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  // get folders
  const folder = DriveApp.getFolderById(targetId);
  // get files
  const files = folder.getFiles();
  while (files.hasNext()) {
    writeRow(sheet, prefix, files.next());
  }
  const folders = folder.getFolders();
  while (folders.hasNext()) {
    const sub = folders.next();
    writeRow(sheet, prefix, sub);
    permissionCheker(`${prefix || ""}/${folder.getName()}/${sub.getName()}`, sub.getId());
  }
}

function writeRow(sheet, prefix, obj) {
  let rowIndex = sheet.getLastRow() + 1;
  sheet.getRange(rowIndex, 1).setValue(prefix ? `${prefix}/${obj.getName()}` : '');
  sheet.getRange(rowIndex, 2).setValue(obj.getName());
  const editors = obj.getEditors();
  const viewers = obj.getViewers();

  if (editors.length ===0 && viewers.length ===0) return;

  sheet.getRange(rowIndex, 3).setValue(editors.length+viewers.length);
  sheet.getRange(rowIndex, 4).setValue(editors.map((a) => {
    return a.getName() != null ? a.getName() : 'someone';
  }).join(", "));
  sheet.getRange(rowIndex, 5).setValue(viewers.map(a => a.getName()).join(", "));  
}
Enter fullscreen mode Exit fullscreen mode

We are almost there!!!

Step 5 Set a cell for a Google Drive folder

In this case, I'm using P2 to put a Google Drive folder URL, so I put targetFolderURL in O2.
If you don't like to put it there, you can change it. For doing that, you need to modify the code. What you need to change is the line2 in getId() function.

Step 6 Layout the sheet

This step is completely optional since this won't affect the GAS's action.
In my case, I add labels to the sheet.
Alt Text

I put name, path, the number of editors+viewers, editors, and viewers.

Step 7 Put the Folder link and Run the GAS

Copy the Google Drive folder's link that you want to check the permissions and paste it into P2 cell and Click me button.

Alt Text

Hope this will be useful!!!

Exceeded maximum execution time

Seems that the max execution time for Google scripts is 6 mins.

Solution

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .