Roll a form over each cycle

Northwind runs OKR check-ins every quarter. Same form, same questions, same people — but each quarter’s responses need to live in their own bucket so the team can compare Q1 to Q2 without scrolling through nine months of rows. Doing that by hand on the first of each quarter is exactly the sort of chore that gets forgotten until someone notices the chart has gone weird.

This script takes a snapshot of the response sheet, files it in an archive folder, and clears the live sheet so the next cycle starts empty. The form itself is untouched — same URL, same trigger wiring, same destination — so respondents notice nothing.

What you’ll need

• A Google Form linked to a Google Sheet as its response destination. Save the sheet’s ID in RESPONSES.
• A Drive folder for the archives. Save its ID in ARCHIVE_FOLDER. Anything the script owner can write to works — a team-shared folder is ideal.
• A schedule. Quarterly is the example, but the same script handles monthly, fortnightly, or annual rollovers.

The script

// The live response spreadsheet — the one the form writes to.
const RESPONSES = '1abcResponsesId';

// Drive folder where dated snapshots live.
const ARCHIVE_FOLDER = '1abcResponseArchivesId';

// Date format for the archive filename. Sortable, plain, no surprises.
const STAMP_FORMAT = 'yyyy-MM-dd';

/**
 * Snapshot the current response sheet into the archive folder, then clear
 * the live sheet ready for the next cycle.
 */
function rolloverForm() {
  const ss = SpreadsheetApp.openById(RESPONSES);
  const sheet = ss.getSheets()[0];

  // 1. Build the archive filename and copy the file into the archive folder.
  //    makeCopy clones the data, formatting, and protected ranges in one shot.
  const stamp = Utilities.formatDate(new Date(), 'GMT', STAMP_FORMAT);
  const archiveName = `Responses archive — ${stamp}`;
  const archive = DriveApp.getFileById(ss.getId())
    .makeCopy(archiveName, DriveApp.getFolderById(ARCHIVE_FOLDER));
  Logger.log(`Archived to ${archive.getUrl()}`);

  // 2. Clear every row below the header on the live sheet. Skipping when
  //    lastRow is 1 avoids an invalid range error on an already-empty sheet.
  const lastRow = sheet.getLastRow();
  if (lastRow > 1) {
    sheet
      .getRange(2, 1, lastRow - 1, sheet.getLastColumn())
      .clearContent();
    Logger.log(`Cleared ${lastRow - 1} rows from the live sheet.`);
  } else {
    Logger.log('Live sheet was already empty — nothing to clear.');
  }
}

How it works

1. rolloverForm opens the live response spreadsheet by ID and grabs its first sheet — the one Forms writes into by default.
2. It builds a date-stamped archive name like Responses archive — 2025-09-27 using Utilities.formatDate so the stamp sorts cleanly in Drive listings.
3. DriveApp.makeCopy clones the whole spreadsheet into the archive folder. You get a full point-in-time snapshot — values, formatting, charts, and any summary tabs you have built on top.
4. It reads getLastRow to find the last filled row, then clears rows 2 to the end with clearContent. The header stays put.
5. The guard around lastRow > 1 matters — calling getRange with a zero row count throws, which would block the trigger silently on an empty sheet.
6. Both steps log to the executions panel so you can confirm the rollover happened and how many rows moved.

Example run

On 27 September 2025, with 142 responses in the live sheet, a single run of rolloverForm produces:

• A new file in the archive folder: Responses archive — 2025-09-27 holding all 142 rows plus the header and any pivot tabs.
• The live sheet trimmed back to one row — just the headers — ready for Q4 submissions.

The execution log shows:

Archived to https://docs.google.com/spreadsheets/d/1xyz.../edit
Cleared 142 rows from the live sheet.

Trigger it

OKR check-ins are quarterly at Northwind, so the trigger fires on the first day of each quarter:

1. In the Apps Script editor, open Triggers (clock icon, left sidebar).
2. Click Add trigger and pick rolloverForm.
3. Event source: Time-driven. Type: Month timer, day 1, then in the trigger function add a guard so it only runs in Jan, Apr, Jul, Oct:

   function quarterlyRollover() {
     const month = new Date().getMonth();
     if (![0, 3, 6, 9].includes(month)) return;
     rolloverForm();
   }

4. Point the trigger at quarterlyRollover instead of rolloverForm.

For monthly or weekly cycles, drop the guard and pick the matching schedule.

Watch out for

• The form-response link is intact. The script copies and clears the destination sheet — it does not unlink the form. New submissions keep flowing into the live sheet exactly as before.
• Formulas in the live sheet are wiped. clearContent removes every value, including formulas in rows below the header. If you keep summary formulas on the response tab, move them to a second sheet that reads from the response sheet instead.
• Archive copies count against Drive storage. A long-running form with rich formatting can be several megabytes per snapshot — twelve quarters in, that matters. Either archive to a shared drive with more headroom, or export the archive as .xlsx and trash the live copy.
• The rollover is not atomic. If the copy succeeds and the clear fails, the live sheet still holds last cycle’s data — re-run safely, because the next archive will just be a near-duplicate. Avoid the reverse (clear before copy); the script orders the two steps deliberately.