The problem: Our high school has two campuses. They are separated by a football field, so they are not far from each other, but they do have separate offices and separate intercoms. We read announcements over the intercom right at the start of first period. The problem is that one office got announcements from teachers via paper forms while the other didn't always get them. The question was how do we implement an easy process to get all students announcements and ensure they get to all the school officials who need them?
The solution: A google form, a google doc and about 50 lines of code using the Google Apps Script editor.
Step 1 - Develop your google form.
Our google form was relatively simple. We needed only a few pieces of information:
- who wants the announcement made
- his/her email address
- the subject of the announcement
- the dates he/she wanted the announcement made
- the announcement message
- does the announcement need to be made at the middle school
Step 2 - Develop your announcement form.
The next step was to develop the finished product. We wanted something that would be uniform (easier to read multiple ones). We had a paper version that was similar (and a Word doc that was also similar, but people messed up the formatting). The paper version became the template for the new digital announcement.
You can change the look of your announcement form as you want. This is just a regular Google Doc. Format the text that will be replaced and the new text will have the same formatting.
Step 3 - Develop your GAS.
This is the fun (and frustrating) part. I can't tell you how much time it can take to do some of the simplest stuff using the script editor. But it is also rewarding when you get it to finally work.
Your google form has an associated spreadsheet for the responses. You can get there by clicking the View Responses button on the form editor.
Below is the entirety of my code (although I have taken out email addresses). I will highlight in red and talk about each section. Highlighted in the entire code are the things you will need to change to fit your needs if you want to copy and paste. Please feel free to copy and paste.
var docName = "Announcement";
function onFormSubmit(e) {
var sponsor = e.values[1];
var email = e.values[2];
var subject = e.values[3];
var startDate = e.values[4];
var endDate = e.values[5];
var announcement = e.values[6];
var fifthStreet = e.values[7];
var copyId = DocsList.getFileById(docTemplate)
.makeCopy(docName +' from '+ sponsor)
.getId();
var copyBody = DocumentApp.openById(copyId);
copyBody.replaceText("keySponsor", sponsor);
copyBody.replaceText("keySubject", subject);
copyBody.replaceText("keyStartDate", startDate);
copyBody.replaceText("keyEndDate", endDate);
copyBody.replaceText("keyAnnouncement", announcement);
copyBody.saveAndClose();
//Send to Fifth Street if needed.
var ccEmail;
if (fifthStreet == 'Yes') {
ccEmail = '[email protected], [email protected], [email protected]'
}
else {
ccEmail = '[email protected], [email protected]'
}
var file = DriveApp.getFileById(copyId);
var subject = docName+' starting '+ startDate;
var message = 'Please make this announcement from ' + startDate + ' to ' + endDate + '. \n\n'+announcement;
MailApp.sendEmail(email, subject, message, {
name: 'WPHS Announcement',
attachments:[file.getAs(MimeType.PDF)],
cc: ccEmail
});
DocsList.getFileById(copyId).setTrashed(true);
}
https://docs.google.com/document/d/ and /edit.
var docTemplate = "1dP2iJX_NotLFIE94YYQmlzU332EK1OSMi8uistkH9W8";
var docName = "Announcement";
GAS uses a tweak on javascript and javascript uses functions and variables. The function for this script is called onFormSubmit and it has an input e, which is the form itself. When a user submits the form, the information they put in the form goes in to this function. The matching } is at the very end of the code.
function onFormSubmit(e) {
The form has 7 fields and each of these is assigned to a javascript variable. e.values[1] refers to the first field in the form. All of these variables will be used to replace the text in the Google Doc template except for the fifthstreet and email variables. They will be used later.
var sponsor = e.values[1];
var email = e.values[2];
var subject = e.values[3];
var startDate = e.values[4];
var endDate = e.values[5];
var announcement = e.values[6];
var fifthStreet = e.values[7];
The following script grabs the Google Doc template, makes a copy of it and replaces the text with the variables which were assigned the values from the submitted form. .makeCopy(docName +' from '+ sponsor) also renames the copy "Announcement from [person making announcement]." The + is used to combine a variable like docName with a string like ' from ' in to one string. docName was named at the very beginning of the GAS. When the text is replaced the Doc is saved and closed.
var copyId = DocsList.getFileById(docTemplate)
.makeCopy(docName +' from '+ sponsor)
.getId();
var copyBody = DocumentApp.openById(copyId);
copyBody.replaceText("keySponsor", sponsor);
copyBody.replaceText("keySubject", subject);
copyBody.replaceText("keyStartDate", startDate);
copyBody.replaceText("keyEndDate", endDate);
copyBody.replaceText("keyAnnouncement", announcement);
copyBody.saveAndClose();
This is where we use the fifthstreet variable. If the user selected yes, then the announcement is also sent to the middle school principals and secretary. If the user selected nothing or no, then the announcement is only sent to staff at the high school. This also creates a new variable ccEmail, a string of email addresses that will be used later.
//Send to Fifth Street if needed.
var ccEmail;
if (fifthStreet == 'Yes') {
ccEmail = '[email protected], [email protected], [email protected]'
}
else {
ccEmail = '[email protected], [email protected]'
}
This is the part of the script that sends the announcement via email. The announcement is attached to the email and the message of the announcement is also placed in the text of the email for those of us who read them on our phones. The name appearing as the sender has been changed to WPHS Announcement. The subject line is "Announcement starting [start date]." The email addresses created in the code directly above are added to the cc line of the email. The email is also sent to the user (remember the email variable from earlier?).
var file = DriveApp.getFileById(copyId);
var subject = docName+' starting '+ startDate;
var message = 'Please make this announcement from ' + startDate + ' to ' + endDate + '. \n\n'+announcement;
MailApp.sendEmail(email, subject, message, {
name: 'WPHS Announcement',
attachments:[file.getAs(MimeType.PDF)],
cc: ccEmail
});
This is the last of the code. It trashes the copy of the announcement, you don't need it once it has been emailed to you (also it is stored in the spreadsheet associated with your google form), and has the accompanying } for the beginning of the function onFormSubmit.
DocsList.getFileById(copyId).setTrashed(true);
}
Save your code. Click the Resources drop-down menu and select Current project's triggers. You will need to Add a new trigger and change the settings to the ones below.
Click Save, authorize the GAS and you should be good to go. Have fun and let me know if you have any problems.
Cheers!
*Note on emailing lots of people: The other day I hit my limit with my Gmail account. I have since found out it is 500 different recipients per day. If each time you send out an announcement it sends to 9 people that means you can only have 55 announcements per day and can send no more emails. I suggest a Gmail account dedicated wholly to this form.
**Second Note: The limit has been changed again. It is now 100 per day. See my post about this here.