- This topic has 44 replies, 2 voices, and was last updated 6 years, 11 months ago by
.
-
Posts
-
-
[Moderator insert: this is a more advanced topic which deals with using a Google form in conjunction with an add-on called ‘Autocrat’ to create a mail merge response system. During the discussion we tested a number of ways to achieve automatic notifications for the Form that staff complete and some of these didn’t work, so we’ve marked that reply to indicate this for you. Please note this is an advanced topic and we also provides some one on one support for the member in this instance.]
Hi Rob,
As you know we have set up a leave form via google docs, together with utilising Autocrat to create a spreadsheet of the responses on the leave form. This then acts as a mail merge to send a response to the staff member to notify them if their leave has been accepted or declined.
Can you please tell me how to change my form/spreadsheet that we have set up in google docs so that the Principal and team leaders can be notified that there is a leave form to action on the spreadsheet. Thanks so much.
-
Hi Vicki,
Great question and totally doable. Here is an overview of the steps:
Firstly, this requires you altering the Form so that you capture the Team Leader email address, that way you can specify the Team Leader email as a merge field and then that person can also be emailed the request (thus notifying him/her).- Next you need to create a field for the staff member to select their team leader. I would use a Drop Down field and put the email of each Team Leader in this so there can be no mistake with emails being mis-typed.
- You could chance the existing field for ‘Team’ to ‘Team Leader’ if you wanted as that would identify the team they are in anyway… but you may want long term data by team (eg. h
ow much leave does X team have each term/year?)… so perhaps create a completely new field. - Once you create the new field in the Form, you’ll notice that the Sheet has a new column which corresponds to this.
- Once the field is created, you next need to go into the Autocrat Add-on (Add-ons >
Autocrat > Open…) and edit it by clicking the pencil tool… - Click through the steps until you get to step 8: ‘Share docs & send emails‘.
- This is where you match the column headings in your Sheet to the Autocrat Add-on.
- Add the merge field (which is now in your Sheet) for <<Team Leader>> in as a ‘cc’ or ‘bcc’ email depending on your preference.
- You may also opt to put your Principal’s email in the ‘cc’ field too. It is always better to use principal@… as that is the enduring email address for that role.
A bit about notifications…
There are a number of ways to be notified of new submissions.
- This technique creates a merged PDF or shared document to send to these people you specify.
- You can delete the email ‘office@…’ if you wish and turn on notifications from within the Sheet itself. This gives an anonymous notification that ‘someone filled in your Form in your email – it may be better to keep your email in the ‘cc’ field so you’ll see exactly who you are dealing with when you are monitoring leave requests.
- If you opt to be notified that ‘someone filled in the form’ then in your Sheet go to: Tools > Notification Rules…
Let me know how you get on, you’ve done really well with this as your first Form, especially given this is a more complex use case Vicki!
Reference (if you really want to knock yourself out): Autocrat User Guide
-
Thanks Rob for your help with this. I have done all of those instructions above. If I do add the <<Team Leader email>> in the cc section of the Step 8 in Auto Crat and also the principal email address as a ‘cc’. Won’t this then send a copy of the confirmation letter to both those people as well as the person filling out the leave form?
If that is the case – what I wanted was just a notification to the team leader and principal that there is a leave request to be approved on the spreadsheet – rather than them receiving the actual confirmation letter?
Or am I totally incorrect here? I can also look on the notification rules under tools to check this out.
Thanks so much.
Vicki Harrington
-
Oh whoopsie, yes you are totally correct. My mistake – here is what I meant to say…
There are a couple of ways you can do a notification using a Google Form or a Google Sheet:
- You can use the option built into the Sheet when someone submits, as in step 3 of the ‘notifications’ section in the previous post – I think this would be useful for you only as you monitor all the requests (if you want extra emails that is).
- There are a number of Add-ons for either Sheets or Forms which deal with notifications. Some options below for you…
Form Notifications… option 1 which sends a notification to the respondent and/or you
We have curated a number of useful Add-ons such as this one in our Resources > Useful Add-ons section of this site.
- Go into your Form and click the three dots (1) top right then select ‘Add-ons‘… (2)
- From here you’ll be presented with a ‘candy box’ which is the Add-on store… you can spend hours in here!
- Once you’ve connected an Add-on to your Form, you’ll see the little jigsaw piece (3) at the left of this list of items on your Form. This allows you to manage the Add-ons you’ve got connected to the Form.
- You have a couple of options. The easiest is the Add-on called ‘Form Notifications‘ which might be the quickest way to deal with this. It allows you to select who you are sending a notification to. In this case you’d choose the Team Leader email address.
There is an Add-on called Form Notifications Extended which gives further features similar to those identified below.
Form Notifications… option 2 which is similar to the above based on conditions/rules you specify
Another Add-on with more power is the Add-on ‘Email Notifications for Forms‘ which will give you more flexibility. This Add-ons free features may do what you want as I’ve tested it.
The difference between this Add-on and the other one (apart from this one having a paid version) is that it uses rules which are a bit like Email Filters. Below is a 9 min video tutorial:
Additional options worth exploring…
I’ve come across a number of other Add-ons that will achieve the same goal. Some are more complex, so I will test them later.
- Simply Send – this Add-on allows you to also send an email notification based on another field in the form (ie. Team Leader).
- Form Confirmation Emails – this is the simpler option which is quite good, if you know HTML if allows you to format your email responses.
- Confirmation and Thank You Mailer – this will also achieve the same result and is quite simple to use.
- Form Workflow – this Add-on allows you to specify workflows from form submissions and is likely more complex. It is an Add-on for Google Sheets which I think this is worth exploring so it may warrant a separate post in itself. I’ve not tested this one yet.
I wonder if this topic would be worthy of a short course to help people?
Let me know how you go…
Rob
-
Hi Rob,
Thanks for your help with this. Have used the Confirmation and Thank you Mailer Add-on – as I could not get the other Form Notifications to work with the Team Leader email address. I cc’d the Principal email into the option – and it has not worked. Paul did not receive the email. Am waiting to hear if Meg (Team Leader) received the email notifying her of the need to approve the leave. Will keep you posted. If she does not receive it – back to the drawing board.
Appreciate all your assistance with setting up this system in our school. It is fantastic having you to help.
Vicki -
Hi Vicki,
Let’s see… it might be that this mailer does its thing after a period of time. Given this possibility, I’d be interested to see if Meg does get an email, as her email is a merge field, whereas the [email protected] email is typed manually in.
With regards to the principal getting the email – a couple of considerations:
- the principal@… email may need the {squiggly brackets} around it?
- could you check Paul’s spam filter? It might have gone in there (scroll down the list of labels on the left of the email inbox and click on Spam) perhaps?
We’ll see, only time will tell at this stage.
-
-
-
Hi Rob,
If a person completes a leave form and they don’t need a reliever at all (like a support staff member) would this muck it up for sending the approval letter to them if we don’t fill in that cell in the spreadsheet where it says reliever (and we click on confirm)? In the orange section.
I have had two forms done now via the new system and one will not send the confirmation letter to the staff member. She does not need a reliever.
Help!!! 🙂 When you have time.
-
Hi Vicki,
The confirmation letter run through autocrat should be working fine – can we check this via a Google Hangout screen share? That way I can check whether you’ve done the merge for those people before and if so, if you’ve deleted the Autocrat data at the far right of the Sheet prior to trying to merge again.
With regards to a person not needing a reliever…
This is a case of ensuring that the merge template wording accounts for this. For example, if it says: “Your reliever is <<merge field reliever name>>” then that will result in:
“Your reliever is” if there is nothing put in the Form.
So, in the Merge Template (Google Doc) I would put a heading in bold above this saying Reliever Details (if required): to account for this.
-
-
Hi Vicki,
The notifications aspect is proving a bit of a research project. I’ve tried a couple and found Simply Send which says it will do what we want, ie. send an email notification on form submission to an email from a merge field (ie. Team Leader).
I’ve just requested leave with Megan as my Team Leader. Would you see if she got the email please and come back to me?Rob 😉
-
-
-
Hi Rob,
How are you? Hope you had a lovely weekend.
The letters of confirmation for leave are coming through on email as if they are from you, rather than from me.Do you know how I can change this going forward.
Also – not sure if the notifications worked in terms of the form telling the Principal or Team leader that there is a leave form to approve or otherwise. They still don’t seem to be going through. Did you try adding something different last week – another add-on???
Kind regards,
Vicki
-
Hi Vicki,
I think what is happening is that because we’ve both been testing different add-ons (primarily me with new ones) that the last person to save one that works was me, therefore it will automate from me.
The other aspect relates to how Autocrat works; the person who installed it (or edited/saved it) last is likely to be the person that the merges come from.
The solution here is for you to go through the autocrat Add-on and ‘save’ it, thus saving over me. To be sure then disconnect me from the Sheet so that it isn’t shared with me (blue Share button top right inside Sheet).I suggest testing it again with someone to ensure the merge comes from you, not me. Feel free to use my email to test it so I’ll know you are giving it a go.
I’ll check in tomorrow with you to fix this.
-
Hi Rob,
Thanks for the call. I have made a small change in the email part of the add on in Auto-crat and resaved it and also deleted you from the sharing of the spreadsheet.I will also check with Megan to see if she received any emails at all to let her know there was a leave form to approve. Will come back to you on that one.
Also I will ask Tina to run a test leave form for me.
Cheers
Vicki -
Hi Rob,
Further to our emails re the leave forms. For some reason I am no longer receiving the pdf of the mail merge letter that staff receive. Not sure why not. I have checked the add on via autocrat and my email address is still in the CC part of that section. Also – I have not received a confirmation letter for my own leave which was approved yesterday.
On a good note – Megan received the notifications finally that there is leave to approve or decline. So that add on seems to be working. When you have a moment – do you mind helping me. I am away tomorrow but back on Monday.
Thanks heaps.
Vicki Harrington – Elmwood -
-
Hi Vicki,
I’m sorry to hear this – the solution is to step through the autocrat set up again, making sure you save it once you get through the steps.
Plus, if you do the same for the Simply Send Add-on then it should work for you. If this doesn’t solve it, then let’s jump on a hangout to resolve it together – I will be available most of the day tomorrow to help you sort it.
Rob 😉
-
Thanks Rob, I had already stepped through the autocrat set up the other day, but have redone again now and saved it again. As far as I can tell – it should only be this. As the simply send add on which you added for me to the form – to notify the team leaders seems to be working, I shouldn’t need to touch that should I? If I was to reload it – I think I need a bit of help with that. 🙂
Vicki
-
-
If I was to add Paul’s email address in the simply send add on – in the recipients email above the team leaders box – do you think this would work – so to notify Paul as well for the approval of leave on the spreadsheet.Thanks heaps. Um… it seems that once we took you off – it just won’t send the automated mail merged letter to the staff. I know it will get sorted. Will look at your reply in the morning.
Kind regards
Vicki -
Hi Vicki,
I am not sure if that Add-on will allow you to do what you ask:
…add Paul’s email address in the simply send add on – in the recipients email above the team leaders box…
…mainly because it appears to be an ‘either-or’ situation. That said, the only way to find out is to test it. This is one of the challenges with using Add-ons, especially when using them for more complicated tasks such as this.As far as getting this pesky merge to work, I agree, perhaps it was a mistake to un-share it with me! Would you re-share it please?
I’m wondering if we are missing a step in the Autocrat set up? The step I’m wondering about is the ‘run on form submit’ option. I’ll have a look at this once you share the document back to me.
You are getting there!
Rob 😉
-
Hi Rob,
I had a look at the Autocrat help page today. I found this. 5. In some cases, a trigger set by one user cannot be removed so that a second user can become the “trigger owner”. Maybe this is why this has happened.
Perhaps I need to go through the whole thing all over again and do all of it myself. (Although I did think that we had done that since the problem arose – but maybe I only made slight changes and re-saved).
Cheers
Vicki
-
Hi Vicki,
Well done, I was wondering about that and hadn’t read the documentation as carefully as you have. I think you are right. It is a case of probably deleting the merge job, then re-creating it from scratch. You’ll be an expert at this!
When I come in to see Paul I am happy to watch how you do this if you need me to.
Rob
-
-
Hi Vicki,
Okay, I’m wondering if when we first set it up, if I was the user who installed the Autocrat Add-on onto the Google Sheet? I think this might have a bearing on this. Given this, this leads me to wonder if we logged in as you, deleted the Add-on from the Sheet completely, then re-added it in and set the merge again if the sent from email would then be you.
In checking the Autocrat FAQs, it appears that the person who creates the trigger is the one that the email will come from… see reference here.
I’m wondering if you emailed me your username and password and for me to have a look through this for you? We’ll get it!
-
Hi Rob,
Oh – ok – yesterday we made a new add on and deleted the other one we had. Is that not the same thing. I thought I added it – as the first tests we did were working and the mail merge letter was coming from me for the first few trials. When it all turned to custard was after we were fiddling round doing the add on – onto the actual form that gets filled out. After that the mail merge somehow changed to come from you.
Yes – saw that about the trigger.
On another note – when I look at the dates in the spreadsheet – that the staff are taking their leave for – they show up the wrong way around – american way – and they print out as that on the mail merged letter. Don’t think it used to be like that either.
So…. things we need to fix:
Dates – to be NZ with day then month then year
Trigger – so mail merge comes from me.
When the mail merge prints out – it now says your reliever is ‘approved’ rather than ‘confirmed’. I am sure we had that before – and it came out saying ‘confirmed’. It got that from column D in the spreadsheet – once I put confirmed in there. Not sure why this has changed to ‘approved’.
Thanks for your help Rob.
Vicki
-
Hi Vicki,
Oh – ok – yesterday we made a new add on and deleted the other one we had. Is that not the same thing. I thought I added it – as the first tests we did were working and the mail merge letter was coming from me for the first few trials.
No, what I meant is to actually delete the entire Add-on (rather than the merge job itself). I’ll try this and come back to you with what I find out.On another note – when I look at the dates in the spreadsheet – that the staff are taking their leave for – they show up the wrong way around – american way – and they print out as that on the mail merged letter. Don’t think it used to be like that either.
You’ll need to highlight the entire date column and go to Format > Number and select the option you want.
These sorts of things are covered in the course Google Sheets & Forms for the Office 1 😉
Have a good weekend,
Rob
-
Hi Vicki,
The merge is working now. Here is what I found out when checking through it…
- When checking through the merge settings, the field that was mapped to the tag <<confirmed>> was actually meant to be <<Reliever Confirmed>>… one of us must have changed it.
- This had caused the Autocrat script to stop working:
- So, what I did was to delete the merge job, then disconnect and reconnect the Autocrat Add-on (I probably didn’t need to do this step but just to be sure).
- I then changed the merge tag in the Google Doc template, and re-created the merge job, ensuring the fields were correctly mapped.
- Voila! You’ll see in this image from my phone that my test worked, it’s coming from [email protected]
Now we can sit back and rest! 😉
- When checking through the merge settings, the field that was mapped to the tag <<confirmed>> was actually meant to be <<Reliever Confirmed>>… one of us must have changed it.
-
-
Hi Rob,
Just when we thought it was all sorted. Megan (Team Leader) is receiving two emails to notify her that she needs to approve leave. One is from you and one is from me. Maybe I will just tell her to delete the one from you!!! 🙂 Actually that is not the answer – I will try and look into it and find out why the system is generating two.
Keeping you in the loop.
Vicki
-
Hi Vicki,
Hmmm… strange. I’m glad we’ve only go two Add-ons working here!
I’ve reset the Simple Send Add-on… the risk in me doing this is that it has turned it off for you as well as me. So if you’d now please go into the Add-ons area (the jigsaw piece on the left of the icons top right of the Form screen) and check it.
If I’ve deleted your settings then go in a place them in again, save and it should be fixed.
Another thing to check would be that the Sheet doesn’t have notifications set (Tools > Notification rules) under Megan’s log in… though I doubt this is the case from what you describe.
Let me know how you go.
Rob
-
Hi Rob,
I checked that the sheet doesn’t have notifications set under Megan’s log in…. so that is all good… however. I just tried to get the sheet to merge and produce confirmation letters – and it didn’t do anything – so I checked the Autocrat and for some reason it says No merged jobs set up – click to get started on a new job.
I don’t understand how our add-on would just disappear like that …. so annoying. It has not been touched. I will have to set it up again.
In setting up the new add on I am a wee bit stuck just on one bit. The file settings file name – I know you click on the blue tab at the left – but do you chose a Merged doc URL or Merge Doc ID? Do you know? Hope you can answer this. Thanks heaps.
Vicki
-
O that is a real pain, sorry to hear this Vicki.
Surely it’s not possible that any of the other collaborators who have access may have ‘had a look’ and deleted the merge job? (I doubt this would happen.)
In any case, you don’t need to choose either of the Merged doc URL or Merge Doc ID. All you’d do is create the file with a naming format that makes sense, for example:
Leave Request – <<First Name>> <<Last Name>> – <<Timestamp>>
That will create what you need if I’m not mistaken 😉
If this occurs again, then I think the best approach is to contact the Autocrat help Google+ community here… it appears that this is an issue that occurs occasionally for some users, from what I read in there, there are solutions – but first let’s see if it persists.
-
-
-
-
Hi Rob,
I just tried to run a manual merge sequence of the leave spreadsheet through the autocrat add on. As it did not run an automatic one with the leave that has Yes beside it.
It has not worked. Trying not to get frustrated and persevere with this add on….
Let me know your thoughts when you have a momento. 🙂 Thank you.
Kind regards
VickiHi Vicki,
I’ve gone through the set up and found two errors in the settings – only one of which would cause the merge to not work at all.
The first issue I spotted was at Step 4 – File Settings:
Suggest changing this to PDF rather than a Google Doc which is editable. This isn’t a critical error 😉The second issue was at Step 7 – Merge Conditions:
This is the little ‘beastie’ that caused your merge to stop working. I changed the merge condition field to ‘Merge’ (the name of the field you made for whether or not it is ready to send out to the staff member, and it worked.I ran the merge and it merged all those who had ‘Yes’ in the field Merge.
Let me know how you go with these changed settings Vicki,
Rob
PS remember if you want to ‘cc’ yourself, this is the step relating to send emails, which is Step 8: share and send emails.
-
Hi Rob,
I have set up Step 8 in Autocrat. I have taken two screen shots of what I have done. I have attached the two screen shots of step 8 – just to ensure I have it right. I think I do. do you mind having a quick look at the screen shots and letting me know. In the meantime I will save it and hope for the best. I am not sure how to get the screen shots to sit in this box so you can see it – as you have done with previous posts.
Thanks Rob.
Vicki
-
Hi Vicki,
This looks all good to me, well done 😉
With regards to inserting an image into a forum post, I have actually made a short tutorial on that which lives inside Help > Using the Site – here is the video for you:
-
Hi Rob,
When staff are finished completing their leave form and they submit it – once submitted it brings up an option to ‘see previous responses’.
And when you click on see previous responses – it brings everyones leave up for the person applying for leave to see. How can I delete that. Not such a good thing to have on there .
Thanks so much.
Vicki
-
-
-
hmmm… I’m sorry this is not working Vicki,
I suspect that there is a checkbox ticked in your Settings. Go to the cog top right and check in the General Settings area…
- You must be logged in to reply to this topic.