Google Apps Script: Send Bulk Emails and Share a Google Sheet with One Button Click

Google Sheets, Google Apps Script, GmailApp

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:

  1. Email column.
  2. Columns you want to use to create a unique email for each person you want to send your message to.
  3. Email sent checkbox location
  4. An Image (I use a button) that the user can click on to send the email and give edit permission to the Spreadsheet.

Google Sheet necessary items for GAS email and share template

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.

Continue reading “Google Apps Script: Send Bulk Emails and Share a Google Sheet with One Button Click”

Google Sheets: Conditional Formatting with Custom Formula

Feature inner image credit: Samuel King Jr. 

Google Sheets – Conditional Formatting

Conditional formatting in Google Sheets is a powerful and useful tool to change fonts and backgrounds based on certain rules.

This tutorial assumes that you already have a basic knowledge of Conditional Formatting but would like to uncover the mysteries of the Custom Formula option.

In this post, I will guide you through the steps to build your own custom formulas in oder to:

  1. Apply Conditional Formatting across a whole range based on a value in one or two columns.
    1. Example 1: Conditional Formatting a Whole Range Based on One Column’s Cell Values.
    2. Example 2: Conditional Formatting a Whole Range Based on Selected Values and Formulas.
    3. Example 3: Conditional Formatting a Whole Range Based on Multiple Column Values.
  2. Apply Conditional Formatting across one column based on values in other columns.
    1. Example 4: Conditional Formatting of a single range Based on Another Column Value.
    2. Example 5: Conditional Format a single column range based on a value in another column – Multiple times.
    3. Example 6: Conditional Formatting a Single Column Based on Two Values.

Throughout the examples, we’ll look at various aspects of using Custom Formula to match, use formula functions and apply multiple conditions.

Before we hit the examples, let’s briefly go over accessing the Custom Formula in Google Sheet’s Conditional Formatting.

Continue reading “Google Sheets: Conditional Formatting with Custom Formula”

How do I Forward and send Emails for my Website Email in my Personal Gmail account?

Gmail, cPanel, web host like GreenGeeks or HostGator

One of the most frustrating things about using a domain email like imbatman@yagisanatode.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.

Groan!

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?

ugh!

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:

  1. Setup my website email forwarding from my web host’s cPanel to my personal Gmail account.
  2. Setup the ability to send or reply to emails using your website email from your personal Gmail account. Teleport to this bit!

Let’s get started…

Continue reading “How do I Forward and send Emails for my Website Email in my Personal Gmail account?”

Google Apps Script – When I add a value to a cell in a selected column, I want it to move to another Google Sheet

Google Apps Script: onEdit, Google Sheets

Sometimes you want to be able to automatically move a row from one sheet to another based on the value of a certain cell.

The Example

One of the first things that come to mind, and I am sure it does for you dear reader, is when I took advantage of the Great Chicken Transformation back in, oh, 2019, I believe.

Folk kept turning into chickens, while other folk were wanting eggs. It just so happened that I had the farm to make it all happen.

First, though, I needed to keep a tab of every person I knew and if they turned into a chicken. If they did, then they were destined for the pen.

…Note to self: it may be late at night, but dam Yagi, your analogies are tight!

Google Sheets and Google Apps Script to the rescue.

So first off I set up a sheet named: Plague. Here I put all the people I knew, so I could watch em good and propper.

Next, I set up a sheet named Farm. These are for the people who turned into chickens. No harm in profiting from a few newly formed egg layers, right?

Whenever a new transformation occurs, I find the person on the Plague sheet and then select “Yes”  to say that they have turned into a chicken and will now be spending their days on the farm. Upon editing (onEdit) this cell to “Yes”, the row is copied and pasted to the Farm sheet.

Just like this:

move to another sheet onEdit Google Apps Script

Continue reading “Google Apps Script – When I add a value to a cell in a selected column, I want it to move to another Google Sheet”

How to display a date for one day in a week that automatically changes weekly on Google Sheets

Google Sheets – TEXT, TODAY, WEEKDAY

If you are a busy admin nerd like me and have created a Google Doc or Sheet on the fly to meet your company’s demands for something or another, you probably also have a few Sheets lying around that are not 100% right. That obsessive-compulsive nature in you is niggling the back of your mind saying, “You can do this better!”

But the day-to-day race to get things done takes over and you move on to more pressing matter.

Until the next time you have to look at that Google Sheet and it starts bugging you again. Well, until you have a moment to fix it.

The Problem

For me each week I had a sign-up sheet for a makeup test for students. The coordinators who would add students to the sheet required that in the header rows, the date of the next Makeup Test be added for each week.

The makeup test was always at the same time each week: Monday at 15:30. I would then need to prefix this with the day and month. So it would look something like this:

Monday 28 Jan at 15:30

My original approach then was to open the sheet and change the date manually each week. Okay, I admit that on not just one occasion  I forgot to change the date much to the glee of the most persnickety of the coordinators who could happily call me out on my failure. Grrr.

I knew I could make this more efficient. I knew I could probably automate this process. Fortunately, the day finally came where  I found myself with 15 minute s of free time and this issue in my mind.

This post is the solution to the problem.

The Solution

Continue reading “How to display a date for one day in a week that automatically changes weekly on Google Sheets”