I want the folders with all the files or subfolders in them to be moved from one directory/folder to another based on the value in the google sheet. After some research, I have found some snippets that may not do exactly what I want like keep the folder shared after it moves but it does create a copy of the same folder with the same name in another directory and move files there. I am further struggling to make it as per my needs like moving the folder based on values in the spreadsheet.
Here is the snippet that makes a copy of the folder in another directory and moves the files there.
// main function (recursive)
function migrateFolderToSharedDrive(folder,parent) {
var copiedFolder =
parent.createFolder(folder.getName()).setDescription('previous Id:
'+folder.getId());
Logger.log('Folder created with id %s in parent
%s',copiedFolder.getId(),parent.getId());
// move files
var files = folder.getFiles();
while (files.hasNext()) {
Logger.log('File with id %s moved to %s',
files.next().moveTo(copiedFolder).getId(), copiedFolder.getId())
}
// recurse any subfolders
var childfolders = folder.getFolders();
while (childfolders.hasNext()) {
var child = childfolders.next();
Logger.log('processing subfolder %s',child.getId());
migrateFolderToSharedDrive(child,copiedFolder); // recursive call
}
}
Here is the snippet that I tried on my end to make the function run as the values change in the spreadsheet. I tried to do it on onEdit
earlier but it doesn’t work with that(OnEdit requires authorization and DriveApp
don’t work with that). As I am new to coding, I know the function I have made has many bugs and is not working but I am adding it here just to show what I am struggling to achieve.
function movefolder(){
const inactive_folder =
DriveApp.getFileById('1_hBkktH0Alzx06XS0Hu2tI0MSQ_SffKw');
const active_folder =
DriveApp.getFolderById('13uon5guBduiA0gCKiLjl9ElOgek9CiU6');
var lastRow = SS.getSheetByName("Clients").getLastRow();
var statusrange = SS.getSheetByName("Clients").getRange("A2:M" & lastRow);
var statusvalue = statusrange.getValues()
// look into the status of every row
for (i = 0, l = statusvalue.length; i < l; i++){
var client_name = statusvalue[i][1]; // get client name from 2nd column
var client_folder_name = active_folder.getFoldersByName(client_name) //Get
folder name from active folder directory
// Move the folder to inactive directory if the cell value in column 1 is
inactive and folder exists in active folder directory
if (statusvalue[i][0] === 'inactive' && client_name === client_folder_name){
var folder_id = DriveApp.getFolderById(client_folder_name) //Get folder
id from folder name
migrateFolderToSharedDrive(folder_id,inactive_folder); // copy folders and
move files
folder_id.setTrashed(true); // trash original folder
// update the the new url to the 11th column in the sheet
statusvalue[i][11].setValue([migrateFolderToSharedDrive.copiedFolder]);
}
else{
// Move the folder to active directory if the cell value is active and
folder exists in inactive folder
if (statusvalue[i][0] === 'active' && client_name === ia_client_folder_name)
{
var ia_client_folder_name = inactive_folder.getFoldersByName(client_name)
//Get folder name from inactive folder directory
var ia_folder_id = DriveApp.getFolderById(ia_client_folder_name) //Get
folder id from folder name
migrateFolderToSharedDrive(ia_folder_id,inactive_folder); // copy folders
and move files
folder.setTrashed(true); // trash original folder
// update the the new url to the 11th column in the sheet
statusvalue[i][11].setValue([[migrateFolderToSharedDrive.copiedFolder]]);
}
}
}
}
Any help would be highly appreciated. Thank you.