How to get a form linked to a sheet using google app script?

Question

In class Spreadsheet there is getFormUrl() which returns the url for the form attached to the spreadsheet, null if there is no form.

But now that you can have a form attached to each sheet, how do you get the ID or Url of the form attached to a given sheet?

There could be some answers in this question that are useful: http://stackoverflow.com/questions/28548802/getting-responseid-of-latest-form-submission-in-google-apps/28550325#28550325 — Andrew Roberts, Feb 23 '15 at 22:13
Thanks Andrew, good thoughts but I'm pretty sure Sandy is right, when you have multiple forms linked to a spreadsheet (each to a given sheet), there is no current way of getting the ID or Url of any of the forms except the first one, which is possible via `getFormUrl()`. When I get a chance I will submit a feature request as he suggests. @AndrewRoberts — Dana, Feb 25 '15 at 04:59

score 4 · Accepted Answer · edited Jun 06 '22 at 18:04

You can call getFormUrl on the Spreadsheet, and on each Sheet of the Spreadsheet:

let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheets = ss.getSheets();

for (let sheet of sheets) {
    let sheetName = sheet.getName();
    let formUrl = sheet.getFormUrl();
    Logger.log("formUrl1 %s %s", sheetName, formUrl);
    if (formUrl) {
      Logger.log("formid1 %s", FormApp.openByUrl(formUrl).getId());
    }
}

let formUrl = ss.getFormUrl();
Logger.log("formUrl2 %s", formUrl);
if (formUrl) {
  Logger.log("formid2 %s", FormApp.openByUrl(formUrl).getId());
}

In my case, the spreadsheet was created by a form (in the Answers section, the green spreadsheet icon), and formUrl2 refers to this form. The formUrl1 of one of the sheets relates also to this form, because this sheet contains the answers of the form. Another sheet has the url of a second Form, because I connected the second form to the same spreadsheet. A third sheet has null as formUrl, because it is not related to a form.

score -1 · Answer 2 · answered Feb 22 '15 at 14:54

I give an example for you of my script. Maybe it can help you.

example :

var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Sheet1");
  var data = ss.getDataRange().getValues();  // Data for pre-fill

  var formUrl = ss.getFormUrl();             // Use form attached to sheet
  var form = FormApp.openByUrl(formUrl);
  var items = form.getItems();

score -1 · Answer 3 · answered Aug 31 '15 at 18:30

I just took the URL provided by the getFormUrl and assigned letters 49 through 93 to a string. That should provide the Form ID.

var ss=SpreadsheetApp.getActiveSpreadsheet();
var fOrmUrl=ss.getFormUrl();
var sTring="";
for (var i=49; i<93; i++)
{
  sTring=sTring + fOrmUrl[i];
}

score -1 · Answer 4 · answered Mar 18 '19 at 00:46

I've noticed that the sheet.getFormURL() method does not always return the same URL that form.getPublishedUrl() returns. Both URLs are valid, though. To get around the problem, I'm opening the sheet's formUrl and then checking and comparing that form's ID:

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]

How to get a form linked to a sheet using google app script?

4 Answers4

Linked