How to archive a Google sheet with a linked form

Question

The Requirements: I am trying to write a script to enable users to archive a spreadsheet - and its linked form - at the end of each year by:

1. creating a folder for the year inside a pre-defined archives folder
2. Making a copy of the spreadsheet to be archived inside the newly created folder.

The Problem: When the sheet is copied and moved to the archive folder, the linked form is also copied but does not automatically move with its associated sheet. It remains in its original folder. I needed to find how to get the copy of the form to also be moved with the spreadsheet?

The solution: I initially wrote a function to create a new folder for each year in a pre-defined archives folder and create a copy of the sheet to archive within that folder. With the much appreciated suggestions from @doubleunary and the feedback from @Rubén, I made few changes to improve my question, and the script. Any idea on how to improve it further would be greatly appreciated.

---

function archiveSpreadSheet() {

  // Main Spreadsheet to be archived 
  var mainSheet = SpreadsheetApp.openById("Spreadsheet ID");
  // Form responses/data sheet 
  var formData = mainSheet.getSheetByName("Sheet name");

  // 1 - Find the date of the last entry in the form responses sheet
  var timestamp = formData.getRange(formData.getLastRow() - 1, 1).getValue();
  // 2 - Get the year in a string format
  var archiveYear = new Date(timestamp).getFullYear().toString();
  // 3 - Create a new folder using the year as name and store its id
  // Note: createNewFolder is a function that takes the id of the
  // parent folder and the chosen name for the folder to create and only creates it if it doesn't already exist.
  var archiveFolderId = createNewFolder("1XTW9WA35PWDUPy8TVpH3xJ4hWbqqk9nj", archiveYear);
  // 4 - Get the name of the newly created folder
  let archiveFolder = DriveApp.getFolderById(archiveFolderId);
  // 5 - create a named copy of the spreadsheet to archive inside the archive folder
  var archiveSheet = DriveApp.getFileById(mainSheet.getId())
                             .makeCopy("Archive Sheet - " + archiveYear, archiveFolder); 
  // 6 - Get the url of the linked form of the copied spreadsheet
  let relatedFormUrl = SpreadsheetApp.openByUrl(archiveSheet.getUrl()).getFormUrl();
  // 7 - Store the form id in a string format 
  let relatedFormId = FormApp.openByUrl(relatedFormUrl).getId().toString();
  // 8 - Rename the form to match its related sheet
  let archiveForm = DriveApp.getFileById(relatedFormId).setName("Archive Form - " + archiveYear);
  // 9 - Using the id, check if the form exists in the in the drive
  if(relatedFormId) {
    // 9.1 - Move the linked form to the same archive folder as its associated sheet
    archiveForm.moveTo(archiveFolder);
  }


}

Answer 1

Here's a bit simplified version of the solution. It could be simplified further by making the createNewFolder() return a Folder object instead of a folder ID.

function archiveSpreadSheetAndLinkedForm() {
  const ss = SpreadsheetApp.openById('...put spreadsheet ID here...');
  const formSheet = ss.getSheetByName('Form Responses 1');
  const timestamp = formSheet.getRange(formSheet.getLastRow() - 1, 1).getValue();
  const archiveYear = String(timestamp.getFullYear());
  const archiveFolder = DriveApp
    .getFolderById(createNewFolder('1XTW9WA35PWDUPy8TVpH3xJ4hWbqqk9nj', archiveYear));
  const linkedFormUrl = DriveApp
    .getFileById(ss.getId())
    .makeCopy(`Archive Sheet - ${archiveYear}`, archiveFolder)
    .getFormUrl();
  const linkedFormId = FormApp.openByUrl(linkedFormUrl).getId();
  if (linkedFormId) {
    DriveApp
      .getFileById(linkedFormId)
      .setName(`Archive Form - ${archiveYear}`)
      .moveTo(archiveFolder);
  }
}