-1

I hope this going to be an easy one, however I could not find answer on these forums nor on Google.

The problem.
A code changing Google Form title does work when a function is called "manually" but doesn't work when called via onFormSubmit trigger (the trigger was set-up manually).
I'm the owner of the sheet, however the form is shared with me (I'm an editor, not the owner).

Any ideas please? :)

//the below changes the form title when run directly from editor
function changeFormTitle() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("TEST")
  var url = sheet.getFormUrl()
  console.log(url) // https://docs.google.com/forms/d/---correctID---/viewform
  var form = FormApp.openByUrl(url)
  form.setTitle("new title ***")
}

//the below code throws an exception when executed via onFormSubmit trigger
function onFormSubmit(e) {
  var sheet = e.range.getSheet() //get sheet where a form response was received
  var url = sheet.getFormUrl()
  console.log(url) // https://docs.google.com/forms/d/---sameIDasAbove---/viewform
  try {
    var form = FormApp.openByUrl(url)
    form.setTitle("new title ***")
  } catch (err) { console.log(err) } 
  //throws: { [Exception: No item with the given ID could be found. Possibly because you have not edited this item or you do not have permission to access it.] name: 'Exception' }
}
Cooper
  • 59,616
  • 6
  • 23
  • 54
Rafał
  • 39
  • 3
  • 1
    Perhaps you used the onFormSubmit trigger from the form and not the spreadsheet because the one from the spreadsheet has range but the one from the form does not. There are two possible triggers [from SS](https://developers.google.com/apps-script/guides/triggers/events#form-submit) [From Form](https://developers.google.com/apps-script/guides/triggers/events#form-submit_1) – Cooper Aug 17 '22 at 14:17
  • One trigger can be manually create in the script editor opened from the form edit panel and the other is the script editor opened from the spreadsheet. I linked to two event object above and this is the event object for the forrm `{"authMode":"FULL","response":{},"source":{},"triggerUid":"id"}` and this is the event object for the SS `{"authMode":"FULL","namedValues":{"Untitled Question":["Option 1"],"Timestamp":["8/17/2022 8:15:54"]},"range":{"columnEnd":2,"columnStart":1,"rowEnd":9,"rowStart":9},"source":{},"triggerUid":"12346730","values":["8/17/2022 8:15:54","Option 1"]}` – Cooper Aug 17 '22 at 14:21
  • Thanks for you replies. I wrote the script in Spreadsheet script editor. I've just found out that I had to set-up the onFormSubmit trigger using the account that owns the Google Form - then the onFormSubmit works fine. If I set oFS using another account it doesn't work. – Rafał Aug 17 '22 at 14:29
  • And is the trigger for the script created from the trigger panel access from that script editor? – Cooper Aug 17 '22 at 14:30
  • @Cooper yes, It was created manually. – Rafał Aug 17 '22 at 14:41
  • 1
    @Cooper The solution should not be added to the question, it should be posted as an answer. – Rubén Aug 17 '22 at 15:06

1 Answers1

1

From a OP's comment

I've just found out that I had to set-up the onFormSubmit trigger using the account that owns the Google Form and both codes work fine.

If this solved the problem, then it's very likely that the triggers was created with an account that hasn't edit access to the form. This might accidentally happen when using Google multiple sign-in with Google Apps Script as some pages automatically change the active account to a different account when navigating or clicking a button. To prevent this, instead of using Google multiple sign-in, use a different browser or different browser profile for each account used for Google Apps Script.

According to the comments in both functions you are calling the /viewform URL but FormApp.getFormByUrl requires the /edit URL

Workaround

If sheet.getFormUrl is really returning the wrong URL (includes /viewform insted of /edit), and considering that forms can't be linked to an existing sheet, instead of using this method add the form URL as a string.

As sheet.getFormUrl should return the /edit URL, consider to report this as a bug through the Google Issue Tracker - https://developers.google.com/apps-script/support#bugs

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Hi Rubén, I tried both /viewform and /edit as well, making sure the ID was for the backend of the GForm. The URL returned by getFormUrl() were correct on both occasions. The direct execution manipulated the title of the form just fine, the triggered execution did not change the form's title throwing the error as described above. I've just found out that I had to set-up the onFormSubmit trigger using the account that owns the Google Form and both codes work fine. – Rafał Aug 17 '22 at 14:36
  • The /viewform and /edit URL hasn't the same ID. Only the ID from the /edit URL works with FormApp.getFormById. – Rubén Aug 17 '22 at 15:04
  • @Rafał Answer updated – Rubén Aug 17 '22 at 15:29
  • Thanks @Rubén - however this is incorrect. getFormUrl() returns the URL containing ID for the backend. For example the returned URL ***XxxZ/viewform -> when pasted into browser leads to the frontend ***XabC/viewform. But when you change ***XxxZ/viewform to ***XxxZ/edit it leads you to backend ***XxxZ/edit. Regardless, none of the URLs allowed me to change the form title when attempted via trigger, unless the trigger was set-up by the form owner - in this case the url returned by getFormUrl() worked just fine. – Rafał Aug 18 '22 at 06:31