根据表单提交创建具有名称的新工作表 [英] Create New Sheet with Name Based on Form Submission

问题描述

在Google表格中，我有一个脚本用于为提交的每个Google表单创建一个新的表格.应该根据最后一列G(不是表单提交的列)来创建一个具有名称的新工作表.然后，它从最后一行和标题行中获取信息，并将其复制到所创建工作表的前两行中.它还将公式添加到单元格中，以将信息放入列(转置)并根据创建的工作表将其格式化.

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.

请帮助！ 〜查尔斯

我已复制以下代码:

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);
}

推荐答案

在注释和播放的帮助下，我弄清楚了我需要的代码.非常感谢@Cooper！

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

这里是:

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);
}

这篇关于根据表单提交创建具有名称的新工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！