Google Spreadsheet script that lists all the shared documents inside a specified folder and all its subfolders recursively. Skips out the documents shared internally (with specified email addresses)

listGoogleDriveSharedDocuments.js

function start() {
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow(["Name", "Sharing Access", "Sharing Permission", "Get Editors", "Get Viewers", "Date", "Size", "URL", "Download", "Description", "Type"]);

  const folder = DriveApp.getFolderById("folder_id_copied_from_the_url_without_the_google_drive_domain");
 
  recursiveListSharedFiles(folder, sheet);
}

function recursiveListSharedFiles(folder, sheet) {
  listSharedFiles(folder, sheet);

  const subfolders = folder.getFolders();

  while (subfolders.hasNext()) {
    recursiveListSharedFiles(subfolders.next(), sheet);
  }
}

function listSharedFiles(folder, sheet) {
  const files = folder.getFiles();

  while (files.hasNext()) {
    const file = files.next();
    const externalEditorEmails = filterInternalEditors(file);

    if (isOnlySharedInternally(externalEditorEmails, file)) {
      continue;
    }

    appendRow(externalEditorEmails, file, sheet)
  }
}

function filterInternalEditors(file) {
  const internalEmails = [
    "[email protected]",
    "[email protected]"
  ];

  const editorEmails = file.getEditors().map(e => e.getEmail());

  return editorEmails.filter(editorEmail => !internalEmails.includes(editorEmail));
}

function isOnlySharedInternally(externalEditorEmails, file) {
  return externalEditorEmails.length === 0
    && file.getViewers().length === 0
    && file.getSharingAccess() === DriveApp.Access.PRIVATE;
}

function appendRow(editorEmails, file, sheet) {
  const viewerEmails = file.getViewers().map(v => v.getEmail());

  data = [
    file.getName(),
    file.getSharingAccess(),
    file.getSharingPermission(),
    editorEmails.toString(),
    viewerEmails.toString(),
    file.getDateCreated(),
    file.getSize(),
    file.getUrl(),
    "https://docs.google.com/uc?export=download&confirm=no_antivirus&id=" + file.getId(),
    file.getDescription(),
    file.getMimeType(),
  ];

  sheet.appendRow(data);
}

This is the correct recursive approach imo.

You could also call const folder = DriveApp.getRootFolder(); to scan the entire drive at line 5.

One can also add file.setSharing(DriveApp.Access.PRIVATE, DriveApp.Permission.EDIT); at line 69 to just close these loose files immediately after documenting. But, it should be checked whether you own the file or they are not just shared with you by others. It is possible to move shared links (files or folders) under your Google drive.

// iterative approach

function recursiveListSharedFiles(rootFolder, sheet) {
  const stack = [];
  stack.push(rootFolder);
  while(stack.length !== 0){
    const folder = stack.shift();
    listSharedFiles(folder, sheet);
    const subfolders = folder.getFolders();

    while (subfolders.hasNext()) {
      stack.push(subfolders.next());
    }
  }
}

Thanks. I'd add in line 43 another line to allow filtering by domain:

function filterInternalEditors(file) {
  const internalEmails = [
    "@gmail.com",
    "[email protected]"
  ];

  const editorEmails = file.getEditors().map(e => e.getEmail());

  return editorEmails.filter(editorEmail =>
      !internalEmails.includes(editorEmail) &&
      !internalEmails.includes(editorEmail.slice(editorEmail.indexOf('@')))); //Line added
}