Using Google Sheets and App Scripts to schedule staying-in-touch emails

I don’t think anyone needs someone else to explain that things are a bit weird right now. We can’t visit family, we can’t go to sports clubs, and there are colleagues we might not have contact with at the moment because of furlough.

I’ve been worried I might lose touch with some people or, more importantly, that some people could find themselves going days without really speaking to colleagues or friends. Things like House Party or even group hangouts are great for spending time as a group but there are problems.

Group chats can feel very impersonal, they can be dominated by the most talkative, and they don’t really leave much space for meaningful for small, meaningful, chats.

The problem is – it’s easy to forget to spend time with people so I made something to help using Google Sheets and Google App Scripts.

The staying in touch sheet

When you’ve got my “staying in touch” sheet set up (which should take about ten minutes) you, or your colleagues or friends or family can just write their emails in the Email Address column, and set how often they want to be paired up. Then the script I wrote will automatically send an email (from you) to pairs of people suggesting a catch-up.

It doesn’t take care of actually organising the catch up (everyone’s weeks will be different, nobody wants a flood of calendar invite)

List the emails of people who want to stay in touch and how often they should be paired.
You’ll get emails like this

What exactly does the script do?

Here’s a copy of the script. All it does is;

  • Goes to the Email list tab – gets a list of all the non-blank rows
  • If it’s a weekend, only keeps the emails that want to be included on the weekend
  • If it’s a weekday, only keeps the emails that want to be included on a weekday
  • Shuffles the list of emails and splits it in half, pairing up each item in the two lists
  • For each pair it sends them an email suggesting they catch up
  • If anything goes wrong, it emails the manager list you specified in the Manager Details tab.

How do I set it up?

Make a copy of the staying in touch sheet

You’ll need your own copy because this all runs through your Google account.

Add yourself as a manager

Click the “Manager details” tab and put your email address in there.

This list is the list of email addresses that will get notified if something goes wrong. So, for example, if someone puts in their email wrong and is being left out because of it – you’ll know.

Check the script in the App Script editor

To be clear – this setup will work if you just follow these instructions. However – I really recommend that whenever you’re running scripts you take the time to have a bit of a look at what they are doing. Partially because you might pick something up, partially for your own peace of mind.

At the bottom of this post I’ve written out what the script is doing but you can also see it for yourself now.

Click on Tools -> Script editor that’ll take you to a screen with all the scripts.

Click on Tools -> Script editor

The screen with your script

How to authorise your App Script

Once you’re happy with the script, click the dropdown at the top of the page, select “sendEmails” and then hit the black, “play” style button. You’ll only need to do this once, we’ll be scheduling it soon.

The play button runs the script but before it does anything, Google wants to make sure you’re happy with what it’s doing. Google doesn’t want things doing things you don’t know about so you need to authorise the script. That’s pretty standard.

When you click the button a box like the below will appear. Then you’ll be taken through the authorisation process.

You’re only giving access to this script in this sheet specifically and it will only ever do two things;

  1. Take the list of email addresses you put in this sheet
  2. Email those people.

It won’t send anything to anyone you haven’t specified but, like I said, make sure you’re happy with it before you authorise.

When you click through you might also get a message saying this hasn’t been approved by Google. That’s because it hasn’t – I did it on a Monday evening, who has time for that? If you do get that message, click “Advanced” and then “continue to sendEmails”

Once you click that link the script will be authorised.

How to schedule your script

Go back to the app script editor ( the tab should still be open from before), the tab will look something like this;

In the app script screen, click the thing that looks like a speech bubble with a clock inside it – that is the button to schedule your script.

The app script scheduling screen

You’ll be taken through to a screen which looks like this, Google refers to “Triggers” as things that automatically make your script run.

Click “Add Trigger” and you’ll get a screen like the below. You want to schedule the sendEmails function for a given time every day. You can just copy the screenshot below, once it’s set up scroll down and click “Save”.

You’re done!

Now your script should run once every day (don’t worry, it’ll only contact people daily if they ask for it).

If you try it, I’d love to hear what you think – drop me a comment below 🙂

Github gist with the Google App Script code

Leave a Reply

Your email address will not be published. Required fields are marked *