Please help with script to deal with Google form attachments

[Yuriy Polovskyy]

I'm struggling to write a code to deal with attachments in google form correctly, so I'm reaching out for help.

Just to say I'm not an architect, I'm actually an accountant - but I'm trying to design a very simple software to manage jobs in the motor repairs garage - this is our family business. 

Every new car that comes for repair we fill in one google form. (1 response = 1 car, but it will be edited multiple times during the day/s, as we take customer info first, then we insect, then we fix and so on)

Last question on the form allows to submit attachments - normally pictures and videos.

My script creates a folder for each job in the correct location (it's not duplicated if form is edited multiple times)

The info is stored in the Google Sheet table where I also have:

* the edit link to the form which allow mechanics to easily access the form response for each car - this works perfect.

* the folder link - that will allow easy access to all pictures and videos of a car.

So this is where I need help:

Attachments may be added to the form on initial submission and maybe not.

Attachments maybe added when editing the form

Attachments maybe removed when editing the form

How to complete the script that will store attachments in the JobFolder - the same way they are in the form? eg. 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 to the fist form completion, but I'm really struggling with edits and removal

I would really appreciate any help.

Thank you in advance!

Happy Christmas!

Natasha & Yuriy

const formID = '17UEZAFk8mQyM0YDloFJRCPkacAOZu-qwwasWZOdsKJE';  // Form ID

const spreadsheetID = '1BIjrFASByFWhzY6COVNl0j6LPCvd0jvecNZ1uakSNEE'; // Spreadsheet ID

const parentFolderID = '1p7xfbjt9-8q7-3lL-V8-ClBuFM4Ulyp0';  // 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)

  }

}