At last, I decide to use Apps Script to check the structure in the folder of my structure. Currently, this main folder contains around 3000 folders. Each folder need to have 3 sub folders.
In my table (a Google Spreadsheet), every item has a unique name (RFP) and it is possible to find/search the folder with this name, and (it is important) a date of creation of the item.
We have two solutions. First to call directly, from Appsheet, the Apps Script with the list of folder name to check. Second to call a scheduler (in Apps Script) every 15 minutes or every hour.
Here is the part of the script in charge of the check in Google Drive. (the main folder is referenced by an ID)
/*
* To be call directly from Appsheet
*
*/
function checkTheExistingProjectFolder(listRFPprojects) {
// Get the folder of projects of Appsheet XXX
var projectsFolder = DriveApp.getFolderById( projectsFolderGoogleDriveID );
console.log("List of RFP project to check: " + listRFPprojects);
// Create the list of projects number to check as folder
var data = new Array;
if ( listRFPprojects != null) {
//var lstRfp = listRFPprojects.split(',');
//for (var j = 0; j < lstRfp.length; j++) {
// data.push( lstRfp[j] );
//}
data = listRFPprojects.split(',');
}
// Search, one by one the project folder (this method is more quick than to get all existing subfolder)
for (var i = 0; i < data.length; i++) {
// search the exact name of one project
var listFolderFound = projectsFolder.searchFolders( "title = '"+ data[i] +"'" );
// check if the project exists
if (listFolderFound.hasNext()){
//console.log(data[i] + "- already exist");
var childFolder = listFolderFound.next(); // Get the current existing project folder object
findSubStructure_(childFolder); // Check sub folder and create if necessary
} else {
console.log("Project folder not exists: " + data[i] );
var newFolderID = projectsFolder.createFolder( data[I] ).getId();
var childFolder = DriveApp.getFolderById(newFolderID); // Get the new project folder object
console.log("Project folder created: " + data[i]);
findSubStructure_(childFolder); // Check sub folder and create if necessary
}
}
console.log("End of check.");
}
/*
* Test the sub folder of a project
* Hidden function
*/
function findSubStructure_(objFolder) {
// Get the list of all existing folders
var listFolders = objFolder.getFolders();
// create a list of name of existing folder
var childNames = [];
while (listFolders.hasNext()) {
var child = listFolders.next();
childNames.push([child.getName()]);
}
// check the structure
for (var j = 0; j < subProjectFolders.length; j++) {
// check if the required folder is in the list of existing folder
if (childNames.flat().includes(subProjectFolders[j])) {
//console.log(subData[j] + " - already exist");
} else {
console.log(" - Sub folder not exists:" + subProjectFolders[j]);
// Create the new required folder in the current folder of the checked project
var newFolderID = objFolder.createFolder( subProjectFolders[j] ).getId();
DriveApp.getFolderById(newFolderID); // This ask Google to verify him job !!!
console.log(" - Sub folder created:" + subProjectFolders[j]);
}
}
}
/*
* To be call from the App Script scheduler
*
*/
function scheduledCheck(){
extractDataFromSpreadsheedKSA_();
}
LinkedIn
Twitter
