How to move subfolder based on values in Google Sheet column?

Question

I have a google sheet which I use to Index a list of Property, with an App Script setup on Edit to create a Property SubFolder inside a Draft Folder using the values in column A and B when 'Draft' is Selected in Col C.

Ie. REF + LOCATION

I'm currently trying to get the Script to move the Property SubFolder between 'Draft Folder', 'Live Folder', 'Archive Folder' depending on Listing Status in Column C. (These folders exist inside a main parent and I would like to avoid manually moving the property subfolder between Draft, Live and Archive.

However, I am having issues, in terms of Property Sub-Folder not moving. The Folder IDs are correct and the Folder Names match exactly, but when I select 'Live' or 'Archive' in Col C, the Property Sub-Folder doesn't move from draft into selection folder and nothing happens.

Could anyone please advise accordingly where I'm going wrong, and how to move the propertyFolder with selection at Col C?

The App Script is straightforward in regard to it checks if the folder already exists, and IF not creates the subfolder in Draft and ELSE checks if folder needs to be moved.

Here is the ELSE IF to Set folder where to move the Property SubFolder

} else if ((status == 'Live' && statusFoldername != 'Live Folder') || (status == 'Archived' && statusFoldername != 'Archive Folder')) {
    
                if (statusFoldername != 'Live Folder') {
                    var moveToFolder = liveFolder;
                } else {
                    var moveToFolder = archveFolder;
                }
                propertyFolder.moveTo(moveToFolder);
            }

Here is the full verison..

function createFolderSubFolders(e) {
    // Get Parent folder
    var parentFolder = DriveApp.getFolderById('ID');
    var draftFolder = DriveApp.getFolderById('ID');
    var liveFolder = DriveApp.getFolderById('ID');
    var archveFolder = DriveApp.getFolderById('ID');

    // Get current spreadsheet
    var ss = SpreadsheetApp.openById('ID');
    var i = e.range.getRow();

    // Process if Col B is not empty and Col C equals 'Draft'

    if(ss.getRange('C' + i).getValue() == 'Draft') {
        
    //Set intended folder name
        var ref = ss.getRange('A' + i).getDisplayValue();
        var location = ss.getRange('B' + i).getValue();
        var status = ss.getRange('C' + i).getValue();
        var propertyFolderName = ref + ' - ' + location;
        
    ///Check if folder already exists
        var findTest = false;
        //Get FolderIterator for looking into status folders
        var statusFolders = parentFolder.getFolders();
        // Checks if Parent Folder already has Folder with given name
        while (statusFolders.hasNext() && findTest == false) {
            var statusFolder = statusFolders.next();
            //Property Listings in current iterated status folder.
            var propertyFolders = statusFolder.getFolders();
            while (propertyFolders.hasNext() && findTest == false) {
                var propertyFolder = propertyFolders.next();

                if (propertyFolderName == propertyFolder.getName()) {
                    //Check where property folder lies currently.
                    var statusFoldername = statusFolder.getName();
                    findTest = true;
                    break;
                }
            }
        }
        // Create new property folder in draftFolder if not already created.
        if (findTest == false) {

            var propertyFolder = draftFolder.createFolder(propertyFolderName);

            //Otherwise check if folder needs to be moved to other section.
        
    } else if ((status == 'Live' && statusFoldername != 'Live Folder') || (status == 'Archived' && statusFoldername != 'Archive Folder')) {

**//Set folder where to move the propertyFolder
            if (statusFoldername != 'Live Folder') {
                var moveToFolder = liveFolder;
            } else {
                var moveToFolder = archveFolder;
            }
            propertyFolder.moveTo(moveToFolder);
        }**
    }
}

Answer 1

I believe your goal is as follows.

• You have a sheet including the columns "A" to "C". When the dropdown list of column "C" is changed, you want to create a folder name using columns "A" and "B" and you want to search the folder name from 3 folders of "Live", "Archived", "Draft". When the folder name is not found and the value of column "C" is "Draft", you want to create a new folder using the folder name to "Draft" folder. When the folder name is found, you want to move the folder using the value of column "C".

