Get Google Drive Shared Files' Info with GAS
Tags: gas, javascript, googledrive, productivity
Published: 2021-01-18 14:54:55

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
- https://gsuite.google.com/marketplace/app/drive_permissions_auditor/370134017754

- https://www.whohasaccess.com/

- https://github.com/morimorihoge/google-drive-permission-search

- https://drivechecker.taf-jp.com/ (Japanese)


[What I built]

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/9s3vlu9j245tftsoawoe.png)

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.

```js
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(", "));  
}
```

## 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](https://dev-to-uploads.s3.amazonaws.com/i/xniy90ajnjbl25k7ocd8.png)

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](https://dev-to-uploads.s3.amazonaws.com/i/vgwn6trz6ehzs6bn8itw.png)


Hope this will be useful!!!

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

Solution
- https://stackoverflow.com/questions/41971806/how-can-i-increase-the-6-minute-execution-limit-in-google-apps-script

- https://stackoverflow.com/questions/38665061/google-script-exceeded-maximum-execution-time