FormApp.openById() - No item with the given ID could be found

Question

I have a Google Sheets trigger function that on form submit places submissions in sheets based on the value selected for one of my questions. I'm trying to test my function with "Test as an Add-On" feature, but when I submit a form, I receive an error within my onSubmit function with the error message No item with the given ID could be found, or you do not have permission to access it. (line 9, file "Code") from trigger formSubmit

This is the line of the error,

  var form = FormApp.openById(ss.getFormUrl());

which makes me think there is an authentication issue, but I'm not sure how to debug this further

Full code:

function onSubmit(e) {
  //Open Marketing - Discoveries and Changes - v1
  var sheet = e.range.getSheet();

  //Return Spreadsheet that contains this sheet
  var ss = sheet.getParent();

  //Open Marketing - Discoveries and Changes - v1
  var form = FormApp.openById(ss.getFormUrl());

  //Destination sheet based on "Location of Change"
  var destSheet = getSheet(ss, e.namedValue['Location of Change']);

  //Store response in destination sheet
  destSheet.appendRow(e.values);

  function getSheet( spreadsheet, sheetName, headings) {
    spreadsheet = SpreadsheetApp.getActive();
    var sheet = spreadsheet.getSheetByName(sheetName);
    if (sheet == null) {
      sheet = spreadsheet.insertSheet(sheetName);
      if (headings && headings.constructor === Array) {
        sheet.getRange(1,1,1, headings.length).setValues([headings]);
      }
    }
    return sheet;
  }

}

score 0 · Accepted Answer · edited May 23 '17 at 12:01

0

The combination of openById() and ss.getFormUrl() isn't right. In the answer to Adding Tabs to Google Sheets Based on Form Response, openByUrl() was used:

var form = FormApp.openByUrl(ss.getFormUrl());

If you want to use openById(), you'll need to extract the ID from the URL. (See Easiest way to get file ID from URL on Google Apps Script.)

edited May 23 '17 at 12:01

Community

1
1

answered Oct 27 '15 at 14:11

Mogsdad

44,709
21
151
275

great catch! I made the change and don't get the error anymore, but it appears that the `namedValue` is undefined. Would this indicate an issue with form or sheet information? `TypeError: Cannot read property "Location of Change" from undefined. (line 16, file "Code")` – cphill Oct 28 '15 at 17:20
Could it be that my trigger function doesn't work with the `Test as an add-on` feature? I know it won't work within the script editor, but didn't think that this should be an issue when using `Test as an add-on` – cphill Oct 28 '15 at 17:23
How are you installing the trigger? (Must be programmatically.) – Mogsdad Oct 28 '15 at 19:00
I added the trigger via the UI "All your triggers" button. This won't work in testing? – cphill Oct 28 '15 at 19:23
Nope - read [Testing Details](https://developers.google.com/apps-script/add-ons/test#testing_details), _Installable triggers are currently not supported when testing. Functionality that depends on installable triggers will not be testable._ You can still test your trigger in a bound script. – Mogsdad Oct 28 '15 at 19:27
thank you for the insight. Can you point me to a link in the documentation where I can add a bound script? – cphill Oct 29 '15 at 17:17

FormApp.openById() - No item with the given ID could be found

1 Answers1