Getting Form Document Properties from forSpreadsheet onSubmit Trigger using GAS

[Darren D'Mello]

I want to build an editor add for Google Form
This creates the response Google Sheet automatically

I create trigger for the form using From spreadsheet - On form submit

 ScriptApp.newTrigger('onSubmit')
    .forSpreadsheet(spreadsheet)
    .onFormSubmit()
    .create();
   
The code is inside the Google Forms Script Editor

function setSettings()
{
  PropertiesService.getDocumentProperties().setProperty("NAME", "Andrew Kings");
}

function getSettings()
{
  return PropertiesService.getDocumentProperties().getProperty("NAME");
}

function onSubmit(e)
{
var properties = PropertiesService.getDocumentProperties().getProperties();
console.log(properties);
}


Since the trigger is forSpreadsheet, when the Google form is submitted the getSettings() will return {}, I believe the trigger runs on behalf of Spreadsheet
How can I associate the script to fetch Forms DocumentProperties when the onSubmit function is run



I cannot use forForm trigger since I need to access the row submission response event object of the sheet.
Is there a modification required in appscript.json manifest to associate the script to Form as well as Spreadsheets?


I am sure, some addons like Document Studio uses forSpreadsheet trigger

Any pointers are greatly appreciated
 

[Bennett, Scott]

If I am understanding you correctly.  If you can get the sheet that the form is going to. Then you can have sheet.getFormUrl() and go from there to get the form properties.

--
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 on the web visit https://groups.google.com/d/msgid/google-apps-script-community/a51e352a-3493-4da4-8207-e6901985f692n%40googlegroups.com.

--

Scott Bennett

Data and Assessment Coordinator/Math Teacher

Bradley-Bourbonnais Community High School

P 815.937.3707 ext. 6010

[Darren D'Mello]

Thanks Bennett for taking a look at this. Your solution doesn't work for me since the addon needs to be deployed as a forms add-on. If I have understood correctly, how would your solution work? How can a sheet access form document properties? There are no methods so far.

Could you share me the code that does so?

@Clark Lind  do you have any insights Clark?

I appreciate everyone's time and efforts. Thanks

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAL7%3DANF82BhaxN25PKa6fz2Kj1VXdYHgC0sHCSEZcD9pzxd2LA%40mail.gmail.com.

[Bennett, Scott]

I don't have anything that would help you then.  Just thought I would throw that out there.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAHeo4XjcfMnp0z3Fy08Y5%2BU0KVZmKo1GPb7NDFZQNXj65hMxuQ%40mail.gmail.com.

[cwl...@gmail.com]

(ref: link)
I think this may do the trick. In your trigger-builder, instead of pointing to the sheet, just point to the code within the form:

 ScriptApp.newTrigger('onSubmit')
  //  .forSpreadsheet(spreadsheet)

    .forForm(form)
    .onFormSubmit()
    .create();

[Darren D'Mello]

Thanks Clark. I agree with you. But we don't get access to the current row that's being written to sheet.

I can easily use last row. If the responses are flooded in a short span this last row doesn't work well to identify the response associated with sheet row.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/94c9426a-4954-4f6b-8dc4-72320297080cn%40googlegroups.com.

[cwl...@gmail.com]

Ahh.. ok. Let me grab an older script I made a couple years ago that seemed to do this. It may give you some ideas.

[cwl...@gmail.com]

This was the script I used to get any form submission into an email, and sent off to those who needed to handle the request. It also set column wrapping on one of the cells of the submission. It ran in the spreadsheet on formSubmit(). Hope this gives you some ideas or solution to try.

//global variable
var ss = SpreadsheetApp.getActiveSpreadsheet();

function onFormSubmit() {
    var sheet = ss.getActiveSheet();
    var aRow = sheet.getActiveRange().getRowIndex();
    var fUrl = ss.getFormUrl();
    var form = FormApp.openByUrl(fUrl);
    var formResponses = form.getResponses();
    var lastResponse = formResponses[formResponses.length - 1]; //this is a zero-based array, so -1 from length
    var editURL = lastResponse.getEditResponseUrl();
//    sheet.getRange(aRow, 34).setValue(editURL).setWrap(true);  //this wrapped the text in col 34 of the response so the url wasn't split and broken

//Send Email
    var emailTo = "theEmai...@someplaceCool.com"; //recipients of email
    var subject = ' Acq Request Form input received'; //subject of email

    //Get input details
    var details = lastResponse.getItemResponses();

    //Build email body content
    var emailBody = 'The following input was received on the Acq Request Form';

    //Build table
    emailBody += '<table width="100%" border="1">';
    //Table header row
    emailBody += '<tr><th>Submitter: ' + lastResponse.getRespondentEmail() + '</th>';
    emailBody += '<th>Edit Link: <a href="' + editURL + '">Link</a></th></tr>';

    //Loop through input details and add them to the table as a new column
    for (var j = 0; j < details.length; j++) {
        var detail = details[j];
        emailBody += '<tr><td width="50%">' + detail.getItem().getTitle() + '</td><td width="50%"><B>' + detail.getResponse() + '</B></td></tr>';
    }
    
    //call GmailApp and send the email
    MailApp.sendEmail({
        to: emailTo,
        subject: subject,
        htmlBody: emailBody //using an htmlbody allows us to send html versus plain text
    });
}

[Darren D'Mello]

Thanks Clark. This is very helpful.

I will try this. I appreciate your time and effort to dig this archive code.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/9641eec1-dedc-4d17-a9e3-74e3745f7c51n%40googlegroups.com.