google apps script - get form URL from a spreadsheet bound Form

Question

In a spreadsheet script I want to send mail to users that will point them to the URL of a form that will let them enter data. I have tried:

function test1(){

  var formID = FormApp.getActiveForm();
  var formUrl = DriveApp.getUrl(formID);
  sendMail(formUrl);
  return
}

This fails because the value of formID is allways NULL.

Answer 1

If there is one form linked to spreadsheet

Use

var ss = SpreadsheetApp.getActiveSpreadsheet(); // or openById, etc
var formUrl = ss.getFormUrl();

to get its Url. If needed, FormApp.openByUrl(formUrl); returns a pointer to the form, which allows any other methods.

Multiple forms linked to spreadsheet

There is no built-in method to return the list of forms linked to a given spreadsheet; this is an open issue in Apps Script issues tracker. A workaround is to search all forms (as Cyrus Loree did), get the destination of each, and return the list of those where the destination is the spreadsheet of interest. This is how:

function linkedForms() {
  var ssId = SpreadsheetApp.getActiveSpreadsheet().getId();
  var formList = [];
  var files = DriveApp.getFilesByType(MimeType.GOOGLE_FORMS);
  while (files.hasNext()) {
    var form = FormApp.openByUrl(files.next().getUrl());
    try {
      if (form.getDestinationId() == ssId) {
        formList.push(form.getPublishedUrl());
      }
    }
    catch(e) {
    }
  }
  return formList;
}

Remarks:

• I put form.getDestinationId() in a try block because this method throws an error what the form's destination spreadsheet happens to be deleted (instead of just returning null)
• To get the list of form Ids instead of Urls, use form.getId() in the function.