How to (from a Google Spreadsheet) get the ID of a linked form

Question

I have a Google Spreadsheet that a form is linked to and all form responses are stored in. What I am trying to find is the ID of the FORM itself. I tried this but this does not work..

(I'm running the following code FROM The script editor IN the spreadsheet that the form is linked to.)

function getID()
{
 var form = FormApp.getActiveForm();
 var formID = form.getId();
 Logger.log(formID);
}

That returns NULL since the script is container-bound to the spreadsheet itself. Is there any other way to get the ID of the linked form or even the URL of the linked form?

I can MANUALLY get it by doing the following from the Spreadsheet. Form > Edit form This will show me the URL.

IF I knew the NAME of the form I could get it by name using the DriveApp.getFilesByName(), iterate through it and then use the File.getId() but I don't necessarily know the name.

Any ideas?

Undefined

might need to run the function manually first to grant permissions – Oxpecker

Answer 1

To avoid parsing the url, the safest way would be:

Logger.log( (FormApp.openByUrl(SpreadsheetApp.getActiveSpreadsheet().getFormUrl())).getId() );

or long hand version:

function logFormId_LongHand(){
  var formURL = SpreadsheetApp.getActiveSpreadsheet().getFormUrl();
  var form = FormApp.openByUrl(formURL);
  var formId = form.getId();
  Logger.log( formId );
}

To get the form object in a spreadsheet app script I use the following function:

function getLinkedForm(){
  return FormApp.openByUrl( SpreadsheetApp.getActiveSpreadsheet().getFormUrl() );
}

Handrail

Answer 2

To anyone else who stumbles upon this: The new spreadsheets allow this. I've tried it already and it works for me. Here is how I get the ID:

 var formUrl = SpreadsheetApp.getActiveSpreadsheet().getFormUrl();
 var formID = formUrl.match(/[-\w]{25,}/);

I got the regex from this question: Easiest way to get file ID from URL on Google Apps Script

Hope this helps.

Isbella

Answer 3

If you only want the Form ID for the attached Form, and don't need to keep the Form URL for any reason, this one-liner should suffice:

    var formID = SpreadsheetApp.getActiveSpreadsheet().getFormUrl().match(/\/d\/(.{25,})\//)[1];

Rataplan

Answer 4

Yes.

It would be a script like this that would be ran from within the spreadsheet:

function getFormUrl()
{

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var formUrl = ss.getFormUrl();
 Logger.log(formUrl);

}

However, if you try and run this in the new spreadsheets you receive this error message:

The api method 'getFormUrl' is not available yet in the new version of Google Sheets.

So, until they add support it will only work in older versions.

Cord

True, that will get you the Form URL, but the OP asked how to get the Form ID, which requires an additional step to extract the ID portion from the URL. – Rataplan

Answer 5

You can also find out / connect to the original form from the "Help" menu. Type form and then click "Edit Form." It will locate the original form. This was helpful when a search in drive was unsuccessful through the name of the form (???). So if you need the original editable form for whatever reason, can't find it, and have the linked Sheets doc, then you can now backtrack and go from there. :)

Wastrel

This is what OP describes within the question as "I can MANUALLY get it by doing the following from the Spreadsheet. Form > Edit form This will show me the URL.". If so, this probably does not answer the question. If not you need to be more obvious about how this is answering the question. Please edit. – Bargello