using apps script to search a shared drive

[TempleSnr]

Hi,

I've been tinkering with Apps Script for a while but never digging too deep.

I have a need to search all shared drives that I have access to, it could be by filename or a string within the file. I wanted to start with Martin Hawksey's code as a basis, Creating a Google Drive report in Google Sheets using the Google Drive API, where he says "but with minor modification you can implement shared drive support" well, I've been trying to work out how to do it and I'm failing miserably.  I'm using Method Two:

/**

* Method Two: Calling Drive API v3

* By @mhawksey based on https://stackoverflow.com/a/41741521

*/

I guess it's something to do with supportsAllDrives: true but I'm still failing, I tried to add it here:

function driveCall_(query) {

// options

const options = {

muteHttpExceptions: true,

method: "GET",

//can I get it to support shared drives?

supportsAllDrives: true,

headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() },

};

but that didn't work ...

 .... it would be great if someone could please put me out of my misery.

Thank you

[TempleSnr]

ah, I put the supportsAllDrives in the wrong place ... I've put it in the "right" place but it still gives the same error. I took it out of driveCall_ and it's now a little further down in the code:

// loop for drive api calls

do {

const params = {

//can I get it to support shared drives?

"supportsAllDrives": true,

"pageSize": 1000,

"fields": "files(id,name,createdTime,modifiedTime,size,parents,webViewLink,mimeType,quotaBytesUsed),nextPageToken",

}

When I give a parameter which is a folder in a shared drive, it fails. The error message isn't helpful (to me) 

Execution log

2:39:06 PM

Notice

Execution started

2:39:08 PM

Info

Getting files in chunks

2:39:08 PM

Info

Getting filesList batch: 1

2:39:08 PM

Error

TypeError: Cannot read properties of undefined (reading 'length')

generateFolderFilesDriveV3

@ Hawksey v3.gs:61

[Conor McCarrick]

1. Download Drive API Service (will take care of oauth) - on left of your script editor click Services, select Drive, and add.

2. Adjust your code as follows:

// Set folder id to search

let folder_id;

const options = {

supportsAllDrives: true,

includeItemsFromAllDrives: true,

q: `'${folder_id}' in parents and trashed = false`,

fields: 'items(id,title,createdDate,modifiedDate,fileSize,parents,webContentLink,mimeType,quotaBytesUsed)',

}

const data = Drive.Files.list(options).items;

console.log(data);

[TempleSnr]

thank you Conor - I'm still struggling with this. I'm working through the code step by step. I think the fact that I didn't post all the code hasn't helped.

I'll report back later, I'm determined to get it working!

[TempleSnr]

well, I feel like I'm making progress but it's slow.

• I have pruned the original code from Mr. Hawksey so I'm just dealing with my shared drives.
• if anyone can please point me to somewhere that might help to explain how the call querystring is 

What I really want the end product to do is return only files where the content or title has a string match - so I don't expect too many results even if I have many drives.

• At the moment, I am trying to get the code right so it searches for files only, without trying to find a particular string.
• Once that's working, I want to search for filenames containing a certain string.
• Once that's working, I want to search file content.

I'm getting an error because the files are not being returned. Any ID's or names are not the real ones.

1. I get the message "sharedDriveItems: { ...." displaying the first shared drive.
2. I get the message (and I think the syntax is wrong here) "fields=files(id%2Cname%2CcreatedTime%2CmodifiedTime%2Csize%2Cparents%2CwebViewLink%2CmimeType%2CquotaBytesUsed%2CteamDriveId)%2CnextPageToken&corpora=drive&supportsAllDrives=true&includeItemsFromAllDrives=true&supportsTeamDrives=true&driveId=%7B%22name%22%3A%22%5BEXTERNAL%5D%20TEST%20SHARED%20DRIVE%22%2C%22id%22%3A%220ABdrfqDGEvLXUk9PVA%22%2C%22kind%22%3A%22drive%23drive%22%7D&q=trashed%20%3D%20false%20AND%20mimeType%20!%3D%20'application%2Fvnd.google-apps.folder'
3. then I get an error: {error={errors=[{reason=notFound, message=Shared drive not found: {"name":"[EXTERNAL] TEST SHARED DRIVE","id":"ABdrfqDGEvLXUk9PVA","kind":"drive#drive"}, location=driveId, domain=global, locationType=parameter}], message=Shared drive not found: {"name":"[EXTERNAL] TEST SHARED DRIVE","id":"ABdrfqDGEvLXUk9PVA","kind":"drive#drive"}, code=404.0}}
4. then, as I say the results array is empty.

Here's the code:

/**

* list as many shared drives as you want (maxResults)

* useDomainAccess = false so you get only shared drives you can access

*/

function SharedDriveList() {

// Get (first n) Shared Drives for domain (1 in this example)

const drives = Drive.Drives.list({useDomainAdminAccess: false, maxResults: 1}).items;

console.log("sharedDriveItems: " + drives );

drives.forEach(drive => generateDriveFiles(drive));

}

/**

* list all of the files within the shared drive, one shared drive at a time

*

*/

function generateDriveFiles(drive) {

// get all files on this drive

let filesList = [];

console.log('Getting all files');

// exclude folders

// const filesQuery = "trashed = false AND '"+drive.id+"' = teamDriveId AND mimeType != 'application/vnd.google-apps.folder'";

const filesQuery = "trashed = false AND mimeType != 'application/vnd.google-apps.folder'";

filesList = driveCall_(filesQuery,drive);

// constructing the 2d array for google sheets

const heads = [['Path', 'Name', 'ID', 'Link', 'Created Date', 'Modified Data', 'Mime Type', 'Size', 'Shared Drive ID']];

const res = filesList.map(f => {

f.path = 'thisfolderpath';

return [f.path, f.name, f.id, f.webViewLink, new Date(f.createdTime), new Date(f.modifiedTime), f.mimeType, f.quotaBytesUsed,f.teamDriveId]

});

// 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])

}

/**

* Make Drive API v3 files.lists calls

* @param {String} optional query term

* @return {Object} files resource object array

*/

function driveCall_(filesQuery,drive) {

// 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 = {

// I DON'T FULLY UNDERSTAND HOW options and params are working together from here on

// "pageSize": 1000,

"fields": "files(id,name,createdTime,modifiedTime,size,parents,webViewLink,mimeType,quotaBytesUsed,teamDriveId),nextPageToken",

'corpora': "drive", // does using corpora mean that the external shared drive won't work?

'supportsAllDrives': true,

'includeItemsFromAllDrives': true,

'supportsTeamDrives': true,

'driveId': drive

}

// additional parameters

if (pageToken) params.pageToken = pageToken;

if (filesQuery) params.q = filesQuery;

// construct the call querystring

const queryString = Object.keys(params).map(function (p) {

return [encodeURIComponent(p), encodeURIComponent(params[p])].join("=");

}).join("&");

Logger.log(queryString);

const url = "https://www.googleapis.com/drive/v3/files?" + queryString;

const response = JSON.parse(UrlFetchApp.fetch(url, options).getContentText());

Logger.log(response);

if (response.files.length > 0) filesList = [...filesList, ...response.files];

pageToken = response.nextPageToken;

} while (pageToken);

