Skip to content

Instantly share code, notes, and snippets.

@Robin-Lord
Created April 5, 2020 12:20
Show Gist options
  • Save Robin-Lord/240d5251836e753fafff918347f2030b to your computer and use it in GitHub Desktop.
Save Robin-Lord/240d5251836e753fafff918347f2030b to your computer and use it in GitHub Desktop.
GSheets app script for pairing people's emails so that they can stay in touch
/**
For a sheet which uses this script, go to
https://docs.google.com/spreadsheets/d/1v06lwTvqo-D1SBzAkRfNkGqxdI8KmcJkCZqbvVmKots/copy
For a blog post explaining this sheet, go to http://www.therobinlord.com/a-google-sheet-to-help-you-stay-in-touch/
*/
/**
* Shuffles list of people to email
*/
function shuffle(array) {
// Getting current list
var currentIndex = array.length, temporaryValue, randomIndex;
// While there remain elements to shuffle...
while (0 !== currentIndex) {
// Pick a remaining element...
randomIndex = Math.floor(Math.random() * currentIndex);
currentIndex -= 1;
// And swap it with the current element.
temporaryValue = array[currentIndex];
array[currentIndex] = array[randomIndex];
array[randomIndex] = temporaryValue;
}
return array;
}
/**
* Collects error messages
*/
function sendErrors(err_msg){
try{
// Get all the listed managers
var managerRows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Manager details').getDataRange().getValues();
// Logging the rows so we can debug if this isn't working
console.log(managerRows)
console.log("Manager emails:")
// Remove top row because those are our headings
managerRows.shift();
for (var i in managerRows){
// Extract each recipient
var recipientRow = managerRows.shift();
var recipient = recipientRow[0]; //First column
console.log(recipient)
// Send an email with the failed email addresses
MailApp.sendEmail(recipient, "Error", err_msg);}
}
catch (err) {
// Logging failures to help debugging
console.log("Getting error message emails failed")
console.log(err)
}}
/**
* Sends emails with data from the current spreadsheet.
*/
function sendEmails() {
console.log("Starting")
// Getting the date
var currentTime = new Date();
// Finding out day index
var day = currentTime.getDay();
try {
//Finding all the active rows in the sheeet
var rows = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Email list').getDataRange().getValues();
console.log(rows)
// Remove top row because those are our headings
rows.shift();
// Some people don't want to be included every day, some
// will only want wekends, weekdays, or intermittent.
// This finds those people and removes them on certain days
if ((day === 6) || (day === 0)){
// Filtering all the rows to those that are weekend or
// every day users
var rows = rows.filter(function (row) {
return (row[1] !== "Weekdays" && row[1]!== "Three working days");
});
}
else {
// Filtering all the rows to those that are weekday or
// every day users
var rows = rows.filter(function (row) {
return row[1] !== "Weekends";
});
if ((day === 1) || (day === 3)){
// Filtering all the rows to those that haven't set
// "only three days a week" as True
var rows = rows.filter(function (row) {
return row[1] !== "Three working days";
});
}
}
console.log("Done filtering");
console.log(rows);
// Shuffling remaining values to randomise pairings
shuffle(rows);
// Getting the half length of our list
var half_length = Math.floor(rows.length / 2);
// Splitting our list into contacters and recipients
var contacters = rows.slice(0, half_length);
var recipients = rows.slice(half_length, rows.length);
// Creating an array to populate with failed emails
var failedEmails = []
// For each of our contacter email addresses
for (var i in contacters){
// List is already randomly shuffled so take first elem
var recipientRow = recipients.shift();
var recipient = recipientRow[0]; //First column
// Get email address
var row = rows[i];
var emailAddress = row[0]; // First column
// Creating email text
var message = 'Hey there! You should both get in touch';
var subject = '[Automated reminder] Catch up';
// If there is an issue with one of the emails just save and continue
try {
MailApp.sendEmail(emailAddress+", "+recipient, subject, message);
console.log(emailAddress, recipient)
}
catch(err) {
failedEmails.push(emailAddress+" ");
failedEmails.push(recipient+" ");
}
}
if(failedEmails.length>0){
// If there were any issues, email sheet manager with details
var errMsg = "The following emails failed: \
".concat(failedEmails)
sendErrors(errMsg);}}
catch (err) {
console.log("Some kind of error, trying to alert sheet manager")
console.log(err)
sendErrors(err);
}}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment