如何在谷歌表单绑定脚本和谷歌表单绑定脚本之间传递变量？ [英] How can I pass a variable between a google form bound script and google sheet bound script?
问题描述
我有问题从Google表单获取信息到Google表单中。我正在寻找获取编辑网址onFormSubmit，然后将其设置到存储响应的列中的记录末尾。
以前我问这个问题无法调用方法getEditResponseUrl在使用表单ID打开表单时绑定到表单上的未定义的Google Apps脚本，该脚本作为绑定到表单的脚本启动，但尝试访问表单。然后成为绑定到表单的脚本，但尝试访问表单。
我发现这个问题看起来与我的问题有关
表单上的应用程序脚本如何将额外数据存储到表单中？稍微不同的用例），但与我的类似，我认为它会在表单上获取电子表格方法时遇到问题。
由于这两种必需的方法只能用于脚本或表单，所以现在我一直在打墙，我认为我可能需要混合解决方案，它需要一些的代码被绑定到工作表上，而另一些绑定到两个脚本之间传递变量的窗体上，这两个脚本都在执行onFormSubmit。
这就是我认为我应该保持绑定的形式
function onFormSubmit（e）
{
Logger.clear; //如果我可以使用日志来传递变量我想在每次提交开始时清除
var form = FormApp.getActiveForm（）;
var activeFormUrl = form.getEditUrl（）; //这是我需要传递给表单的变量
Logger.log（activeFormUrl）; //只是为了确认我们得到的东西，除非我能以某种方式在使用图表脚本之后访问日志
} //这是绑定到Form
的onFormSubmit函数的结尾 这是我认为我应该保持绑定到表格的内容
function onFormSubmit（e）
{
var ss = SpreadsheetApp.getActiveSheet（）;
var createDateColumn = ss.getMaxColumns（）; // CreateDateColumn当前位于AX（列50），它是最后/最大列位置
var urlColumn = createDateColumn-1; // urlColumn当前在AX中（第50列）使用它的相对位置来计算createDateColumn位置
if（ss.getActiveRange（urlColumn）.getValue（）==）//以便后续编辑Google Form不会覆盖editResponseURL
{
var editResponseURL = setGoogleFormEditUrl（ss，createDateColumn，activeFormUrl）;
var createEditResponseUrl = ss.getActiveRange（urlColumn）;
createEditResponseUrl.setValue（activeFormUrl）;
}
else
{
if（ss.getActiveRange（urlColumn）.getValue（）！= activeFormUrl）
{
Logger.log（Something出错了 - URL不匹配+ activeFormUrl）;
Logger.log（ss.getActiveRange（urlColumn）.getValue（））;
var checkLog2 = Logger.getLog（）;
}
else {} //什么也不做
}
} //这是绑定到Sheet
我需要知道的是如何从表单脚本中获取activeFormUrl并将其发送到工作表脚本。我可以使用日志吗？
我必须将表单和电子表格操作分开，因为使用FormApp方法获取formEditURL会如果我在同一个函数中使用其他SpreadsheetApp方法，并且FormApp方法只在onFormSubmit函数中才起作用，那么这种方法就无法工作了。
这里是我使用的代码片段成功
function onFormSubmit（e）
{
var rng = e.range; //收集事件活动范围
var ss = SpreadsheetApp.getActiveSpreadsheet（）; //收集活动电子表格对象
var fUrl = ss.getFormUrl（）; //获取表单url链接活动电子表格
var f = FormApp.openByUrl（fUrl）; //使用表单url打开表单
var rs = f.getResponses（）; //从活动表单获取响应
var r = rs [rs.length - 1]; //获取在活动表单上进行的最后一个响应
var c = getCellRngByCol（rng，'formEditURL'）; //通过搜索标题名称来定位表单url将被存储的单元格
c.setValue（r.getEditResponseUrl（））; //将表单url值设置为正确的单元格以用于活动表单响应
var callSpreadsheetFunctions = spreadsheetFunctions（rng，ss）; //方法调用其他电子表格函数。这必须被模块化，因为如果其他函数出现在同一个函数中，则无法获取表单url
} //这是onFormSubmit函数的结尾
函数spreadsheetFunctions（rng ，ss）
{
var rowIndex = rng.getRowIndex（）; //获取当前响应的行索引。跟踪编号
var createDateCell = getCellRngByCol（rng，'CreateDate'）; //通过搜索标题名称来定位创建的存储单元
var timestampCell = getCellRngByCol（rng，'Timestamp'）; //通过搜索标题名称来定位自动生成的时间戳所在的单元格
var trackingNumberCell = getCellRngByCol（rng，'Tracking ID＃'）; //通过标题搜索来定位跟踪ID＃将存储在哪个单元格中名称
var createDate = setCreateDate（rng，createDateCell，timestampCell）; //方法设置创建日期。注：函数不包含在代码片段中，但留在这里以演示使用的信息的类型
var trackingNumber = setTrackingNumber（rng，rowIndex，trackingNumberCell，createDateCell）; //方法设置跟踪号码。注：函数不包含在代码片段中，但在此处用于演示使用的信息类型
return;
} //这是callSpreadsheetFunctions函数的结尾
函数getCellRngByCol（rng，col）//找到与活动范围和列有关的单元格
{
var aRng = SpreadsheetApp.getActiveSheet（）。getDataRange（）; //获取电子表格数据范围
var hRng = aRng.offset（0，0，1，aRng.getNumColumns（））。getValues（）; / /通过偏移
var colIndex = hRng [0] .indexOf（col）;找到标题行范围; //在标题行中声明列索引
返回SpreadsheetApp.getActiveSheet（）。 getRange（rng.getRow（），colIndex + 1）; //返回传入该方法的活动行和列名的位置处的单元格范围
} //这是getCellRngByCol函数的结尾
I am having issues getting information from a google form into a google sheet. I am looking to get the edit url onFormSubmit and then set it to the end of the record in a column where the responses are stored.
I asked this question previously Cannot call method "getEditResponseUrl" of undefined on Google Apps Script bound to Sheet when opening form using form ID which started as a script bound to the sheet but trying to access the form. Then became a script bound to the form but trying to access the sheet.
I found this question which looks to be related to my question How can an apps-script on a Form store extra data into the Sheet? (with a slightly different use case) but similarly to mine I think it will have issues getting spreadsheet methods while on the form.
Since both required methods that are only available to either the script or the form I keep hitting a wall so now I am thinking that I may need a hybrid solution that requires some of the code to be bound to the sheet and some to be bound to the form with a variable passed between the two scripts that are both executing onFormSubmit.
This is what I think I should keep bound to the form
function onFormSubmit(e)
{
Logger.clear; //if I can use log to pass variable I want to clear out at the beginning of each submission
var form = FormApp.getActiveForm();
var activeFormUrl = form.getEditUrl();//This is the variable I need to pass to the sheet
Logger.log(activeFormUrl); //only to confirm what we are getting unless I can somehow access the log after the fact using sheet script
}//This is the end of onFormSubmit function bound to the Form
This is what I think I should keep bound to the sheet
function onFormSubmit(e)
{
var ss = SpreadsheetApp.getActiveSheet();
var createDateColumn = ss.getMaxColumns(); //CreateDateColumn is currently in AX (Column 50) which is the last/max column position
var urlColumn = createDateColumn-1; //urlColumn is currently in AX (Column 50) Calculating using it's relative position to createDateColumn Position
if (ss.getActiveRange(urlColumn).getValue() == "") // so that subsequent edits to Google Form don't overwrite editResponseURL
{
var editResponseURL = setGoogleFormEditUrl(ss, createDateColumn, activeFormUrl);
var createEditResponseUrl = ss.getActiveRange(urlColumn);
createEditResponseUrl.setValue(activeFormUrl);
}
else
{
if (ss.getActiveRange(urlColumn).getValue() != activeFormUrl)
{
Logger.log("Something went wrong - URL doesn't match" + activeFormUrl);
Logger.log(ss.getActiveRange(urlColumn).getValue());
var checkLog2 = Logger.getLog();
}
else {}//do nothing
}
}//This is the end of the onFormSubmit function bound to the Sheet
What I need to know is how to take activeFormUrl from the form script and send it to the sheet script. Can I use the log?
I had to separate the form and the spreadsheet operations as getting the formEditURL using the FormApp method would not work if I was using other SpreadsheetApp methods in the same function and the FormApp method only worked if it was in the onFormSubmit function.
Here is the code snippet which I used successfully
function onFormSubmit(e)
{
var rng = e.range; //Collects active range for event
var ss = SpreadsheetApp.getActiveSpreadsheet();//collects active spreadsheet object
var fUrl = ss.getFormUrl();//gets form url linked with active spreadsheet
var f = FormApp.openByUrl(fUrl);//opens form using form url
var rs = f.getResponses(); //gets responses from active form
var r = rs[rs.length - 1]; // Get last response made on active form
var c = getCellRngByCol(rng, 'formEditURL'); //locates the cell which the form url will be stored by searching header name
c.setValue(r.getEditResponseUrl());// sets form url value into correct cell for active form response
var callSpreadsheetFunctions = spreadsheetFunctions(rng, ss); //method calls other spreadsheet functions. This had to be modularized as you can't get form url if the other functions are occuring in the same function
}//This is the end of the onFormSubmit function
function spreadsheetFunctions (rng, ss)
{
var rowIndex = rng.getRowIndex();//gets row index for current response. This is used by tracking number
var createDateCell = getCellRngByCol(rng, 'CreateDate'); //locates which cell the createdate will be stored in by searching header name
var timestampCell = getCellRngByCol(rng, 'Timestamp'); //locates which cell the autogenerated timestamp is located in by searching header name
var trackingNumberCell = getCellRngByCol(rng, 'Tracking ID#');//locates which cell the tracking ID# will be stored in by searching by header name
var createDate = setCreateDate(rng, createDateCell, timestampCell); //method sets create date. NOTE: Function not included in code snippet but left here to demonstrate type of information used
var trackingNumber = setTrackingNumber(rng, rowIndex, trackingNumberCell, createDateCell); //method sets tracking number. NOTE: Function not included in code snippet but left here to demonstrate type of information used
return;
} //This is the end of the callSpreadsheetFunctions function
function getCellRngByCol(rng, col)//finds the cell associated with the active range and column
{
var aRng = SpreadsheetApp.getActiveSheet().getDataRange();//gets the spreadsheet data range
var hRng = aRng.offset(0, 0, 1, aRng.getNumColumns()).getValues();//finds the header row range by offsetting
var colIndex = hRng[0].indexOf(col);// declares the column index in the header row
return SpreadsheetApp.getActiveSheet().getRange(rng.getRow(), colIndex + 1); //returns the cell range at the position of the active row and column name passed into this method
}//This is the end of the getCellRngByCol function
这篇关于如何在谷歌表单绑定脚本和谷歌表单绑定脚本之间传递变量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