return filesList;

}

[TempleSnr]

Hooray! one step further down the road! 

I have been round numerous blocks over the last few days to get to Google's helpful content, Google Workspace APIs Explorer - New Feature! it didn't actually solve my problem but it did kinda help because of the explanation of the Method here where I removed the deprecated parameters teamDriveId, includeTeamDriveItems, supportsTeamDrives.

With the following code, I am getting the file list that I so desired, now it's time to see if I can filter it .... (I've highlighted the changes)

/**

* list as many shared drives as you want (maxResults)

* useDomainAccess = false so you get only shared drives you can access

*/

function SharedDriveList() {

// Get (first n) Shared Drives for domain (1 in this example)

const drives = Drive.Drives.list({useDomainAdminAccess: false, maxResults: 1}).items;

console.log("sharedDriveItems: " + drives );

drives.forEach(drive => generateDriveFiles(drive));

}

/**

* list all of the files within the shared drive, one shared drive at a time

*

*/

function generateDriveFiles(drive) {

// get all files on this drive

let filesList = [];

console.log('Getting all files');

// exclude folders

// const filesQuery = "trashed = false AND '"+drive.id+"' = teamDriveId AND mimeType != 'application/vnd.google-apps.folder'";

const filesQuery = "trashed = false AND mimeType != 'application/vnd.google-apps.folder'";

filesList = driveCall_(filesQuery,drive.iD);

// constructing the 2d array for google sheets

const heads = [['Path', 'Name', 'ID', 'Link', 'Created Date', 'Modified Data', 'Mime Type', 'Size', 'Shared Drive ID']];

const res = filesList.map(f => {

f.path = 'thisfolderpath';

return [f.path, f.name, f.id, f.webViewLink, new Date(f.createdTime), new Date(f.modifiedTime), f.mimeType, f.quotaBytesUsed,f.driveId]

});

// 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])

}

/**

* Make Drive API v3 files.lists calls

* @param {String} optional query term

* @return {Object} files resource object array

*/

function driveCall_(filesQuery,drive) {

// 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 = {

// I DON'T FULLY UNDERSTAND HOW options and params are working together from here on

// "pageSize": 1000,

"fields": "files(id,name,createdTime,modifiedTime,size,parents,webViewLink,mimeType,quotaBytesUsed,driveId),nextPageToken",

'corpora': "drive", // does using corpora mean that the external shared drive won't work?

'supportsAllDrives': true,

'includeItemsFromAllDrives': true,

'driveId': drive

}

// additional parameters

if (pageToken) params.pageToken = pageToken;

if (filesQuery) params.q = filesQuery;

// construct the call querystring

const queryString = Object.keys(params).map(function (p) {

return [encodeURIComponent(p), encodeURIComponent(params[p])].join("=");

}).join("&");

Logger.log(queryString);

const url = "https://www.googleapis.com/drive/v3/files?" + queryString;

