Count files in MyDrive where I am the owner per GScript-CodePudding

I try to create a script which is counting my files.

Currently I have written this Script:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("GDrive");
  sheet.clear();

  for (var i = 5; i <= 13; i  ) {
    //var i = 5;
    if (i == 5) {var sFT = "presentation";}
    if (i == 6) {var sFT = "form";}
    if (i == 7) {var sFT = "audio";}
    if (i == 8) {var sFT = "video";}
    if (i == 9) {var sFT = "archive";}
    if (i == 10) {var sFT = "drawing";}
    if (i == 11) {var sFT = "shortcut";}
    if (i == 12) {var sFT = "folder";}
    if (i == 13) {var sFT = "site";}

    if (i == 5) {var sTXT = "GPräsentation";}
    if (i == 6) {var sTXT = "GForm";}
    if (i == 7) {var sTXT = "Audio";}
    if (i == 8) {var sTXT = "Video";}
    if (i == 9) {var sTXT = "Archive (ZIP)";}
    if (i == 10) {var sTXT = "Zeichnungen";}
    if (i == 11) {var sTXT = "Links";}
    if (i == 12) {var sTXT = "Verzeichnisse";}
    if (i == 13) {var sTXT = "GSite";}

    if (i == 5) {
    var files = DriveApp.searchFiles('"me" in owners and (mimeType contains "'   MimeType.MICROSOFT_POWERPOINT_LEGACY   '" or mimeType contains "'   MimeType.MICROSOFT_POWERPOINT   '" or mimeType contains "'   MimeType.GOOGLE_SLIDES   '")');
    }

    if (i == 6) {
    var files = DriveApp.searchFiles('"me" in owners and (mimeType contains "'   MimeType.GOOGLE_FORMS    '")');
    }

    if (i == 7) {
    var files = DriveApp.searchFiles('"me" in owners and (mimeType contains "'   MimeType.Audio    '")');
    }

    if (i == 8) {
    var files = DriveApp.searchFiles('"me" in owners and (mimeType contains "'   MimeType.video    '")');
    }

    if (i == 9) {
    var files = DriveApp.searchFiles('"me" in owners and (mimeType contains "'   MimeType.ZIP    '")');
    }

    if (i == 10) {
    var files = DriveApp.searchFiles('"me" in owners and (mimeType contains "'   MimeType.drawing    '")');
    }

    if (i == 11) {
    var files = DriveApp.searchFiles('"me" in owners and (mimeType contains "'   MimeType.Links    '")');
    }

    if (i == 12) {
    var files = DriveApp.searchFolders('"me" in owners');
    }

    if (i == 13) {
    var files = DriveApp.searchFiles('"me" in owners and (mimeType contains "'   MimeType.GSite    '")');
    }

    let count = 0;

    sheet.getRange(i,2).setValue(sTXT);
    SpreadsheetApp.flush();
   
    while (files.hasNext()) {
      var file = files.next();
      count = count  1;
    }

    sheet.getRange(i,1).setValue(i);
    sheet.getRange(i,2).setValue(sTXT);
    sheet.getRange(i,3).setValue(count);
    SpreadsheetApp.flush();
  }
}

Link to my example-file: https://docs.google.com/spreadsheets/d/1cR3EyK1WgF8MLniSu_O4QFUzvPQ-x3Gq9G6Y40cZr3s/edit?usp=sharing

Does anyone now why my script is not found any files for: Audio, Video, Archive (ZIP), Zeichnungen, Links, GSite but I have some files from this categories. When I search about the browser I can find all the files.

Does anyone can help?

Greetingss

CodePudding user response：

Try Some Recursion

function countallfiles() {
  let folder = DriveApp.getFolderById('1obWyAZl0vDlwbNqcGBq7Nt38qzusWt1d');
  getAllFilesAndFolders(folder);
  Logger.log(n);
}

var n = 0;
function getAllFilesAndFolders(folder) {
  let files = folder.getFiles();
  while (files.hasNext()) {
    let file = files.next();
    if (file.getOwner().getName() == 'Your Name') {
      n  ;
    }
  }
  let subfolders = folder.getFolders();
  while (subfolders.hasNext()) {
    getAllFilesAndFolders(subfolders.next());
  }
}

CodePudding user response：

I believe your goal is as follows.

You want to retrieve the number of files every mimeType you set.
You want to achieve this using Google Apps Script.

Issue and solution:

About your question of Does anyone now why my script is not found any files for: Audio, Video, Archive (ZIP), Zeichnungen, Links, GSite, when you tested the following script,

