How to reset Google Form counter values using google apps script

Question

I have several Google forms which are reused frequently with around 300 - 400 form submission each time.

Until now, I have two ways to reset form, by hand:

Deleting the form while keeping the form editor opened.
Select the rows that contain form submissions, right click and choose "Delete ... rows".

With script, I've have no luck finding such method like form.reset(). The method .DeleteRows(fromRow, to Row) just delete the rows, but doesn't affect the counters.

So, how can I use Google apps script to automate the "reset form" task?
Or, how to mimic the second way in script?

Thank you!

Mogsdad Mogsdad · Accepted Answer · 2013-02-22T16:20:06

I have a working solution, that uses deleteRows(). I'm not sure why that didn't work for you, it would be interesting to see your code.

The new Forms product (February 2013) is quite different than the legacy forms - this solution will work on legacy forms only.

Anyway, here's what I have, with before & after screenshots. The tidy() function can be called by passing in the number of rows to be removed, or you can call it from a menu as demonstrated here. A more complete version of this script is available as a gist.

/**
 * Standard onOpen provided in script template.
 */
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{name : "Read Data",functionName : "readRows"},
                 {name : "Tidy Form Responses", functionName : "tidy"}];
  sheet.addMenu("Script Center Menu", entries);
};

/**
 * Prompt user for the number of form response rows to remove, then delete them.
 * Assumes that the form responses are in the active sheet, that there is one
 * row of 'headers' followed by responses (in row 2).
 *
 * @param {number} thisMany (Optional) The number of rows to remove. If not
 *                          specified, user will be prompted for input.
 */
function tidy(thisMany) {
  if (tidy.arguments.length == 0) {
    thisMany = Browser.inputBox("How many responses should be tidied?");
  }

  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.deleteRows(2, thisMany);
}

Before

Spreadsheet with form responses and custom menu

During

Script operation, inputBox

After

Spreadsheet with 2 fewer form responses

How to reset Google Form counter values using google apps script

2 Answers

Before

During

After