const response = JSON.parse(UrlFetchApp.fetch(url, options).getContentText());

Logger.log(response);

if (response.files.length > 0) filesList = [...filesList, ...response.files];

pageToken = response.nextPageToken;

} while (pageToken);

return filesList;

}

[TempleSnr]

well, I don't know if you're following my mianderings ...  I've got the search working to look at file content, then pulling back results into a sheet. 

1. I need to change the code to be able to search > 100 shared drives (page limit)
2. add an input box for the search string (which I have found elsewhere).

I'll add the completed code when I've finished.

[Peter Berkhout]

const options = {

muteHttpExceptions: true,

method: "GET",

headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() },

};

I think you have to remove the last comma in your options block.

const options = {

muteHttpExceptions: true,

method: "GET",

headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() }

};

Op zondag 27 augustus 2023 om 09:38:17 UTC+2 schreef TempleSnr:

[TempleSnr]

@Peter great spot! (I sometimes wonder how pedantic Apps Script might be).

So the great news is that I have the search working. 

For me, it is searching all 226 shared drives that I have access to - it does not take Admin permissions to search outside the that I have access to.

As you can imagine, that big a search, millions of documents, takes a few minutes, but it's worth the wait.

One last thing - I may have made mistakes or the code may not be that optimal ... please let me know!!

1. I added an HTML box that takes the search input, I'm working on making it a bit more interactive.
2. The code for the search, uses the driveLister function takes a searchString.
   • the html code for the search box came from CodeWithCurt
   • if you don't have the html code, then you can just supply the string
3. I decided to code the spreadsheet interaction so you just have to create a new sheet, add the titles and then run the search. 
   • I'm going to change the code so the s/sheet tab gets the name of the search

/**

* list as many shared drives as you have

* useDomainAdminAccess = false so you get only shared drives you can access

* Implement shared drive support is here: https://developers.google.com/drive/api/guides/enable-shareddrives

*/

/**

* driveLister based on the following:

* https://stackoverflow.com/questions/70289187/how-to-get-a-pagetoken-for-the-files-list-api-endpoint-of-the-google-drives-api#:~:text=function%20myFunction()%20%7B

*

*/

function driveLister(searchString) {

// as we're looping, we need to know which row we're on at the end of each loop

// clear the spreadsheet outside the loop and

// pass the last row to the loop

// run the row counter in the subroutine

const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

var lastRow = sheet.getLastRow()+1;

sheet.getRange('A2:H'+lastRow).clear();

//

// flag whether or not files were found (set to true if found)

var foundRecords = 'false';

//

// now run the query and get the first page of results

//

let driveList = [];

let pageToken = null;

do {

const obj = Drive.Drives.list({

"useDomainAccess": false,

"orderBy": "title",

"maxResults": 100,

"supportsAllDrives": true,

"fields": "nextPageToken,items",

"pageToken": pageToken

});

if (obj.items.length > 0) driveList = [...driveList, ...obj.items];

pageToken = obj.nextPageToken;

} while(pageToken);

driveList.forEach(drive => generateDriveFiles(drive,sheet,searchString,lastRow));

}

/**

*

*/

function generateDriveFiles(drive,sheet,searchString,lastRow) {

// get all files on this drive

let filesList = [];

console.log('Getting all files');

// exclude folders

const filesQuery = "trashed = false AND fullText contains '" + searchString + "' AND mimeType != 'application/vnd.google-apps.folder'";

filesList = driveCall_(filesQuery,drive.id);

// constructing the 2d array for google sheets

const heads = [['Name', 'ID', 'Link', 'Created Date', 'Modified Data', 'Mime Type', 'Size', 'Shared Drive ID']];

const res = filesList.map(f => {

return [f.name, f.id, f.webViewLink, new Date(f.createdTime), new Date(f.modifiedTime), f.mimeType, f.quotaBytesUsed,f.driveId]

});

// writing the results to the report

Logger.log(res);

if (res.length === 0) {

console.log("Array is empty!") }

else {

var foundRecords = 'true';

sheet.getRange(1, 1, res.length + 1, res[0].length).setValues([...heads, ...res]);

lastRow = lastRow+1;

}

}

/**

* Make Drive API v3 files.lists calls

* @param {String} optional query term

* @return {Object} files resource object array

*

* Method: files.listdescribed in the link below

* https://developers.google.com/drive/api/reference/rest/v3/files/list?apix_params=%7B%22q%22%3A%22name%20contains%20%27exam%27%22%2C%22fields%22%3A%22files(name)%22%7D

* Try Google Workspace APIs here

* https://developers.google.com/workspace/explore?filter=

*/

function driveCall_(filesQuery,drive) {

// 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 = {

/**

* I DON'T FULLY UNDERSTAND HOW options and params are working together from here on

* "pageSize": 1000,

* REST:Resource: files can be found here: https://developers.google.com/drive/api/reference/rest/v2/files

*/

"fields": "files(id,name,createdTime,modifiedTime,size,parents,webViewLink,mimeType,quotaBytesUsed,driveId),nextPageToken",

'corpora': "drive",