Skip to content

Instantly share code, notes, and snippets.

@mhawksey

mhawksey/Method1.gs

Created February 17, 2023 09:07
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mhawksey/c93f504a4f57c17bdfab4923fb88ccb9 to your computer and use it in GitHub Desktop.
Save mhawksey/c93f504a4f57c17bdfab4923fb88ccb9 to your computer and use it in GitHub Desktop.
Download ZIP
Google Apps Script methods for generating Google My Drive report.
Raw
Method1.gs
// @OnlyCurrentDoc
const FOLDER_ID = 'YOUR_FOLDER_ID'; // OR 'root' if you want your entire My Drive
/**
* Method One: Using built-in DriveApp / file iterator
*/
function generateFolderFilesDriveApp() {
try {
const folderId = FOLDER_ID;
const parentFolder = (folderId === 'root') ? DriveApp.getRootFolder() : DriveApp.getFolderById(folderId);
const path = `/${parentFolder.getName()}`;
const heads = [['Path', 'Name', 'ID', 'Link', 'Created Date', 'Modified Data', 'Mime Type', 'Size']];
const res = [];
// get parent folder files
getFolderFiles_(path, parentFolder, res)
// get child folders
getChildFolders_(parentFolder, path, res);
// writing the results to the report
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0]
sheet.getRange(1, 1, res.length + 1, res[0].length).setValues([...heads, ...res]);
sheet.getRange(2, 1, res.length, res[0].length).sort([1, 2]);
} catch (e) {
Logger.log(e.toString());
}
}
/**
* Get child folders/files and output final result to Google Sheet
* @param {Folder} parent folder to get subfolders/files
* @param {String} path to current folder
* @param {Array} res of results
*/
function getChildFolders_(parent, path, res) {
const childFolders = parent.getFolders();
// iterating across the child folders in this folder
while (childFolders.hasNext()) {
let childFolder = childFolders.next();
let currentPath = path + '/' + childFolder.getName();
// combine file detail array
getFolderFiles_(currentPath, childFolder, res)
// Recursive call for any sub-folders
getChildFolders_(childFolder, currentPath, res);
}
}
/**
* Get folder files
* @param {String} path - path of the folder
* @param {Folder} folder to get files from
* @return {Array} of results
*/
function getFolderFiles_(path, folder, res) {
// similar to getting the child folders getting the files in each folder
let files = folder.getFiles();
while (files.hasNext()) {
// Print list of files inside the folder
let file = files.next();
// pushing some file info for the Google Sheet
res.push([path, file.getName(), file.getId(), file.getUrl(), file.getDateCreated(), file.getLastUpdated(), file.getMimeType(), file.getSize()])
}
}
Raw
Method2.gs
// @OnlyCurrentDoc
const FOLDER_ID = 'YOUR_FOLDER_ID'; // OR 'root' if you want your entire My Drive
/**
* Method Two: Calling Drive API v3
* By @mhawksey based on https://stackoverflow.com/a/41741521
*/
function generateFolderFilesDriveV3() {
const folderId = (FOLDER_ID === 'root') ? DriveApp.getRootFolder().getId() : FOLDER_ID;
// Based on https://stackoverflow.com/a/41741521
// Logic:
// #1 get all folders in My Drive (used to filter and build folder paths)
// #2 optionally filter folders for selected folder (if specified in FOLDER_ID)
// #3 get files for filtered folders (or all folders if we are getting the root)
const folderPathDict = []; // we'll use this as a folder path dictionary
// #1 get all folders (build the query and fetch)
const foldersQuery = "trashed = false AND 'me' in owners AND mimeType = 'application/vnd.google-apps.folder'";
const folderList = driveCall_(foldersQuery);
// #2 build the folder path dictionary and filter folders if the entire drive isn't required
// Based on https://gist.github.com/tanaikech/97b336f04c739ae0181a606eab3dff42
const folderIds = function c(folder, res) {
folderPathDict[folder] = getFolderPath_(folder, folderList);
res.push(folder);
// return parent of folder
const ar = folderList.filter(e => { if (e.parents) return e.parents[0] == folder });
ar.forEach(e => c(e.id, res));
return res;
}(folderId, []);
// #3 get all files or get filtered folders files
let filesList = [];
if (FOLDER_ID === 'root') {
console.log('Getting all files');
const filesQuery = "trashed = false AND 'me' in owners AND mimeType != 'application/vnd.google-apps.folder'";
filesList = driveCall_(filesQuery);
} else {
// chunk the folders into batches of 20 (this keeps the length of urlfetch within limits)
console.log('Getting files in chunks');
const folderIdChunks = spliceIntoChunks_(folderIds, 20);
folderIdChunks.map((ids, idx) => {
const filesQuery = ` '${ids.join("' in parents or '")}' in parents`;
console.log(`Getting filesList batch: ${idx + 1}`)
let responseItems = driveCall_(filesQuery);
filesList = [...filesList, ...responseItems];
})
}
// constructing the 2d array for google sheets
const heads = [['Path', 'Name', 'ID', 'Link', 'Created Date', 'Modified Data', 'Mime Type', 'Size']];
const res = filesList.map(f => {
f.path = folderPathDict[(f.parents) ? f.parents[0] : folderId];
return [f.path, f.name, f.id, f.webViewLink, new Date(f.createdTime), new Date(f.modifiedTime), f.mimeType, f.quotaBytesUsed]
});
// writing the results to the report
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheets()[1]
sheet.getRange(1, 1, res.length + 1, res[0].length).setValues([...heads, ...res]);
sheet.getRange(2, 1, res.length, res[0].length).sort([1, 2])
}
/**
* Get folder path
* Based on https://github.com/brucemcpherson/bmFolderFun/blob/main/Code.gs#L29
* @param {string} [folder=null] the folder to get the path of
* @param {array} allFolders files response
* @param {string} path current constructed folder path
* @returns {string} a drive folder path
*/
function getFolderPath_(folder = null, allFolders, path = '/',) {
if (!folder) return '';
let ar = allFolders.filter(e => e.id == folder);
// if no folders at the root
if (!ar.length) {
return "/My Drive" + path;
} else {
// else contrinue to construct the path
return getFolderPath_(ar[0].parents[0], allFolders, '/' + ar[0].name + path);
}
}
/**
* Split array into chuncks
* @see https://quickref.me/split-an-array-into-chunks
* @param {Array} arr input array
* @param {Integer} size of arrays
* @return {Array} of arrays
*/
function spliceIntoChunks_(arr, size) {
return arr.reduce((acc, e, i) => (i % size ? acc[acc.length - 1].push(e) : acc.push([e]), acc), []);
}
/**
* Make Drive API v3 files.lists calls
* @param {String} optional query term
* @return {Object} files resource object array
*/
function driveCall_(query) {
// options
const options = {
muteHttpExceptions: true,
method: "GET",
headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() },
};
// variables
let pageToken = null;
let filesList = [];
// loop for drive api calls
do {
const params = {
"pageSize": 1000,
"fields": "files(id,name,createdTime,modifiedTime,size,parents,webViewLink,mimeType,quotaBytesUsed),nextPageToken",
}
// additional parameters
if (pageToken) params.pageToken = pageToken;
if (query) params.q = query;
// construct the call querystring
const queryString = Object.keys(params).map(function (p) {
return [encodeURIComponent(p), encodeURIComponent(params[p])].join("=");
}).join("&");
const url = "https://www.googleapis.com/drive/v3/files?" + queryString
const response = JSON.parse(UrlFetchApp.fetch(url, options).getContentText());
if (response.files.length > 0) filesList = [...filesList, ...response.files];
pageToken = response.nextPageToken;
} while (pageToken);
return filesList;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment