Google forms issue

[Paul Nicholson]

Hi there,

I'm trying to use the prefilled URL option to prepopulate a form, eventually with a view to using a spreadsheet to get the data to populate the form from.

This is the script code I have so far, but it isn't submitting the form with the supplied data. 

Is there something I'm missing because I just can't work out to have the form submitted?

function myFunction() {
let url = "https://docs.google.com/forms/d/e/{form_ID}/formResponse?usp=pp_url&entry.429610335=Vanilla&entry.429610335=Strawberry";

let response = UrlFetchApp.fetch(url);

} I've removed the form Id for security, 

DISCLAIMER

This email is confidential and subject to important disclaimers and conditions in relation to monitoring, viruses, confidentiality and legal privilege full details of which can be viewed on our Email Policy at the following link: http://www.next.co.uk/privacy/

Next Holdings Ltd registered in England 35161. Registered Office Desford Road Enderby Leicester LE19 4AT. Next Retail Ltd is authorised and regulated by the Financial Conduct Authority for Consumer Credit.

[olivercre...@gmail.com]

Hi Paul,

When it comes to forms, I'd recommend using the FormApp instead of the UrlFetchApp. The FormApp has methods designed to let us create a form response and submit it, while to the best of my knowledge, the UrlFetchApp doesn't let us programmatically submit forms.  

To submit a form response via the FormApp, you can follow these steps. I've also included a code sample at the end in case that's helpful:

1. Get your form with openById and create a form response object by calling the createResponse method on the form.
2. Get the form item (aka question) you want to prefill with getItems or getItemsById and cast it to the correct type. Then, use the item's createResponse method to make an item response object. 
   • Note: The type signature for the createResponse method varies depending on the item type. For example, the createResponse method for a checkbox item takes an array of strings as an argument, while the same method for a multiple choice item takes a string argument. Check the documentation to see what you need to provide as an argument.
3. Attach the item response to the form response object by calling the withResponse method on the form response object, passing in the item response. 
4. Repeat 2 and 3 for each item you want to prefill. 
5. Once you've attached all your item responses to the form response, you can call the submit method on the form response to submit the response. You can also call the toPrefilledUrl method on the form response to make a prefilled url.

Here's an example of how it can look:

function myFunction() {
  const form = FormApp.openById(form_ID)
  const formResponse = form.createResponse()
 
  const checkboxResponse = form.getItems(FormApp.ItemType.CHECKBOX)[0]
    .asCheckboxItem()
    .createResponse(["Vanilla", "Strawberry"])

  formResponse.withItemResponse(checkboxResponse)

  const multipleChoiceResponse = form.getItems(FormApp.ItemType.MULTIPLE_CHOICE)[0]
    .asMultipleChoiceItem()
    .createResponse("Honda")

  formResponse.withItemResponse(multipleChoiceResponse)
 
  formResponse.submit()
  const url = formResponse.toPrefilledUrl() // if you want a prefilled url
}

Best,

[Paul Nicholson]

Thanks,

I'll have a play around with that, hopefully that will do the job.

I'll feedback when tried it out

[Paul Nicholson]

Have got a bit further, here is the full code :-

function fetchActiveRow(){

     var ss = SpreadsheetApp.getActiveSpreadsheet();

     var row = ss.getActiveSheet().getActiveRange().getRow();

     var ui = SpreadsheetApp.getUi();

     var name = SpreadsheetApp.getActiveSheet().getRange("B".concat(row)).getValue();

     var date_from = SpreadsheetApp.getActiveSheet().getRange("C".concat(row)).getValue();

     var date_to = SpreadsheetApp.getActiveSheet().getRange("D".concat(row)).getValue();

     var email = SpreadsheetApp.getActiveSheet().getRange("E".concat(row)).getValue();

     var hours_req = SpreadsheetApp.getActiveSheet().getRange("F".concat(row)).getValue();

     var days_nights = SpreadsheetApp.getActiveSheet().getRange("G".concat(row)).getValue();

     var date_sub = SpreadsheetApp.getActiveSheet().getRange("H".concat(row)).getValue();

     var manager = SpreadsheetApp.getActiveSheet().getRange("I".concat(row)).getValue();

     var accept_decline = SpreadsheetApp.getActiveSheet().getRange("J".concat(row)).getValue();

     

     const form = FormApp.openById("form_ID")

  const formResponse = form.createResponse()

 var date = new Date();

  const name_Response = form.getItems(FormApp.ItemType.LIST)[0]

    .asListItem()

    .createResponse([name])

  const manager_Response = form.getItems(FormApp.ItemType.LIST)[1]

    .asListItem()

    .createResponse([manager])

  const accept_decline_Response = form.getItems(FormApp.ItemType.LIST)[2]

    .asListItem()

    .createResponse([accept_decline])

    const fromDateresponse = form.getItems(FormApp.ItemType.DATETIME)[3]

   .asDateTimeItem()

   .createResponse([date_from])

 

   

  formResponse.withItemResponse(name_Response)

formResponse.withItemResponse(manager_Response)

  formResponse.withItemResponse(accept_decline_Response)

 

  formResponse.submit()

}

when run the script I get the following error :-

1:48:52 PM

Notice

Execution started

1:48:54 PM

Error

TypeError: Cannot read properties of undefined (reading 'asDateTimeItem')

fetchActiveRow

@ Response_Ovrtime.gs:29

It is reading data from the active row on associated spreadsheet. The contents of the cell for DATETIME is formatted as date/time and I've run a test to make sure the data is been read from the spreadsheet,

the name_response,manger_response and accept_decline_response are all been read and submitted to the form correctly, but as soon as put the section to read the first date/time cell, it crashes as shown above, it's as

the script thinks that the fromDateresponse variable is empty!

Any suggestions anyone?

[olivercre...@gmail.com]

Hi Paul,

I'm glad you were able to make some progress. For your current error, it might be related where you are indexing into the array of on this line:

const fromDateresponse = form.getItems(FormApp.ItemType.DATETIME)[3]

The call to form.getItems(FormApp.ItemType.DATETIME), returns an array that only contains the DateTime items in your form. Any other form items are not included in the array. 

In the code above, you are getting the array of DateTime items and setting the fromDateresponse variable equal to the 4th DateTime item in that array. But if your form has fewer than four DateTime items, then that array index will be undefined, so the variable will be set to undefined. I suspect that this may be the source of your error. Try and double check how many DateTime items are in your form to make sure you're getting the item at the correct array index.

Best,

[Paul Nicholson]

Hi Oliver,

you were right with that, changed to :-

 const fromDateresponse = form.getItems(FormApp.ItemType.DATETIME)[0]

That doesn't give the error about been undefined anymore, but I suspect that the date and time from the spreadsheet is in the incorrect format, it's possibly in the full format including GMT +00:00 which throws the following exception :-

3:06:23 PM

Error

Exception: The parameters (number[]) don't match the method signature for FormApp.DateTimeItem.createResponse.

fetchActiveRow

@ Response_Ovrtime.gs:30

As it's close to end of working day, think I'll take a breather and come back to it tomorrow with a clear head!!!!!

[Martin Molloy]

In this line

const fromDateresponse = form.getItems(FormApp.ItemType.DATETIME)[0]

   .asDateTimeItem()

   .createResponse([date_from])

you need to remove the square brackets.

You needed them for the previous items because they were list items and therefore arrays. This item is not an array.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/a6a7b5cb-fa64-4389-9c67-3a8efbffb441n%40googlegroups.com.

[Paul Nicholson]

Hi Martin,

Thank so much for that, because I have two DATETIME fields in the form I assumed that it was an array, but it makes total sense that it's only an array for referencing each field in the form.

Now working for that part of the script, hopefully it's all sorted now!