Moving Google Form attachments if multiple form edits

Question

I'm trying to design a very simple software to manage jobs in the motor repairs garage. I was able to write the code below:

Spreadsheet Image

Google Form Image

const formID = 'FORM_ID';  // Form ID
const spreadsheetID = 'SPREADSHEET_ID'; // Spreadsheet ID
const parentFolderID = 'PARENT_FORLDER_ID';  // Parent Folder ID (new folder location)

function onFormSubmit(e) {
  const sheet = SpreadsheetApp.openById(spreadsheetID).getSheets()[0];
  const lastRow = sheet.getLastRow();  // Get the last row of data
  const formResponse = e.response;    // Form response

  // Get the Folder ID and Response ID for the submission
  const responseId = formResponse.getId();  // Form Response ID

  // Job number assignment based on the row number
  const jobNumber = lastRow;  // The job number is based on the last row
  sheet.getRange(lastRow, 1).setValue(jobNumber);  // Set job number in Column 1

  // Grab the Edit Response URL and paste it in Column 5
  const editResponseUrl = formResponse.getEditResponseUrl();
  const editUrlCell = sheet.getRange(lastRow, 5).getValue();  // Check if the edit URL already exists

  // Ensure the edit response URL is only set once, on the first submission
  if (!editUrlCell) {
    sheet.getRange(lastRow, 5).setValue(editResponseUrl);  // Set edit response URL in Column 5
  }

  // Create a folder for each submission with a name format: Job Number - Column D value
  const folderUrlCell = sheet.getRange(lastRow, 6).getValue();  // Check if folder URL already exists

  // Proceed only if the folder URL is not already set (i.e., only on the first submission)
  if (!folderUrlCell) {
    const attachments = formResponse.getItemResponses().pop().getResponse();  // Get the last response (attachments)

    // Get job number and info from Column D
    const jobNumberValue = sheet.getRange(lastRow, 1).getValue();
    const columnDValue = sheet.getRange(lastRow, 4).getValue();  // Column D (e.g., job description or title)

    const folderName = `${jobNumberValue} - ${columnDValue}`;
    const parentFolder = DriveApp.getFolderById(parentFolderID);  // Get parent folder using ID

    // Check if folder already exists
    const existingFolders = parentFolder.getFoldersByName(folderName);
    let folder;

    if (!existingFolders.hasNext()) {
      // If folder doesn't exist, create a new one
      folder = parentFolder.createFolder(folderName);
    } else {
      // If folder exists, get the existing folder
      folder = existingFolders.next();
    }

    // Now that the folder is created or found, get the Job Folder ID
    const folderId = folder.getId();  // Get the ID of the "Job Folder"

    // Add Folder ID to Column N (14) and Response ID to Column O (15)
    sheet.getRange(lastRow, 14).setValue(folderId);  // Column N (Job Folder ID)
    sheet.getRange(lastRow, 15).setValue(responseId);  // Column O (Response ID)

    // Store the Folder URL in Column 6 (F)
    const folderUrl = folder.getUrl();  // Get the URL of the folder
    sheet.getRange(lastRow, 6).setValue(folderUrl);  // Column 6 (Folder URL)
  }
}

How to complete the script that will store attachments in the JobFolder? For example if I add more files in edit mode, those attachments are moved to the JobFolder, if they are removed from the form they are removed from the Jobfolder

I was able to move attachments to the JobFolder if they were attached on the first form completion, but I'm really struggling with edits and removals.

Answer 1

A script is bound to a Google Form. The script creates a job register based on Google Form responses and created a Drive folder for each job. The form allows users to upload attachments and should move any uploaded files into the relevant Job folder. Users may edit a submission and may attach additional files.

The script does not distinguish between new submissions and edited submissions which is resulting in duplicating of file attachments as well as other problems.

The issues that the script needs to address are:

• New submission vs Edited submission
• Moving files to a Job Folder
• What was edited?
• Move ONLY photos added in an edited submission

FWIW, I strongly suggest that the script should be:

• bound to the linked spreadsheet since spreadsheet Event Objects contain much more information
• launched by an installable onFormSubmit trigger
• include another sheet to hold Job details (the "Form Responses" sheet is typically regarded as sacrosanct (opinion??))