Modification points:

• When I saw your script, each folder ID is a unique ID. So, in this case, it is not required to scan all folders from each folder. So, I thought that the folder can be directly searched using the folder name from each folder.
• And, in order to retrieve the values from columns "A" to "C", this can be achieved by one call.

By modifying these points, the script might be simple and the process cost of the script might be able to be reduced. When these points are reflected in your script, how about the following modification?

Modified script 1:

Please copy and paste the following script to the script editor of Spreadsheet and save the script. And, in this case, in order to run the script by changing the dropdown list of column "C", please install the OnEdit trigger to the function createFolderSubFolders.

function createFolderSubFolders(e) {
  // I prepared this object from your script.
  // Please set your folder IDs.
  var obj = {
    parentFolder: "###",
    searchFolders: [
      { name: "Draft", id: "###" },
      { name: "Live", id: "###" },
      { name: "Archived", id: "###" },
    ]
  };
  var sheetName = "Sheet1"; // Please set your sheet name.

  var { range, value } = e;
  var sheet = range.getSheet();
  if (sheet.getSheetName() != sheetName || range.columnStart != 3 || !["Live", "Archived", "Draft"].includes(value)) return;
  var [ref, location, status] = sheet.getRange(range.rowStart, 1, 1, 3).getDisplayValues()[0];
  if (!ref && !location) return; // <--- Added.  Or, if (!ref || !location) return;
  var propertyFolderName = ref + ' - ' + location;
  var check = obj.searchFolders.reduce((o, { name, id }) => {
    var f = DriveApp.getFolderById(id).getFoldersByName(propertyFolderName);
    if (f.hasNext()) {
      o = { name, f: f.next() };
    }
    return o;
  }, null);
  if (!check && status == "Draft") {
    DriveApp.getFolderById(obj.searchFolders[0].id).createFolder(propertyFolderName);
  } else if (check && status != "Draft" && check.name != status) {
    check.f.moveTo(DriveApp.getFolderById(obj.searchFolders.find(({ name }) => name == status).id));
  }
}

• In this modified script, I thought that the above goal is achieved.

Sample script 2:

I thought that the following modified script might be able to be used. In this modification, the filename of propertyFolderName is searched by one call.

function createFolderSubFolders(e) {
  // I prepared this object from your script.
  // Please set your folder IDs.
  var obj = {
    parentFolder: "###",
    searchFolders: [
      { name: "Draft", id: "###" },
      { name: "Live", id: "###" },
      { name: "Archived", id: "###" },
    ]
  };
  var sheetName = "Sheet1"; // Please set your sheet name.

  var { range, value } = e;
  var sheet = range.getSheet();
  if (sheet.getSheetName() != sheetName || range.columnStart != 3 || !["Live", "Archived", "Draft"].includes(value)) return;
  var [ref, location, status] = sheet.getRange(range.rowStart, 1, 1, 3).getDisplayValues()[0];
  if (!ref && !location) return; // <--- Added.  Or, if (!ref || !location) return;
  var propertyFolderName = ref + ' - ' + location;
  var q = `title='${propertyFolderName}' and mimeType='${MimeType.FOLDER}' and (` + obj.searchFolders.map(({ id }) => `'${id}' in parents`).join(" or ") + ") and trashed=false";
  var f = DriveApp.searchFolders(q);
  var check = f.hasNext();
  if (!check && status == "Draft") {
    DriveApp.getFolderById(obj.searchFolders[0].id).createFolder(propertyFolderName);
  } else if (check && status != "Draft") {
    var fol = f.next();
    var pid = fol.getParents().next().getId();
    var pname = obj.searchFolders.find(({ id }) => id == pid).name;
    if (pname != status) {
      fol.moveTo(DriveApp.getFolderById(obj.searchFolders.find(({ name }) => name == status).id));
    }
  }
}

References:

• reduce()
• find()