Email completed form to different recipients based on form response

[Paul Nicholson]

Hoping someone can help with this.

I have two forms that are used for booking annual leave, one form is for staff to request annual leave, and the other form is for the manager to accept or decline the request.

Both forms are linked to one Google sheet, once a leave request is submitted it populates the sheet.

The manager than accepts a declines the request and that populates the form for response from the manager, the email address of the staff member can be read from the leave application without any issue, with that email address would like to be able to send a copy of the accept/decline response form to the staff member notifying them of the decision.

I can send a email with the details pulled from the Google sheet, but would be better if could send a copy of the form, possibly in a PDF format.

I've seen a plug in called Email notifications for Google forms, however from what I can see it only allows notification to be sent to the manager, not the staff member.

I'm restricted to what add ons we can use, is there a method within app script to do this?

Thanks in advance.

[Brett Grear]

Checkout EduScripts' Special Leave Request Automator.  It might be able to be adapted for your needs.  https://www.eduscripts.uk/solutions/web-app-version-leave-request-automator

[Ed Sambuco]

You should be able to do this through basic App Scripts:

1.  I assume the sheet is tied to the form as a response output.  When the submitter makes a leave request (adds a row in the sheet?), a script is triggered that prepopulates a manager form, and then emails the manager notification along with a link to that response form.

2.  The manager then opens the response approval form and fills it out.  When that form is submitted, a second script runs that directly opens the sheet from step 1 above, updates the sheet and sends an email back to the requestor.

Two forms with two attached scripts and one sheet.  Should be doable.

--
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/6fbd4043-79a6-4017-9f33-66f6fc1e45f8n%40googlegroups.com.

[Paul Nicholson]

Thanks Brett, I'll have a look into that one

[Paul Nicholson]

Hi Ed,

You are almost right in how this is working.

Both the request form and the manager response form are linked to the same spreadsheet.

Once a request is submitted the manager gets an email notifying him of the request.

The spreadsheet then gets populated with the details of the request.

To authorise or decline the request there's a drop down for accept or decline on each row of the sheet.

At this stage the script runs to populate the response form, this saves the manager having to enter the details manually.

As the script accesses the details of the request to email the team member (and the script does that), but thought if could just send the form directly rather than using code to create the email.

If that isn't possible then will look into creating an HTML template rather than plain text.

Maybe I'm looking to deeply into this and trying to reinvent the wheel so to speak!

[Ed Sambuco]

Hmm at the end of your process, you still have to notify the approver as to where the prefilled approval form is.  You have to send out a link somehow ... so I don't see how you can avoid sending the approver an email.

Actually, your process has too many steps.  It is not necessary to have the approver go anywhere near the spreadsheet.  He/she should approve directly on the approver response form.  Sto process could be:

1. The requestor fills out the request form.  When this form is submitted, a script is triggered that adds the request data to the spreadsheet, prefills the corresponding approval form, and sends an email to the approver with a link to the prefilled approval form.

2.  The approver opens the email, and clicks on the approval form link.  He/she accepts or rejects the request in that form, and adds supporting information.  When the approver submits the approval form, the spreadsheet data is updated and an email notice is sent to the requestor.

The approver never directly touches the common spreadsheet.

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.

--
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/b22606a7-3bbc-4d60-b832-bab98f4868d9n%40googlegroups.com.

[Paul Nicholson]

Hi Ed,

Thanks for that, I'll give that approach a try.

It does make a lot more sense to send the prefilled link to the manager, two managers can approve or decline the request, but I'm sure I can sort that out so when it's approved both managers get the notification that it has already been approved.

𝓟𝓪𝓾𝓵 System Controller, Doncaster 2. Email: paul_ni...@next.co.uk Tel:01977 327527

You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/TR12MfpYs8U/unsubscribe.
To unsubscribe from this group and all its topics, 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/CAANZsGPaWBXL%3D6dwGEec_9SgQRURspf3kMB7caHTA5wFdkqQfQ%40mail.gmail.com.

[Paul Nicholson]

Hi Ed,

I have followed your suggestions and it is working as expected now.

The only stumbling block I have come across is that when the request is accepted and the form is submitted the Google calendar for the holidays is updated with the request, however if a request is cancelled, then the manager would delete the response to the form, but when that is done, the entry on the calendar would also be removed, I have the script written to do this, however from what I understand a script can only be triggered with a form submit or form open, what I would need is to run the script to remove calendar entries when a response is deleted.

I'm thinking of investigating creating a menu on the form somehow, and use that to run the script to delete calendar entries. I see know other real option to do it.

𝓟𝓪𝓾𝓵 System Controller, Doncaster 2. Email: paul_ni...@next.co.uk Tel:01977 327527