New submission vs Edited submission
In a linked Google Sheet,

• new Submissions are appended to the last row of the "Form Responses" sheet (getlastRow),
• edited submissions modify the original submission row (Event Objects => e.range.rowStart). But if the edited submission immediately follows the original submission, then the last row and rowStart will be equal, so a additional distinction is required. isFolderIDBlank tests if the Job Number folder has been created
• if blank (true), then this must be a new submission
• if not blank (false), then this must be an edited submission

Moving files to a Job Folder

There's no need to reinvent the wheel, I've used the code from the answer by @AmitAgarwal in Moving Files In Google Drive Using Google Script

What was edited?

The form has seven (7) questions. In the case of an edited submission, the Event Objects only include a timestamp and values for edited fields. The edited values can be identified by looping through the "values" array checking for non-empty values. Depending on which field has been edited, the script should update the respective value in the "JobMaster" sheet (this is something for the Op to address).

Move ONLY photos added in an edited submission

When editing a submission,

• it is NOT possible to delete an attachment
• it IS possible to add new attachments.

If file(s) are uploaded in an edited submission, the URLs are appended to the list of attachments in the values Event Object. So, the script loops though the list backwards and moves only the number of files added in the edited submission

The number of additional files equals:

• number of files in the values array LESS
• number of files from the original and previous edited submissions (from the "JobMaster" sheet)

---

