Writing a script to auto-create new google sheet upon data entry

Question

I currently have a google sheet that receives data via a form submission from a third party. I'm trying to write a script that creates a new sheet every time a unique UserID is submitted.

For example, If I have 5 submissions where the UserIDs are A,B,B,C,C, I am trying to have the script create a unique sheet for A, B, and C and group duplicate UserID submissions in that sheet. I figure a pivot table filtered for UserID may accomplish the latter half of that but I haven't been able to figure out the sheet creation yet to test it.

So far, this is the code I've been working with:

function formSheet(e) {
  var sheetName = e.namedValues['user-id'][0];
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
if (!sheet) {
  sheet = ss.insertSheet(sheetName);
  var formSheet = e.range.getSheet();
  var headers = formSheet.getRange(1, 1, 1, formSheet.getLastColumn());
  headers.copyTo(sheet.getRange(1, 1));
}
sheet.appendRow(e.values);
}`

For reference, the sheet with the data is titled "submissions", and the data I want is under the header "User-Id" in column 2

Cooper Cooper · Accepted Answer · 2019-05-28T23:50:08

Try this:

You used 'user-id' in your script and 'User-Id' in your question so which is it?

function formsheet(e) {
  Logger.log(JSON.stringify(e));
  var ss=SpreadsheetApp.openById('ssid');
  var name=e.namedValues['user-Id'][0];
  var shts=ss.getSheets();
  if(shts.indexOf(name)==-1) {
    var sh=ss.insertSheet(name);
    var hdrs=e.range.getSheet().getRange(1,1,1,e.range.getSheet().getLastColumn()).getValues()[0];
    sh.appendRow(hdrs);
  }else{
      var sh=ss.getSheetByName(name);
  }
  sh.appendRow(e.values)  
}

Writing a script to auto-create new google sheet upon data entry

1 Answers