var yourMimeTypes = [MimeType.MICROSOFT_POWERPOINT_LEGACY, MimeType.MICROSOFT_POWERPOINT, MimeType.GOOGLE_SLIDES, MimeType.GOOGLE_FORMS, MimeType.Audio, MimeType.video, MimeType.ZIP, MimeType.drawing, MimeType.Links, MimeType.FOLDER, MimeType.GSite];
console.log(yourMimeTypes)

the following result is obtained.

[
  'application/vnd.ms-powerpoint',
  'application/vnd.openxmlformats-officedocument.presentationml.presentation',
  'application/vnd.google-apps.presentation',
  'application/vnd.google-apps.form',
  undefined,
  undefined,
  'application/zip',
  undefined,
  undefined,
  'application/vnd.google-apps.folder',
  undefined
]

Unfortunately, in the current stage, there are no values of MimeType.Audio, MimeType.video, MimeType.drawing, MimeType.Links, MimeType.GSite in the enum of mimeTYpes. Ref I thought that this is the reason of your issue of why my script is not found any files for: Audio, Video, Archive (ZIP), Zeichnungen, Links, GSite.

From your script, for example, I guessed that you might want to use the mimeTypes as follows.

About MimeType.Audio, I thought that you might want to retrieve the mimeTypes including audio.
About MimeType.video, I thought that you might want to retrieve the mimeTypes including video.
About MimeType.ZIP, in this case, I thought that the mimeType is different from application/zip. It's like application/x-zip-compressed. So, in this case, the mimeTypes including zip are retrieved.
About MimeType.drawing, I thought that you might want to retrieve the mimeType of MimeType.GOOGLE_DRAWINGS.
About MimeType.Links, I thought that you might want to retrieve the mimeType of MimeType.SHORTCUT.
About MimeType.GSite, I thought that you might want to retrieve the mimeType of MimeType.GOOGLE_SITES.

When these are reflected in a script, it becomes as follows.

Sample script:

This script used Drive API. Because the file list is retrieved using Drive API. So, please enable Drive API at Advanced Google services. And, please check the display names and mimeTypes of obj. Please modify this for your actual situation.

function sample() {
  // Please set the display names and mimeTypes.
  // I created this object by guessing from your script.
  const obj = {
    "GPräsentation": [MimeType.MICROSOFT_POWERPOINT_LEGACY, MimeType.MICROSOFT_POWERPOINT, MimeType.GOOGLE_SLIDES],
    "GForm": [MimeType.GOOGLE_FORMS],
    "Audio": "audio",
    "Video": "video",
    "Archive (ZIP)": "zip",
    "Zeichnungen": [MimeType.GOOGLE_DRAWINGS],
    "Links": [MimeType.SHORTCUT],
    "Verzeichnisse": [MimeType.FOLDER],
    "GSite": [MimeType.GOOGLE_SITES],
  };

  // 1. Retrieve a list of all files and folders from your Google Drive by the search query of `"me" in owners`.
  let list = [];
  let pageToken = "";
  do {
    const res = Drive.Files.list({
      q: `"me" in owners`, // I couldn't understand whether you want to use `trashed=false`. If you want to use, please add it.
      fields: "nextPageToken,items(mimeType)",
      pageToken: pageToken,
      maxResults: 1000,
      // includeItemsFromAllDrives: true, // If you want to search the files from the shared Drive, please use this.
      // supportsAllDrives: true, // If you want to search the files from the shared Drive, please use this.
    });
    if (res.items.length > 0) list = [...list, ...res.items.map(({ mimeType }) => mimeType)];
    pageToken = res.nextPageToken;
  } while (pageToken);
  const mimeTypesObj = list.reduce((o, e) => (o[e] = o[e] ? o[e]   1 : 1, o), {});
  const mimeTypesAr = Object.entries(mimeTypesObj);
  
  // 2. Create an array for putting to the sheet.
  const values = Object.entries(obj).map(([k, v]) => {
    if (Array.isArray(v)) {
      return [k, v.reduce((c, e) => c  = mimeTypesObj[e] || 0, 0)];
    }
    return [k, mimeTypesAr.reduce((c, [kk, vv]) => c  = kk.includes(v.toLocaleLowerCase()) ? vv : 0, 0)];
  });

  // 3. Put the values on the sheet.
  // In this case, the values are put from "B5" from your script.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("GDrive");
  sheet.clear();
  sheet.getRange(5, 2, values.length, values[0].length).setValues(values);
}

Flow:

The flow of this script is as follows.

Retrieve a list of all files and folders from your Google Drive by the search query of "me" in owners.
- I couldn't understand whether you want to use trashed=false. If you want to use it, please add it.
Create an array for putting to the sheet.
Put the values on the sheet.

Issue and solution:

Sample script:

Flow:

References: