删除Google表单响应后，如何重置关联表格的响应行索引？

I get exactly what you're dealing with—Google Forms keeps a hidden internal counter of total responses ever submitted, even after you delete all existing ones. That's why new submissions jump to the next row instead of starting fresh from row 2. Here are a few ways to fix this:

Manual Method (Quick & Simple) #

If you don't need to automate this, you can reset the index in a few clicks:

• Open your Google Form and go to the Responses tab.
• Click the three-dot menu (⋮) next to the spreadsheet icon.
• Select Unlink form and confirm the action.
• Click the spreadsheet icon again, choose your existing spreadsheet, and pick the sheet you want to link to. This will reset the counter, so new responses start at the first empty row below your headers.

Script-Based Method (Automate the Reset) #

If you want to integrate this into your existing App Script workflow, here's how to adjust your code to reset the row index:

The key steps are unlinking the form from the sheet, recreating the sheet (to preserve your headers), then relinking the form. Here's the updated code:

var _spreadsheetId = "YOUR_SPREADSHEET_ID";
var _formId = "YOUR_FORM_ID";
var _sheet = "YOUR_SHEET_NAME";

function resetFormAndSheet() {
  var financeSheet = SpreadsheetApp.openById(_spreadsheetId);
  var financeForm = FormApp.openById(_formId);
  var expencesSheet = financeSheet.getSheetByName(_sheet);

  // 1. Delete all form responses
  financeForm.deleteAllResponses();

  // 2. Save header row to preserve it
  var headers = expencesSheet.getRange(1, 1, 1, expencesSheet.getLastColumn()).getValues()[0];

  // 3. Unlink form from spreadsheet
  financeForm.setDestination(FormApp.DestinationType.NONE);

  // 4. Delete old sheet and create new one with same name
  financeSheet.deleteSheet(expencesSheet);
  var newExpencesSheet = financeSheet.insertSheet(_sheet);

  // 5. Restore headers
  newExpencesSheet.getRange(1, 1, 1, headers.length).setValues([headers]);

  // 6. Relink form to the new sheet
  financeForm.setDestination(FormApp.DestinationType.SPREADSHEET, _spreadsheetId);
}

Alternative: Bypass Form's Automatic Linking (Full Control) #

If you want to avoid dealing with the form's internal counter entirely, you can set up a trigger to manually write responses to the first empty row. This gives you complete control over where submissions go:

First, unlink the form from the spreadsheet (using the manual method above). Then add this code to your script:

var _spreadsheetId = "YOUR_SPREADSHEET_ID";
var _formId = "YOUR_FORM_ID";
var _sheet = "YOUR_SHEET_NAME";

// Run this once to set up the trigger
function setupFormSubmitTrigger() {
  var financeForm = FormApp.openById(_formId);
  ScriptApp.newTrigger("handleFormSubmit")
    .forForm(financeForm)
    .onFormSubmit()
    .create();
}

// This runs every time a form is submitted
function handleFormSubmit(e) {
  var financeSheet = SpreadsheetApp.openById(_spreadsheetId);
  var expencesSheet = financeSheet.getSheetByName(_sheet);

  // Extract response values in the order of your form questions
  var responseData = e.response.getItemResponses().map(itemResp => itemResp.getResponse());

  // Find the first empty row below headers
  var firstEmptyRow = expencesSheet.getLastRow() + 1;

  // Write the response to that row
  expencesSheet.getRange(firstEmptyRow, 1, 1, responseData.length).setValues([responseData]);
}

After adding this code, run the setupFormSubmitTrigger function once to activate the trigger. From then on, every new submission will be written to the next empty row, regardless of past deletions.

内容的提问来源于stack exchange，提问作者Benoit