Search through a folder to move an uploaded file to a subfolder - Google forms

Question

I have a google form that collects details of a set of individuals. They select several options and finally upload a file. I want these files to be uploaded to a specific folder (based on form selection options) that is already created in the google drive. I have found a very good answer in the forum, however, the search of the subfolder (not there is the below reference) is a bit cloudy to me.

Reference: How to Move File Uploads from Google Forms to a Specific Folder & Subfolders in Google Drive

Example: A person fills the form Q1. "1", Q2. "form 1" <- the upload. My other options for Q1 are 2 and 3. Inside my custom "reports" folder (in goolge drive) I have placed the 1,2 and 3 folders. I want this particular upload to end up in the "1" folder. With the below code, the file gets uploaded to some other folder, i.e., Form uploads.

const PARENT_FOLDER_ID = "<folder ID>";

const initialize = () => {
  const form = FormApp.getActiveForm();
  ScriptApp.newTrigger("onFormSubmit").forForm(form).onFormSubmit().create();
};

const onFormSubmit = ({ response } = {}) => {
  try {
    // Get some useful data to create the subfolder name
    const answer = response.getItemResponses()[0].getResponse() // text in first answer
  
    // Get a list of all files uploaded with the response
    const files = response
      .getItemResponses()
      // We are only interested in File Upload type of questions
      .filter(
        (itemResponse) =>
          itemResponse.getItem().getType().toString() === "FILE_UPLOAD"
      )
      .map((itemResponse) => itemResponse.getResponse())
      // The response includes the file ids in an array that we can flatten
      .reduce((a, b) => [...a, ...b], []);

    if (files.length > 0) {
      // Each form response has a unique Id
      const parentFolder = DriveApp.getFolderById(PARENT_FOLDER_ID);
      const subfolder = parentFolder.getFoldersByName(answer).next(); //I am stuck at this point, how can I search for the particular folder and push the file inside
      files.forEach((fileId) => {
        // Move each file into the custom folder
        DriveApp.getFileById(fileId).moveTo(subfolder);
      });
    }
  } catch (f) {
    Logger.log(f);
  }
};

Answer 1

You have a Google Form that accepts file uploads. You intend that the answer to Q#1 on the form will dictate the upload folder for the user file. You have created folders "1, "2","3" on Google Drive to hold uploads.

• a user who answers 1, should have their file upload put in a folder name = "1"
• a user who answers 2, should have their file upload put in a folder name = "2"
• a user who answers 3, should have their file upload put in a folder name = "3"

The solution to this problem is a spreadsheet linked to the Google form and which accepts responses from Form submissions. The spreadsheet will run a script when each form submission is received; the script will move the file upload to the appropriate folder.

---

Installation:

• These script are bound to the spreadsheet.
• Copy ALL the code shown below and paste into the project editor
• find this line const subfolderIDs = ["<<folderID_1>>","<<folderID_2>>","<<folderID_3>>"]
  • substitute the folderId for each of folders "1", "2" and "3".
• Manually run createSpreadsheetOnFormSubmitTrigger()
  • do this once (and only once).
  • This will programmatically create the onFormSubmit trigger

---

// create a trigger that will fire when the spreadsheet has a form submitted to it.
function createSpreadsheetOnFormSubmitTrigger(){
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  ScriptApp.newTrigger("moveFileSubmission")
    .forSpreadsheet(ss)
    .onFormSubmit()
    .create();
}

function moveFileSubmission(e){
  // these are the sub-folders in the UPLOAD FOLDER that relate to the answers to Q1
  const subfolderIDs = ["1S0x9KcR1VgD00Op_G_lV9EnGwRmMuL2P","1VWae65JeDNbXgraIloPh-h9q2CVLXvD4","1m7CcLYcaGOtH_ojMR2RKEWqxEzt7L0Qi"]

  // get Event Objects and values 
  var response = e.values;
  // answer to Question 1
  var question1 = response[1]
  // the file url of the upload
  var fileUrl = response[2]

  // get the fileId from the fileUrl
  var fileId = getIdFromUrl(fileUrl)
  // Logger.log("DEBUG: question#1 answer = "+question1+", URL = "+fileUrl+", fileID = "+fileId)

  // get the subfolderID based on the answer to qn#1
  var subfolderID = subfolderIDs[(question1-1)]
  // Logger.log("DEBUG: the subfolder id = "+subfolderID)

  // get the subfolder
  var subfolder = DriveApp.getFolderById(subfolderID)

  // Move the uploaded file into the appropriate folder
  DriveApp.getFileById(fileId).moveTo(subfolder)
}


//Easiest way to get file ID from URL on Google Apps Script
// https://stackoverflow.com/a/16840612/1330560
function getIdFromUrl(url) { 
  return url.match(/[-\w]{25,}/); 
}

---

LOGIC

• const subfolderIDs - the target folderIds are in an array (zero-based). This makes it easy to take the answer to question#1 and select the appropriate folderId.
• var response = e.values;- the solution makes use of Event Objects
• var question1 = response[1] & var subfolderID = subfolderIDs[(question1-1)]
  • get answer to question#1,
  • subtract 1 (for the zero-based array),
  • get that value from the array of subfolderIDs.
• var fileUrl = response[2] - the url of the uploaded file is included in the Event Objects BUT there's currently no way to get a file by URL. So we need to use a utility getIdFromUrl() to extract the FileID from the URL.
• var subfolder = DriveApp.getFolderById(subfolderID) - gets the destination folder.
• DriveApp.getFileById(fileId).moveTo(subfolder) - moves the file to the appropriate folder.

---

Comments on the OP's referenced script

The OP included a script from How to Move File Uploads from Google Forms to a Specific Folder & Subfolders in Google Drive. Though it appeared similar to the OP's scenario, it was actually quite different.

• involved multiple uploads per user
• required a unique upload folder for EVERY user.
• got file details via the form rather than the spreadsheet.