function formSubmission(e) {

  // display all Event Objects
  Logger.log(JSON.stringify(e)) // DEBUG

  var formID = '1NYT3m5ER3RcJdZ9KATH7eL9qESi6RPlm3NFW0JoDO4g';  // Form ID
  var parentFolderID = '1Js2YuJCvgbG3KojBzLMsxcEsjkV1bRNFqt-CpgWWPDLWtfqHuYF0XYL3c6-A2HG5RlKqH6f8';  // Parent Folder ID for Job Folders

  // identify the Form responses sheet
  var responsesSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Form Responses 2")
  // identify the output ("JobMaster") sheet
  var masterSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("JobMaster")
  // get the form
  var form = FormApp.openById(formID)

  // get the last row on the Form Responses sheet
  var lastRow = responsesSheet.getLastRow()

  // get the event object for the edited row
  var editedRow = e.range.rowStart
  // Logger.log("DEBUG: the last row = "+lastRow+", edited row = "+editedRow)

  // use FolderID (Column G) as an indicator for a new or edited submission
  // If original submission, FolderID will be blank at this stage
  // If edited, then FolderID will have a value
  var folderIDRange = masterSheet.getRange(editedRow,7)
  var isFolderIDBlank = folderIDRange.isBlank()
  var folderIDValue = folderIDRange.getValue()
  // Logger.log("DEBUG: the folder ID range  = "+folderIDRange.getA1Notation()+", is it blank?"+isFolderIDBlank+", and the value = "+folderIDValue)
  
  // test if this is a new submission or an edit
  if (lastRow == editedRow && isFolderIDBlank == true){
    // must be a new submission
    // Logger.log("DEBUG: new submission")
    
    //create a temporary array to hold data to update to Job Master
    var masterUpdate = []

    // create job number for Column A
    masterUpdate.push(editedRow)

    // insert timestamp (Column B) and a blank value for column C
    masterUpdate.push(e.values[0],"")

    // create and insert a jobnumber for Column D
    var jobNumber = "Job"+(editedRow)
    masterUpdate.push(jobNumber)

    // insert car rego for Column E
    masterUpdate.push(e.values[1])

    // get the edit responseurl for column F
    var dateValue = responsesSheet.getRange(editedRow,1).getValue()
    // get form responses based on timestamp
    var formResponses = form.getResponses(dateValue)
    // get the number of responses
    var numberofFormResponses = formResponses.length
    // Logger.log("DEBUG: number of responses = "+numberofFormResponses)
    // get the last response
    var editResponseUrl = formResponses[(numberofFormResponses-1)].getEditResponseUrl()
    // Logger.log("DEBUG: the response edit url = "+editResponseUrl)
    masterUpdate.push(editResponseUrl)
    // Logger.log("DEBUG: the edit response url = "+editResponseUrl)

    // create folder for job and update to Job Master for Column G
    var parentFolder = DriveApp.getFolderById(parentFolderID)
    var newFolderID = parentFolder.createFolder(jobNumber).getId()
    // Logger.log(DEBUG: "The ID of the new folder is "+newFolderID)
    // insert job number folder
    masterUpdate.push(newFolderID)

    // insert photo details for Column H
    // Logger.log("DEBUG: photos: "+e.values[7])
    masterUpdate.push(e.values[7])

    // move photos to new folder
    // test if there are any photos
    if (e.values[7].length !==0){
      var photoString = e.values[7]
      // create an array of the photo URLs
      var photos = photoString.split(",")
      // loop through array and move each file
      for (var i=0;i<photos.length;i++){
        var photoURL = photos[i]
        var photoID = photoURL.match(/[-\w]{25,}(?!.*[-\w]{25,})/)
        // Logger.log("DEBUG: i="+i+", photo url = "+photoURL+", photo ID = "+photoID)
        var file = DriveApp.getFileById(photoID[0])
        var folder = DriveApp.getFolderById(newFolderID);
        file.moveTo(folder);
        // Logger.log("DEBUG: Moved photos")
      }
    }

    // update the "Job Master" sheet with content of the temporary array
    masterSheet.appendRow(masterUpdate)

  }
  else{
    // must be an edit
    // Logger.log("DEBUG: submission is an edit")

    // get the values from Event Objects
    var values = e.values
    // loop through values in Event Object
    // start at i=1 to exclude the TimeStamp from the analysis
    for (var i=1;i<values.length;i++){
      var v = values[i]
      var vLength = v.length
      //  Logger.log("DEBUG: i:"+i+", value = "+v+", length = "+vLength)
      // if the length is greater than zero, then the answer has een changed
      if(vLength !==0){
        // the value of this question has been changed
        // Logger.log("DEBUG: the response to question#"+i+" was changed")
        // << OP to write code to update any relevant edited field (plus the Timestamp) on "Job Master">>

        // test if the edited response was "File Upload"
        if(i==7){
          // this is an edited answer and one or more attachments were added
          // Logger.log("DEBUG: Question#"+i+" was edited, one or more photos were added. Photos = "+v)
          
          // get the existing photos value from JobMaster
          var masterPhotosRange = masterSheet.getRange(editedRow,8)
          var masterPhotosBlank =masterPhotosRange.isBlank()
          if (masterPhotosBlank == true){
            var submittedPhotosCount = 0
          }
          else{
            var masterPhotosValue = masterPhotosRange.getValues()
            var submittedPhotos= masterPhotosValue.split(",")
            var submittedPhotosCount = submittedPhotos.length
          }
          // Logger.log("DEBUG: number of submitted photos = "+submittedPhotosCount)

          // get details of the edited submission
          var editedPhotos = v.split(",")
          var editedPhotosCount = editedPhotos.length
          // Logger.log("DEBUG: number of edited photos = "+editedPhotosCount)

          // loop through the edited photos
          for (var e=(editedPhotosCount-1);e>=submittedPhotosCount;e--){
            var editedPhotoURL = editedPhotos[e]
            //Logger.log("editedPhotoURL = "+editedPhotoURL)
            var editedPhotoID = editedPhotoURL.match(/[-\w]{25,}(?!.*[-\w]{25,})/)
            // Logger.log("DEBUG: e="+e+", photo url = "+editedPhotoURL+", photo ID = "+editedPhotoID)
          
            // move the file
            var file = DriveApp.getFileById(editedPhotoID)
            // get the folderID from the JobMaster sheet
            var editedfolderID = masterSheet.getRange(editedRow,7).getValue()
            // Logger.log("DEBUG: the folder ID range = "+masterSheet.getRange(editedRow,7).getA1Notation()+", the folder id = "+editedfolderID)
            var folder = DriveApp.getFolderById(editedfolderID)
            file.moveTo(folder);
            // Logger.log("DEBUG: Moved photo ID = "+editedPhotoID)
          } 
        }
        // update Photos value on Job master
        masterSheet.getRange(editedRow,8).setValue(v)
      }
    }
  }
}

---