在绑定到Google表单的脚​​本与其绑定到目标表单的脚本之间传递变量 [英] Pass a variable between a script bound to a Google Form and a script bound to its destination sheet

问题描述

我在将信息从Google表单获取到Google表格时遇到了问题.我希望获取编辑URL onFormSubmit，然后将其设置为存储响应的列中记录的末尾.

研究

• 我问了​​这个问题，该脚本以绑定到工作表的脚本开始，但尝试访问表单.然后，它变成了绑定到表单的脚本，试图访问该工作表.
• 我找到了这个问题，看起来与我的问题有关(用例略有不同).与我的类似，我认为在表单上获取电子表格方法时也会遇到问题.

由于这两种必需的方法仅适用于脚本或表格，因此我一直在碰壁.现在，我在想，我可能需要一个混合解决方案，该解决方案要求将某些代码绑定到工作表，而另一些代码绑定到表单，并在两个都执行onFormSubmit的脚本之间传递一个变量. /p>

这就是我认为应该与表格保持联系的方式

    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

这是我认为应该绑定到工作表上的内容

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

我需要知道的是如何从表单脚本中获取activeFormUrl并将其发送到工作表脚本.我可以使用日志吗?

解决方案

我必须将表单和电子表格操作分开，因为如果我在同一函数中使用其他SpreadsheetApp方法，则无法使用FormApp方法获取formEditURL.只有在onFormSubmit函数中，FormApp方法才起作用.

这是我成功使用的代码段

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

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.

Research:

• I asked this question, which started as a script bound to the sheet but trying to access the form. It then became a script bound to the form, trying to access the sheet.
• I found this question which looks to be related to my question (with a slightly different use case). 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. 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

这篇关于在绑定到Google表单的脚​​本与其绑定到目标表单的脚本之间传递变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！