Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.4k views
in Technique[技术] by (71.8m points)

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

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...