G Suite – Paid editions, Gmail, Google Sheets, Google Docs, Google Slides, Google Drive
Recently, I had updated all the course materials in a learning management system (Not Google Classroom. Sorry Google) to only use Google Workspace files like Docs, Sheets and Slides for students to access.
For me, this was a pretty logical step. It allowed course creators to update their files live when they had to correct errors or make minor changes quarter-to-quarter or year-to-year. Administratively, it meant that files did not have to be accounted for, deleted and updated every time a change was made.
Table of Contents
While time-consuming, the changeover went well as we changed all of the student resources into Google files. We set all the files to be accessible to anyone with the link can view (The organization does not have student accounts on the same domain as the staff).
However, in the back of my mind, there was a problem I knew I needed to address. The dreaded request for access to edit. With over 3,500 students on the program and hundreds of files for them to access, it would be a huge pain if some of these students clicked that view and requested edit access.
There is no way to prevent users from not being able to do this within the document’s share options. Likewise, my organisation may want to allow requests inside the domain (for example email@example.com is okay, but firstname.lastname@example.org is not).
Ever wanted a quick way to give edit permissions to a user and then send them an email based on values in your Google Sheet?
This task crosses my table several times a week. Whether it is sending reminders to complete a Google Sheets task or share a Google Sheet to complete a task.
You might be thinking that it’s pretty easy to just go up to the big green Share button on Google Sheets and plug in all the people you need to give edit permission too. But what if you need to give edit permission to a number of people that are on your Google Sheet. Ever noticed that the Share button gets in the way when you are looking through your Sheet for those names or emails?
When you normally share a Google Sheet you have the option to send a message, but what if you want to send a custom message with the user’s name? You are kinda stuck to a brief message which sucks.
So I decided that it would be best to create a template script that I could quickly adapt to suit my need in different circumstances. Below is the result.
Basically, this little piece of Google Apps Script allows you to plug in a custom:
Columns you want to use to create a unique email for each person you want to send your message to.
Email sent checkbox location
An Image (I use a button) that the user can click on to send the email and give edit permission to the Spreadsheet.
The script will then get all the emails and their associated columns you want to use to personalize your email. Remove the duplicates and then give permission to edit the current Google Spreadsheet and send them an email.
If you are in a hurry to copy and paste the code into your own project the script is below with a short Quick Start Guide.
If you are looking to learn how it all works, I will go into the details after the Quick Start Guide.
Gmail, cPanel, web host like GreenGeeks or HostGator
One of the most frustrating things about using a domain email like email@example.com is that every time you want to check your email you need to log into your websites cPanel, navigate to the Email section, click ‘Email Accounts’ find the Access a Webmail provider like Roundcube and then wade through the clumsy interface to read and answer your website emails.
Maybe you found that email forwarding link in the cPanel and forwarded your emails to your Gmail account on your own. Nice one! Have you ever replied with your personal email by mistake? Or do you dread having to go to back into cPanel to reply?
What if I told you that I could make this sick, chore-ladened nightmare go away? What if I could tell you that you can receive and send your website emails from the womblike comfort of Gmail?
OMG, Yagi! Tell me more you sweet goat!
…um… okay, a bit creepy but sure.
Setup is pretty easy, but if you search for it on the interwebs, the instructions even from Google are a little disjointed and incomplete.
This tutorial is a two-parter:
Setup my website email forwarding from my web host’s cPanel to my personal Gmail account.
Setup the ability to send or reply to emails using your website email from your personal Gmail account. Teleport to this bit!