google apps script - getting Exception: Invalid ID error, when using getFileById

Question

I am trying to use a script from a tutorial online that will put URLs in a google forms response sheet so I can go back and edit responses after they have been submitted.

The script comes from this guy https://www.youtube.com/watch?v=nqOE_FIMd_w and his instructions are here: https://docs.google.com/document/d/1m9V_AHZdA24pUAR1xGxQNt_y3k7J9RKoSG5v_9oFvcU/edit

here is the script from the tutorial:

function assignEditUrls() {
  var form = FormApp.openById('Your form ke goes here');

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Your responses Google Sheet name goes here - The tab name, not the file name');

  var data = sheet.getDataRange().getValues();
  var urlCol = Column number where URLs get entered goes here; 
  var responses = form.getResponses();
  var timestamps = [], urls = [], resultUrls = [];
  
  for (var i = 0; i < responses.length; i++) {
    timestamps.push(responses[i].getTimestamp().setMilliseconds(0));
    urls.push(responses[i].getEditResponseUrl());
  }
  for (var j = 1; j < data.length; j++) {

    resultUrls.push([data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'']);
  }
  sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);  
}

It seems all simple enough, but the script keeps giving me an error:

Invalid ID (line 2, file "getEditLinks")

Seems to be a problem with the document key. I have already given the script permission and I have concentrated my attemtps to resolve this by grabbing different parts of this URL for the file. (I remember that Google used to need the whole URL at some point in the past)

Mine is

*https://docs.google.com/spreadsheets/d/1pKmad.....VtT3GaM/edit#gid=1905774080*

(where ..... is more of the doc key. I am not putting the whole lot for security reasons)

According to the tutorial, and all other research into this, it seems this part is the correct part from the URL to use.

1pKmad.....VtT3GaM

But this is what bring the error. I tried the whole URL, the URL just up to the doc key part, and a few other subsets of this, but none work.

Can anyone see what I am doing wrong here?

Answer 1

Issue:

Based on your description you are trying to access a spreadsheet file by its id but you are using FormApp.

Instead you should be using SpreadsheetApp instead:

var form = SpreadsheetApp.openById('1pKmad.....VtT3GaM'); // form is now a spreadsheet object

but the new issue now is that the variable is not form anymore but a spreadsheet object and as a result you will get other errors down the road when calling form.getResponses().

Solution:

You need to use your existing script and put the id of the form instead of the spreadsheet, which can be found on the form url:

var form = FormApp.openById('Form ID here not Spreadsheet ID');

Note:

The form url looks like this:

https://docs.google.com/forms/d/formID/edit

and you can find the form that is attached to the spreadsheet file by the spreadsheet file menu: