表单上的应用程序脚本如何将额外数据存储到表单中？ [英] How can an apps-script on a Form store extra data into the Sheet?
问题描述
情况：我们有一个（长）Google表单，可将许多数据存储到Google表格中。通常条目需要进行编辑，使用原始表单进行编辑要比直接编辑表单更容易。 （其中一些项目是文本，长度为几段）。我想在电子表格中存储一个额外的数据，特别是编辑器可以使用该表单编辑行条目的URL。
我已经可以获取所有表单数据，并且可以通过formResponse.getEditResponseUrl（）获取正确的URL。我可以通过电子邮件将所有这些内容发送给用户，通常是收集所有表单条目的编辑。 （感谢StackOverflow中的很多有用的答案，以帮助我实现这一目标！）但是，编辑器必须手动将URL复制并粘贴到电子表格正确行中的附加列中。
我在Sheet类中看到一个接口，用于向电子表格中添加一列，但我没有看到如何为特定行填充表格存储。我们手动添加了该列，并且已经验证了在通过表单进行编辑时，Google不会覆盖它。如何将这一小段数据存储到工作表中？
我错过了什么？任何帮助将不胜感激。谢谢。
<补充说明2015-02-06]
-
我们有些人提交和其他人编辑的形式很长。编辑将使用表单完成，而不是直接在电子表格中编辑，所以我们需要允许编辑者重新编辑响应的URL。
-
我想在表单提交期间将该URL存储到电子表格中，以便有权访问表单的编辑人员可以找到它。
/ b> -
在表单端的脚本中，我可以很容易地计算出该URL，但是现在如何将它存储到额外列中的表单中？
-
在我目前的Form-side脚本中，我收到URL并将其连同所有表单数据一起发送到编辑分发列表的电子邮件中。其中一位编辑然后从电子邮件中复制URL并将其粘贴到工作表中。 （大多数情况下，进入正确的行，甚至:-)这是一个潜在的容易出错的手动步骤。）
-
第二个问题：什么在表格中的行号与form.getResponses（）中的响应号码一致？行号和响应号似乎随着新项目的提交而变动（即，新行），并且旧项目被编辑。可以合理预测工作表的行号，编辑器将在其中找到表单数据？
再次感谢您给予我的任何帮助。我们有一个可生存的临时解决方案。然而，在接下来的几个月内将有大约一百个参赛作品出现，我希望尽可能地对这个过程进行防错处理。
rick 解决方案问题和希望，我已经正确理解它。
可能的问题：
-
脚本不正确地绑定到附加到表单的电子表格上，而不是绑定到表单本身（根据您的描述，这不是您问题中的问题）
-
提交插入和附加列编辑之间或同时提交之间的竞争条件（请参阅代码第27-32行）
-
无需事先从电子表格中选择工作表，即使电子表格仅包含一张工作表，也能直接访问电子表格！ （参见第36-37行代码）使用列数字索引而不是相应的列字母作为getRange（）方法的参数，该方法仅接受列字母AFAIK（见代码第42-43行）
以下代码可以解决所有这些问题（我还没有测试过它，但是它适用于非常类似的场景）：
//将表单列数字索引转换为相应的列字母
函数columnToLetter（column）
{
var temp，letter ='';
while（column> 0）
{
temp =（column - 1）％26;
letter = String.fromCharCode（temp + 65）+ letter;
column =（column - temp - 1）/ 26;
}
返回信;
以下函数必须注册到On form submit事件来自表格 - 不是来自电子表格！ （脚本工具栏 - >资源 - >当前项目的触发器 - >添加一个新的触发器）
//将表格行与响应附加列中的URL
函数onFormSubmit（e）
{
try
{
//从FormApp获取响应URL：
var responseUrl = FormApp.getActiveForm（）。getEditResponseUrl（）;
//或者从事件中获取：
// var responseUrl e.response.getId（）。getEditResponseUrl（）;
// ....................
//其他URL处理
// ......... ...........
//获取此脚本的公共锁定，因为我们即将修改共享资源。
var lock = LockService.getPublicLock（）;
//等待30秒以完成其他进程。
lock.waitLock（30000）;
//等待行插入完成，以便sheet.getLastRow（）方法获取更新的行数
Utilities.sleep（1000）; // 1秒
//将网址插入电子表格
var spreadsheetUrl =https://docs.google.com/spreadsheets/d/YGUgHi28_gYUffGYGGH_78hkO1Pk/edit;
//获取电子表格中的第一张表格（如果您有多张表格，只需更改值[0]）
var sheet = SpreadsheetApp.openByUrl（spreadsheetUrl）.getSheets（）[0];
//获取更新的行数和列数，表单提交后插入新行
var lastRow = sheet.getLastRow（）;
var lastColumn = sheet.getLastColumn（）;
//通过将列索引转换为相应的字母
var lastCell = columnToLetter（lastColumn）+ lastRow.toString（）;获取新行右侧旁边的确切单元格。
//用新的URL设置单元格的内容
sheet.getRange（lastCell）.setValue（responseUrl）;
//释放锁以便其他进程可以继续。
lock.releaseLock（）;
}
catch（error）
{
//如果有错误，显示错误消息
return error.toString（）;
}
}
如有任何其他问题，请写评论。希望它有帮助。
Q: How can an AppsScript attached to a Form store an extra piece of data into the Sheet?
Situation: We have a (long) Google Form that stores many pieces of data into a Google Sheet. Often the entries need to be edited, and it is much easier to edit using the original form than trying to edit directly into the sheet. (Some of the items are text, several paragraphs long.) I would like to store into the spreadsheet one additional piece of data, specifically the URL that an editor can use to edit the row entry using the form.
I can already get all the form data and I can get the right URL with formResponse.getEditResponseUrl(). And I can send all of that in an email to a user, usually the editor who is collecting all the form entries. (Thanks to many helpful answers in StackOverflow for getting me this far!) But the editor has to manually copy and paste the URL into an additional column in the proper row of the spreadsheet.
I see an interface in class Sheet to add a column to the spreadsheet, but I don't see how to populate that extra column for the particular row that the form just stored. We have added the column manually, and have verified that it is not overwritten by Google when editing via the form. How do I store that one little piece of data into the sheet?
What am I missing? Any help will be greatly appreciated. Thanks.
[added clarifications 2015-02-06]
We have a long form that some people submit and other people edit. Editing is to be done using the form, not editing directly in the spreadsheet, so we need the URL that permits the editors to re-edit the response.
I would like to store that URL into the spreadsheet during the form submission, so that the editors, who have access to the sheet, can find it.
In a script on the Form side, I can easily calculate that URL, but now how do I store it into the sheet in an extra column?
In my Form-side script at the moment, I get the URL and send it, along with all the form data, in an email to the editors' distribution list. One of the editors then copies the URL from the email and pastes it into the sheet. (Most of the time, into the correct row, even. :-) This is a potentially error-prone manual step.)
A secondary question: what is up with the row numbers in the sheet versus the response numbers in the form.getResponses()? The row numbers and response numbers seem to wander as new items are submitted (i.e., new rows), and old items are edited. Can one reasonably predict the sheet's row number in which the editor will find the form data?
Again, thanks for any help you can give me on this. We have a survivable interim solution. However, with a hundred or so form entries coming in the next couple months, I would love to error-proof the process as much as possible.
rick
解决方案So, I've just stumbled upon your questions and, hopefully, I've understood it correctly.
Possible problems:
the script is incorrectly bound to the spreadsheet attached to the form and not to the form itself (which is not the problem in your case as far as I understood from your description)
race conditions between submission insertion and additional column edit, or between simultaneous submissions (see lines 27-32 from code)
accessing the spreadsheet directly, without prior selecting a sheet from the spreadsheet, even if it spreadsheet contains only one sheet! (see lines 36-37 from code)
using the column numeric index, instead of the corresponding column letter as argument for getRange() method, which accepts only column letters AFAIK (see lines 42-43 from code)
Below you have the code which should address all these problems (I have not tested it, but it is an adaptation of a perfect working solution for a very similar scenario):
// Converts sheet column numeric index to corresponding column letter function columnToLetter(column) { var temp, letter = ''; while (column > 0) { temp = (column - 1) % 26; letter = String.fromCharCode(temp + 65) + letter; column = (column - temp - 1) / 26; } return letter; }The following function must be registered to an "On form submit" event from form - not from the spreadsheet! (Script Toolbar -> Resources -> Current project's triggers -> Add a new trigger)
// Associated the sheet rows with response URLs in an additional column function onFormSubmit(e) { try { // Get the response Url, either from FormApp: var responseUrl = FormApp.getActiveForm().getEditResponseUrl(); // Or alternatively get it from the event: // var responseUrl e.response.getId().getEditResponseUrl(); // .................... // Other URL processing // .................... // Get a public lock on this script, because we're about to modify a shared resource. var lock = LockService.getPublicLock(); // Wait for up to 30 seconds for other processes to finish. lock.waitLock(30000); // Wait for row insertion to finish, so that sheet.getLastRow() method gets the updated number of rows Utilities.sleep(1000); // 1 second // Here insert the URL to your spreadsheet var spreadsheetUrl = "https://docs.google.com/spreadsheets/d/YGUgHi28_gYUffGYGGH_78hkO1Pk/edit"; // Gets the first sheet inside the spreadsheet (if you have multiple sheets, just change the value [0]) var sheet = SpreadsheetApp.openByUrl(spreadsheetUrl).getSheets()[0]; // Get updated number of rows and columns, after form submit inserted the new row var lastRow = sheet.getLastRow(); var lastColumn = sheet.getLastColumn(); // Get the exact cell, next to the right of the new row, by converting the column index to corresponding letter var lastCell = columnToLetter(lastColumn) + lastRow.toString(); // Set the content of the cell with the new URL sheet.getRange(lastCell).setValue(responseUrl); // Release the lock so that other processes can continue. lock.releaseLock(); } catch (error) { // If there's an error, show the error message return error.toString(); } }For any other questions, just write a comment. Hope it helps.
这篇关于表单上的应用程序脚本如何将额外数据存储到表单中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
-
