Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have tried and combined a few pieces of script to delete rows, but this does not reset the counter. Help resetting responses using script 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, without the requirement to manually delete responses. (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() {
}

}


}
}
share|improve this question
1  
May I ask why you need this? If you could describe exactly what you need, people might find alternative solutions. –  Vidar S. Ramdal Oct 9 '13 at 14:37

2 Answers 2

Each form is a distinct object, separate from the spreadsheet itself. The spreadsheet is merely the destination for the form responses, for the purpose of reading and using them, and responses sent to the spreadsheet are no longer connected to the form itself. The actual responses that are connected to the form are stored with the form.

If you want to clear the form responses, you can do so by going to the form editor and selecting

Responses > Delete all responses

To do so in a script you need to do something like this:

var form = FormApp.openById(id);
form.deleteAllResponses();

Note: The warning given when deleting manually states that 'responses already sent to a spreadsheet will not be deleted'. It should read '...will not be deleted from that spreadsheet'

share|improve this answer
    
This worked. Thank you Tom –  David Tky Oct 11 '13 at 12:17
up vote 0 down vote accepted

I asked the same question on Stackoverflow and got this answer which means I don't have to physically enter the id Answer to this question

Answer from wchiquito:

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

Thank you both for answering my question

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.