Pass a variable between a script bound to a Google Form and a script bound to its destination sheet

Question

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?

Answer 1

I'm not sure if this would work for you, but you can make an HTTPS GET or POST request to an Apps Script project with UrlFetchApp.fetch(url). So, from the Form project, you can make an HTTPS POST request to a published Web App. The published Web App can actually be published from the project bound to the spreadsheet, if you want to do that.

The way that an Apps Script project detects an HTTPS GET or POST request being sent to it, is with either a doGet() or doPost() function.

var webAppUrl = "https://script.google.com/macros/s/123_My_FileID/exec";

var payload = {
  "url":"activeFormUrl"
};

var options = {"method":"post","payload":payload};

UrlFetchApp.fetch(webAppUrl, options);

The above code makes a POST request to another Apps Script project, and sends the payload to the file.

function doPost(e) {
  var theUrl = e.parameter.url;
};

I'm assuming that you are trying to have a spreadsheet that is getting data from multiple Forms?

Answer 2

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