CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsresetgoogle-forms
user2847142
user2847142

Reputation: 3

Google forms script to delete responses counter

I have tried and combined a few pieces of script to delete rows, but this does not reset the counter. Help resetting responses would be appreciated.

My copy sheet function, and delete all rows function works, but the counter remains, showing 58 responses.

I use the triggers to set the copy and delete functions to occur daily. (sheet url excluding the "docs.google.com..." 0AvTM4SfinH2NdGp1MHdzWms2QnpUMnFiMHJXd1dlV1E&usp) This is what I have so far:

function CopySheet() {
    var sh = SpreadsheetApp.getActiveSpreadsheet();
    var ss = sh.getSheets()[0];// here I chose to always get the first sheet in the spreadsheet
    var inputRange = ss.getRange(1,1,ss.getLastRow(),7);
    var data = inputRange.getValues();
    var newData = [];

    newData.push(['Timestamp','Full Name?','Email?','RAG']);
    for(var n=1;n<data.length;++n){ // skip headers by starting at 1
      for(var c=0;c<7;c=c+3){
         var row = [];
         if(c==0){row.push(data[n][0]) ; c++}else{row.push('')};
         row.push(data[n][c])
         row.push(data[n][c+1]);
         row.push(data[n][c+1+1]);//Keep adding a new row and +1 for each extra column
         newData.push(row);
      }
    }

    //This next bit creates a copy of the sheet. I would rather a spreadsheet copy but could only get document copy to work
    sh.insertSheet().getRange(1,1,newData.length,newData[0].length).setValues(newData);
    var doc = DocumentApp.create('Responses document'); // create document
    var file = DocsList.getFileById(doc.getId());
    file.removeFromFolder(DocsList.getRootFolder());
    file.addToFolder(DocsList.getFolder("Folder 1"));

    var table = doc.getBody().appendTable(newData); // create table in a separate process so I can set the style below
    var style = {};
    style[DocumentApp.Attribute.HORIZONTAL_ALIGNMENT] = DocumentApp.HorizontalAlignment.CENTER; // this one has no effect
    style[DocumentApp.Attribute.FONT_FAMILY] = DocumentApp.FontFamily.ARIAL;
    style[DocumentApp.Attribute.FONT_SIZE] = 10;
    style[DocumentApp.Attribute.FOREGROUND_COLOR] = '#0000ff';
    style[DocumentApp.Attribute.BORDER_COLOR] = '#dddddd' ;
    table.setAttributes(style);
}

//This section deletes the sheet, leaving the headers; "function deleteAllResponses()" at the bottom should reset counter but does not work
function DeleteSheet() {
    SpreadsheetApp.flush();

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheets()[0];
    var datarange = sheet.getDataRange();
    var lastrow = datarange.getLastRow();
    var values = datarange.getValues();// get all data in a 2D array

    for (i=lastrow;i>=2;i--) {
        var tempdate = values[i-1][2]; // arrays are 0 indexed so row1 = values[0] and col3 = [2], If I add more columns I need to up this number
        {
            sheet.deleteRow(i);
            function deleteAllResponses() {}
        }
    }
}

Upvotes: 0

Views: 4201

Answers (2)

marikamitsos
marikamitsos

Reputation: 10573

@user2847142, @brian-tompsett, @wchiquito

New Google Forms allows you to delete even individual responses from within a Google Form itself without the need of a script.
There is now a simpler method than the answer given by @wchiquito.

--This is now possible on the New Google Forms--

Google announcement on the 10th of February 2016. (googleappsupdates.blogspot.com/2016/02/new-google-forms-now-default-option.html)

How to delete ALL of the responses:

Delete ALL responses

How to delete individual responses:

Delete Individual

To delete individual responses you click on the "Responses" tab and choose "Individual". You locate the record you wish to delete and click on the trash can icon to delete that individual response.
This will also reset the counter.
However. The response/s will NOT be deleted from the connected to the form spreadsheet. You will have to manually delete those ones (or using a script).

Upvotes: 0

wchiquito
wchiquito

Reputation: 16551

If you mean the counter responses shown on the form:

enter image description here

One option may be to use deleteAllResponses() (read carefully the documentation) from Class Form.

A minimal implementation can be:

/* CODE FOR DEMONSTRATION PURPOSES */
function deleteAllResponses() {
  var form, urlForm = SpreadsheetApp.getActiveSpreadsheet().getFormUrl();
  if (urlForm) {
    form = FormApp.openByUrl(urlForm);
    if (form) form.deleteAllResponses();
  }
}

Upvotes: 2

Related Questions