Different Google Form Urls (How to find the linked "Form Responses" sheet) in a large Spreadsheet?

Question

One Google Spreadsheet has several sheets, some of them linked to the Google Forms. I tried to use this example but I couldn't find the linked sheet. In one case it shows the link to the form without any domain, but sheet.getFormUrl() returns the link with a domain and with another form's ID! When I used that link it was replaced in the browser on first link (without domain). In this case how to find the linked sheet and why links are different?!

I have onFormSubmit Event handler (on Form side):

/** 
 *  This function is retrieved by onFormSubmit Trigger on the Form side (not on Spreadsheet side)
 * 
 *  https://stackguides.com/questions/53377027/how-do-i-enable-permissions-to-formapp
 *  View / Show Manifest file:
 *  Add:
 *    "oauthScopes": [
 *      "https://www.googleapis.com/auth/forms",
 *      "https://www.googleapis.com/auth/spreadsheets"
 *    ]
 */
function onFormSubmit(event) {
  try {
    Logger.log(JSON.stringify(event));

    const sheet = getLinkedSheet(event);
    if(sheet) {
      Logger.log(sheet.getName());
    }

  } catch (err) {
    Logger.log(err.toString());
  }
}

function getLinkedSheet(event) {
  // Get the form to which this script is bound.
  //var form = FormApp.getActiveForm();
  //OR:
  var form = event.source; 
  var destinationId = form.getDestinationId();

  //No domain: https://docs.google.com/forms/d/e/**[ID1]**/viewform <------------- A
  var formURL = form.getPublishedUrl(); 

  var formID = form.getId();

  Logger.log("Form's URL:" +formURL);

  var ss = SpreadsheetApp.openById(destinationId);

  var sheets = ss.getSheets();  
  var match = null;
  for(var i=0; i<sheets.length; i++) {  

    //With domain: https://docs.google.com/a**/[DOMAIN]**/forms/d/**[ID2]**/viewform <----------- B
    Logger.log(JSON.stringify( sheets[i].getFormUrl() ) ); 

    if(sheets[i].getFormUrl() == formURL) {
      match = sheets[i]; //<----------------------- NO MATCHES FOUND
    }
  }

  Logger.log(JSON.stringify(match)); //null
  return match;
}

david russell david russell · Accepted Answer · 2019-03-18T00:50:26

I have the same problem and have submitted a bug report at https://issuetracker.google.com/issues/128732931.

To get around the problem, I'm opening the form(s) for each sheet's formUrl and then checking and comparing the form IDs:

var form = FormApp.getActiveForm();
var formId = form.getId();
const matches = spreadSheet.getSheets().filter(function (sheet) {
  var sheetFormUrl = sheet.getFormUrl();
  if (sheetFormUrl){
    return FormApp.openByUrl(sheetFormUrl).getId() === formId;
  }
});
const sheet = matches[0]

Different Google Form Urls (How to find the linked "Form Responses" sheet) in a large Spreadsheet?

1 Answers