Script to copy and sort form submission data

Question

I'm using Google forms to create a spreadsheet that I want sorted automatically by datestamp Z-A. The sorting will be triggered whenever anyone fills out a form.

I think the way to do it is:

ask if there is a "copy of Form responses" on spreadsheet...
- if yes, clear all contents...
- else...
copy spreadsheet to "copy of form responses"...
sort according to timestamp

Below is what I've cobbled so far. It works only the first time a response is recorded. I'm not a coder so any help is appreciated. If someone could also point me to a list of commands with basic syntax I'd be grateful.

function CopySheet() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var msheet = ss.getSheetByName("Form Responses"); 
    msheet.copyTo(ss);

    var CopySheet = ss.getSheetByName("Copy of Form Responses"); 
    CopySheet.sort(1,   false); // here 1 is for column no. 1 that 
               // is "Column A" and true is for ascending, make it 
               // false if you want descending.
};

Mogsdad Mogsdad · Accepted Answer · 2013-12-10T04:01:03

You can accomplish this without a script, by using QUERY() in the copy sheet. For instance, if you put this function in cell A1 of your copy sheet, and substitute the key for your form response spreadsheet, you'll end up with a reverse-timestamp-sorted copy of the responses:

=Query(ImportRange(spreadsheet_key,"Form Responses!A:Z"), "select * order by Col1 desc")

This data will be refreshed periodically (~5 mins), so it will reflect new form submissions, but not in real-time.

Note: When using ImportRange() as source data for Query, you need to refer to columns in the Query string using ColN notation.

Alternatively, you could produce a form submission trigger function in the spreadsheet receiving the form submissions, and have it copy the sorted form responses to your copy sheet. The following function does that. You need to set it up as a trigger function for Spreadsheet Form Submission Events. For information on how to test such a function, see How can I test a trigger function in GAS?.

function copyFormSubmissions(e) {
  var sourceSheet = e.range.getSheet();
  var data = sourceSheet.getDataRange().getValues();
  var headers = data.splice(0,1)[0]; // remove headers from data
  data.sort(reverseTimestampOrder);  // Sort 2d array
  data.splice(0,0,headers);          // replace headers

  var destId = "--copy-sheet-ID--";
  var destSheet = SpreadsheetApp.openById(destId).getSheetByName('Sheet1');
  destSheet.clear();
  destSheet.getRange(1,1,data.length,data[0].length).setValues(data);
};

function reverseTimestampOrder(a,b) {
  // Timestamp is in first (zero-th) column
  return (b[0]-a[0]);
}

If someone could also point me to a list of commands with basic syntax I'd be grateful.

The Google Apps Script API classes and methods reference is here. If you're learning, try the tutorials (same place), and I recommend you get familiar with Javascript through some form of e-learning - CodeAcademy.com is a good place to start, since it introduces all the language constructs without focusing on web page development, making it very relevant for Googls Apps Script.

Script to copy and sort form submission data

1 Answers