Create New Sheet with Name Based on Form Submissio

问题:

In Google Sheets I have a script I'm using to create a new sheet for each Google Form that is submitted. It is supposed to create a new sheet with name based on the last column, column G(which isn't a form submitted column). Then it takes the information from the last row and the heading row and copy it to the first two rows of the created sheet. It also adds formulae to cells to put the info into columns(transpose) and format it based on a created sheet.

Right now it is creating the sheet and copying the formulae and format, but not giving it the proper name or pulling the last row information.

Please help! ~Charles

I have copied the code below:

function onFormSubmit() {
  // onFormSubmit
  // get submitted data
  var ss = SpreadsheetApp.openById(
    '...');
  var sheet = ss.getSheetByName("Responses");
  var headings = sheet.getRange(1,1,1,
sheet.getLastColumn()).getValues();
  var lastRow = sheet.getRange(sheet.getLastRow(),1,1,
sheet.getLastColumn()).getValues();
  var studentUsername = lastRow[0][6];
  // check if username has sheet
  if(ss.getSheetByName(studentUsername)){
    var userSheet = ss.getSheetByName(studentUsername);
    // if not make
  } else {
   var userSheet = ss.insertSheet(studentUsername);
    userSheet.getRange(1,1,1,
headings[0].length).setValues(headings);
  }
  // copy submitted data to user's sheet
  userSheet.appendRow([lastRow]);
  userSheet.appendRow(['=CONCATENATE(B6," ",B5)']);
  userSheet.appendRow(['=TRANSPOSE(B1:2)']);
  userSheet.hideRows(1,2);
  userSheet.setColumnWidth(1, 500);
  userSheet.setColumnWidth(2, 500);
  var FormatSheet = ss.getSheetByName("Format");
  var FormatRange = FormatSheet.getRange("a3:b28");
  FormatRange.copyFormatToRange(userSheet,1,3,3,28);
}

回答1:

With some help from the comments and playing around, i figured out the code I need. Big thanks to @Cooper!

Here it is:

function onFormSubmit() {
  // onFormSubmit
  // get submitted data
  var ss = SpreadsheetApp.openById(
    'Sheet_ID');
  var sheet = ss.getSheetByName("Responses");
  var row = sheet.getLastRow();
  var Col = sheet.getLastColumn();
  var headings = sheet.getRange(1,1,1,
Col).getValues();
  var lastRow = sheet.getRange(row, 1, 1, Col);
  var studentUsername = sheet.getRange(row, Col).getValue();
  // check if username has sheet
  if(ss.getSheetByName(studentUsername)){
    var userSheet = ss.getSheetByName(studentUsername);
    // if not make
  } else {
   var userSheet = ss.insertSheet(studentUsername);
    userSheet.getRange(1,1,1,
headings[0].length).setValues(headings);
  }
  // copy submitted data to user's sheet
  userSheet.appendRow(lastRow.getValues()[0]);
  userSheet.appendRow(['=CONCATENATE(B6," ",B5)']);
  userSheet.appendRow(['=TRANSPOSE(B1:2)']);
  userSheet.hideRows(1,2);
  userSheet.setColumnWidth(1, 500);
  userSheet.setColumnWidth(2, 500);
  var FormatSheet = ss.getSheetByName("Format");
  var FormatRange = FormatSheet.getRange("a3:b28");
  FormatRange.copyFormatToRange(userSheet,1,3,3,28);
}