Form Responses Creating New Google Sheets Tabs Rather then Bucketing

Question

I have a Google Sheet which is storing the answers to my form and in theory should bucket the responses by the value selected from a drop down for my form labeled "Location of Change". So far the script accurately creates a new sheet from each response and adds a row with that responses information to newly created sheet, but it creates a new sheet for every response rather then add add responses that contain the same value selected from "Location of Change" to the same sheet.

I assume this is happening because there is a disconnect between the destSheet variable that looks for the values of the "Location of Change" and the getSheet() function, but I could be wrong.

Here is my code:

function onSubmit(e) {

  //Open Marketing - Discoveries and Changes - v1
  var sheet = e.range.getSheet();


  //Return Spreadsheet that contains this sheet
  var ss = sheet.getParent();

  //Open Marketing - Discoveries and Changes - v1
  var form = FormApp.openByUrl(ss.getFormUrl());



  //Destination sheet based on "Location of Change"
  var destSheet = getSheet(ss, e.namedValues["Location of Change"]);

  //Store response in destination sheet
  destSheet.appendRow(e.values);

  function getSheet( spreadsheet, sheetName, headings) {
    spreadsheet = SpreadsheetApp.getActive();
    var sheet = spreadsheet.getSheetByName(sheetName);
    if (sheet == null) {
      sheet = spreadsheet.insertSheet(sheetName);
      if (headings && headings.constructor === Array) {
        sheet.getRange(1,1,1, headings.length).setValues([headings]);
      }
    }
    return sheet;
  }


}

Tovly Deutsch Tovly Deutsch · Accepted Answer · 2015-11-06T17:19:28

I think the problem is that e.namedValues stores all values as arrays even if there is only one value. So e.namedValues["Location of Change"] should be

e.namedValues["Location of Change"][0]

Form Responses Creating New Google Sheets Tabs Rather then Bucketing

1 Answers