用于在多个位置填充GoogleForms响应的脚本 [英] Script to populate GoogleForms response in multiple locations
问题描述
我正在尝试使用同一个电子表格来执行多个自动和半自动功能，其中一个功能是允许用户提交GoogleForms数据。
我的目标是提取表单回复，这样主工作表上的下一个空白行（标题为应用）将填入新提交的数据（其中列名称与表单响应表单的名称相同）。
我一直在玩弄
onEdit（e）{
var ui = SpreadsheetApp.getUi（）;
var ss = SpreadsheetApp.getActiveSpreadsheet（）;
var range = e.range;
var sheet = range.getSheet（）
var newSheet = ss.getSheetByName（Apps）
var sheetName = sheet.getName（）
if（sheetName！=Form ）{
return //退出函数
}
var columnOfCellEdited = e.range.getColumn（）;
if（columnOfCellEdited === 1）
var range2 = range.getRow（）;
var destrange = newSheet.getLastRow（）
sheet = newSheet
//添加公式
var cell = sheet.getRange（C+ destrange）;
cell.setFormula（'= Form！$ C'+ destrange）;
var cell = sheet.getRange（D+ destrange）;
cell.setFormula（'= Form！$ D'+ destrange）;
var cell = sheet.getRange（E+ destrange）;
cell.setFormula（'= Form！$ E'+ destrange）;
var cell = sheet.getRange（F+ destrange）;
cell.setFormula（'= Form！$ F'+ destrange）;
var cell = sheet.getRange（G+ destrange）;
cell.setFormula（'= Form！$ G'+ destrange）;
var cell = sheet.getRange（H+ destrange）;
cell.setFormula（'= Form！$ H'+ destrange）;
}
最后，我发现这个 SO答案，看起来像它会有所帮助，如果我知道在哪里放东西。
编辑：下面试图根据MrRebot的建议实施，不会产生任何可见的结果。应该还是将表单链接起来？
function onEdit（e）{
var form = FormApp.openById（'1YLnK8kw2dRMUUvL1l3s4qjz9fSRIe7kj92MlPV5WV5M'）;
var formResponses = form.getResponses（）;
for（var i = 0; i< formResponses.length; i ++）{
var formResponse = formResponses [i];
var itemResponses = formResponse.getItemResponses（）; （var j = 0; j< itemResponses.length; j ++）{
var itemResponse = itemResponses [j];
Logger.log（'回答＃％s到问题％s是％s'，
（i + 1）.toString（），
itemResponse.getItem（） .getTitle（），
itemResponse.getResponse（））;
}
}
var ss = SpreadsheetApp.getActiveSheet（）;
ss.insertRowAfter（ss.getLastRow（））;
ss.getRange（ss.getLastRow（），1,1，ss.getLastColumn（））。copyTo（ss.getRange（ss.getLastRow（）+ 1,1））;
$ / code>
FormResponse 获取回复列表。查看示例代码：
//通过ID打开表单并记录每个问题的答案。
var form = FormApp.openById（'1234567890abcdefghijklmnopqrstuvwxyz'）;
var formResponses = form.getResponses（）;
for（var i = 0; i< formResponses.length; i ++）{
var formResponse = formResponses [i];
var itemResponses = formResponse.getItemResponses（）; （var j = 0; j< itemResponses.length; j ++）{
var itemResponse = itemResponses [j];
Logger.log（'回答＃％s到问题％s是％s'，
（i + 1）.toString（），
itemResponse.getItem（） .getTitle（），
itemResponse.getResponse（））;
然后尝试按照以下代码插入新行：
var ss = SpreadsheetApp.getActiveSheet（）;
ss.insertRowAfter（ss.getLastRow（））;
ss.getRange（ss.getLastRow（），1,1，ss.getLastColumn（））。copyTo（ss.getRange（ss.getLastRow（）+ 1,1））;
希望这有助于您。
I am trying to use the same spreadsheet for multiple automated and semi-automated functions, one of which is allowing users to submit data GoogleForms.
My goal is to extract form responses, such that the next blank row on a Master Sheet (titled "Apps") is populated with the newly submitted data (in which the column names are identical with that of the "Form Responses" sheet).
I've been playing around with the below, with OnEdit(e) set to "on Form Submit" but that's not quite getting me what I need.
function onEdit(e){
var ui = SpreadsheetApp.getUi();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var range = e.range;
var sheet = range.getSheet()
var newSheet = ss.getSheetByName("Apps")
var sheetName = sheet.getName()
if(sheetName != "Form"){
return //exit function
}
var columnOfCellEdited = e.range.getColumn();
if (columnOfCellEdited === 1)
var range2 = range.getRow();
var destrange = newSheet.getLastRow()
sheet = newSheet
// adds the formulas
var cell = sheet.getRange("C" + destrange);
cell.setFormula('=Form!$C' + destrange);
var cell = sheet.getRange("D" + destrange);
cell.setFormula('=Form!$D' + destrange);
var cell = sheet.getRange("E" + destrange);
cell.setFormula('=Form!$E' + destrange);
var cell = sheet.getRange("F" + destrange);
cell.setFormula('=Form!$F' + destrange);
var cell = sheet.getRange("G" + destrange);
cell.setFormula('=Form!$G' + destrange);
var cell = sheet.getRange("H" + destrange);
cell.setFormula('=Form!$H' + destrange);
}
Lastly, I found this SO answer which looks like it would be helpful if I knew where to put things.
EDIT: Below attempting to implement based on MrRebot suggestions, not producing any visible results. Should form still be linked to sheet?
function onEdit(e) {
var form = FormApp.openById('1YLnK8kw2dRMUUvL1l3s4qjz9fSRIe7kj92MlPV5WV5M');
var formResponses = form.getResponses();
for (var i = 0; i < formResponses.length; i++) {
var formResponse = formResponses[i];
var itemResponses = formResponse.getItemResponses();
for (var j = 0; j < itemResponses.length; j++) {
var itemResponse = itemResponses[j];
Logger.log('Response #%s to the question "%s" was "%s"',
(i + 1).toString(),
itemResponse.getItem().getTitle(),
itemResponse.getResponse());
}
}
var ss = SpreadsheetApp.getActiveSheet();
ss.insertRowAfter(ss.getLastRow());
ss.getRange(ss.getLastRow(),1,1,ss.getLastColumn()).copyTo(ss.getRange(ss.getLastRow()+1,1));
}
On form submit, try using FormResponse to get the list of responses. See sample code:
// Open a form by ID and log the responses to each question.
var form = FormApp.openById('1234567890abcdefghijklmnopqrstuvwxyz');
var formResponses = form.getResponses();
for (var i = 0; i < formResponses.length; i++) {
var formResponse = formResponses[i];
var itemResponses = formResponse.getItemResponses();
for (var j = 0; j < itemResponses.length; j++) {
var itemResponse = itemResponses[j];
Logger.log('Response #%s to the question "%s" was "%s"',
(i + 1).toString(),
itemResponse.getItem().getTitle(),
itemResponse.getResponse());
}
}
Then try to follow this code to insert a new row:
var ss = SpreadsheetApp.getActiveSheet();
ss.insertRowAfter(ss.getLastRow());
ss.getRange(ss.getLastRow(),1,1,ss.getLastColumn()).copyTo(ss.getRange(ss.getLastRow()+1,1));
Hope this helps.
这篇关于用于在多个位置填充GoogleForms响应的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
