how to clear all cells from google spreadsheet (and change last edited column)

Question

For a script I build I have a form that needs "cleaning" each "n time", removing all responses and also the spreadsheet it is bound to needs to be cleared.

While clearing it is seemingly simple (form and sheet refer the form and the corresponding sheet, it is executed from the scope of the spreadsheet).

form.deleteAllResponses();
var range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
range.clearContent();

This doesn't seem to work: it creates side effects. The form seems to wish to "start over" and just adds a new set of columns representing the answers, not using the already existing columns, ie with 4 answers being in column A to D, the next submission after cleaning will be in column E to H.

Now a change would be

form.deleteAllResponses();
sheet.clear();

However this creates a "similar" problem: the form once agains creates a new set of columns to use. after the last column of the sheet.

So how can I tell the form to "just start reusing the first columns" - or in other words: how can I tell the spreadsheet that "after cleaning the last edited column is A"?

Answer 1

Forms indexes which row is the "active" row for the form submission. You want to make sure to delete the entire row, not just clear the contents. This should do it:

function clearAll() {
  var form = SpreadsheetApp.getActiveSpreadsheet().getFormUrl();
  FormApp.openByUrl(form).deleteAllResponses();
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("formData");
  sheet.deleteRows(2, sheet.getLastRow());
}