In a leadership group that I am working with we have the policy of rotating who facilitates the meeting and who takes notes. I think this is a great way of running the meeting as it gives us a collective ownership of it rather than the formal leader of the group running it. Practically we did this by having a table with the facilitator and note taker on top of the document with the notes so everyone could see who was up next. One challenge we faced was however that people didn’t check the document ahead of the meeting and because of that forgot to bring their laptops or if one of the two people where missing they forgot to delegate the responsibilities. This lead to frustration within the group as we lost time as people needed to run off to fetch laptops or where unprepared to facilitate the meeting.

After observing this a few of times I figured: Why not use the same thinking as with software? If something is boring, frustrating and you need to do it over and over again (check the doc) you automate it!

After a bit of looking around I settled with using a Google Spreadsheet and Google Apps Script to integrate that with Slack to get a reminder to the group and a personal ping for the facilitator and note taker in the morning of the day of the meeting.

After a couple of hours of hacking away I had something in place that looks like this in our Slack channel:

It requires no manual steps for us except for if someone leaves the group and requires people to be taken off the rotation. Below I’m going to walk you through how you can create this for your team as well.

Step 1: Setting up Slack

The first thing we want to do is to prepare Slack for receiving  messages from the outside. For this you will need to set up an Incoming WebHook. Start by going to this url: https:// [your Slack team name].slack.com/apps/manage/custom-integrations (if you can’t access this you will need to seek help from an admin). Go to Custom Integrations and then Incoming WebHooks.

In the dialog for Incoming WebHooks you will click the button Add Configuration. You can then select the channel you want to post to and then click Add Incoming WebHooks Configuration. At the top of the next page you will find the WebHook url. It looks like this: https:// hooks.slack.com/services/[a bunch of characters] and you will need it in the later steps. Except for Descriptive Label (where I’d advice you to enter something like “Meeting integration”) you can leave everything with the default values. Click Save Settings and let’s move on to the next step.

Step 2: Moving on to the spreadsheet

Now it’s time to move the data from the document where we had the list of facilitators and note takers to the spreadsheet where we will pull the data from. The first thing you will need is to make sure you have a G Suite account set up. Use your account to create a new spreadsheet and create a sheet called “data” (the naming is important in later steps). The layout should look like this:

The smeared out data in the sheet are the Slack handles of all the people on the team. This is the same lists that were previously found at the top of the meeting notes.

Step 3: Notifying Slack

What I want to do now is to take the top names, the people on row 2, and send that as a message to our channel in Slack. Time to do some scripting with Google Apps Script.

To access the script editor you go to Tools -> Script editor…

Let’s do the sending of messages step by step. All of these functions could be merged to one but if you want to extend the script it’s a good idea to keep them separated for reusability. Also, it’s going to be easier to explain them one by one.

Step 3.1: Creating the message

function createMeetingMessage(sheet) {
 var facilitatorCell = "data!A2";
 var noteTakerCell = "data!B2";
 var message = ">>> *TODAY'S MEETING*\n"
 + ":speaking_head_in_silhouette: *Facilitator:* @" +sheet.getRange(facilitatorCell).getValue() +"\n"
 + ":computer: *Note taker:* @" +sheet.getRange(noteTakerCell).getValue() +" (don't forget to bring a laptop)";
 
 return message;
}

The function takes a SpreadSheet as input (more on where this comes from in later steps)

The first two rows in the function pull data from the spreadsheet in the sheet “data” and shell A2 (facilitator) and B2 (note taker). On the next row the message is put together. Here you can use the same formatting as you would in a normal Slack message. I’m using quote, bold font in a couple of places and emojis. The “\n” is to insert line breaks.

Step 3.2: Sending things to Slack

function triggerSlackRequest(channel, msg) {
 var slackWebhook = "https://hooks.slack.com/services/[a bunch of characters]";
 var payload = { "channel": channel, "text": msg, "link_names": 1, "username": "Meeting Bot", "icon_emoji": ":robot_face:" };
 var options = { "method": "post", "contentType": "application/json", "muteHttpExceptions": true, "payload": JSON.stringify(payload) };

Logger.log(UrlFetchApp.fetch(slackWebhook, options));
}

This function takes a channel to send the message to and a message as input.

In the first row of the function you should enter your WebHook url that you got in Step 1.

On the second row the payload is put together with the channel, the message, name for the sender (Meeting bot in this case) and an icon to appear next to the message (I choose :robot_face:). Feel free to change the name and the icon if you prefer something else.

The next row is about how the message will be sent and then the last row is where the message is actually sent to Slack.

Step 3.3: Putting it together

function notifySlackChannelOfFacilitatorAndNoteTaker() {
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data");
 var channel = getChannel();

var msg = createPotlacMeetingMessage(sheet);
 triggerSlackRequest(channel, msg);
}

This function is where it is all pulled together.

On the first row you get the sheet you want to work with. The sheet you created in step 2. On the next row you get the name of the channel you want to send the message to. I use a separate function for this for debugging purposes. That way, if you want to play around with it, you avoid spamming the rest of your team. More about this function in the next step.

The third line is getting the message from 3.1 and on row four that message is sent.

Step 3.4: Safe play

function isTest() {
 return false;
}

function getChannel() {
 if(isTest()) {
 return "name of your test channel";
 } else {
 return "year teams channel";
 }
}

As mentioned above you don’t want to spam your team when you play around with these things.

The function isTest()  determines if you are testing or not. If it says false you send messages to your team. If it says true you send the messages to your test channel.

The function getChannel() gets the name of the channel you want to send your messages to. Replace the names above with the names of your channels.

Step 3.5: The complete thing

Putting it all together this is what it looks like.

function isTest() {
 return false;
}

function notifySlackChannelOfFacilitatorAndNoteTaker() {
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data");
 var channel = getChannel();

var msg = createPotlacMeetingMessage(sheet);
 triggerSlackRequest(channel, msg);
}


function getChannel() {
 if(isTest()) {
 return "name of your test channel";
 } else {
 return "year teams channel";
 }
}

function triggerSlackRequest(channel, msg) {
 var slackWebhook = "https://hooks.slack.com/services/[a bunch of characters]";
 var payload = { "channel": channel, "text": msg, "link_names": 1, "username": "Meeting Bot", "icon_emoji": ":robot_face:" };
 var options = { "method": "post", "contentType": "application/json", "muteHttpExceptions": true, "payload": JSON.stringify(payload) };

Logger.log(UrlFetchApp.fetch(slackWebhook, options));
}

function createMeetingMessage(sheet) {
 var facilitatorCell = "data!A2";
 var noteTakerCell = "data!B2";
 var message = ">>> *TODAY'S MEETING*\n"
 + ":speaking_head_in_silhouette: *Facilitator:* @" +sheet.getRange(facilitatorCell).getValue() +"\n"
 + ":computer: *Note taker:* @" +sheet.getRange(noteTakerCell).getValue() +" (don't forget to bring a laptop)";
 
 return message;
}

Step 3.6: Triggering sending the message

There is a tool i Google Apps Script called triggers that are pretty neat. We area going to use them to automatically send the message.

You access the triggers by clicking the button above or by going to Edit -> Current Project’s Triggers. Here you select the function you want to run and at what time you want to run it. As we have our meeting on Thursdays it looks like this:

Step 4: Rotating the list

Hang on a second! If we just go by this the same people will always be pinged as we never change the spreadsheet. We need to rotate the rows a bit.

Step 4.1: Adding a time for rotating out

function addRotationTime(sheet, row, column) {
 var time = new Date();
 time = Utilities.formatDate(time, "GMT+02:00", "yyyy-MM-dd HH:mm:ss");
 sheet.getRange(row, column).setValue(time); 
}

This function takes a sheet as input and adds a timestamp to the row and column from the input parameters.

Step 4.2: Swapping rows

function moveTopRowToBottom(sheet, firstRow) {
 var rowAfterLast = sheet.getLastRow() + 1;
 var lastRowRange = sheet.getRange(rowAfterLast, 1);
 sheet.getRange(firstRow).moveTo(lastRowRange);
 sheet.deleteRow(2);
 sheet.insertRows(rowAfterLast);
}

The function takes a sheet and the first row as input. The reason we send the first row is to avoid swapping down the header row.

The function finds the row after the last row with data and moves the first row to it. Once that is done the fist row is deleted (as it is now empty).

Step 4.3: Putting it together

function rotateFacilitator() {
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data");
 addRotationTime(sheet, 2,3);
 moveTopRowToBottom(sheet, "Data!A2:C2");
}

As with the sending of messages this is where it is put together. The function gets the sheet, adds the rotation time to the first row and then does the rotation.

Step 4.4: The complete thing

The three functions put together then look like this:

function rotateFacilitator() {
 var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("data");
 addRotationTime(sheet, 2,3);
 moveTopRowToBottom(sheet, "Data!A2:C2");
}

function moveTopRowToBottom(sheet, firstRow) {
 var rowAfterLast = sheet.getLastRow() + 1;
 var lastRowRange = sheet.getRange(rowAfterLast, 1);
 
 sheet.getRange(firstRow).moveTo(lastRowRange);
 
 sheet.deleteRow(2);
 sheet.insertRows(rowAfterLast);
}
 
function addRotationTime(sheet, row, column) {
 var time = new Date();
 time = Utilities.formatDate(time, "GMT+02:00", "yyyy-MM-dd HH:mm:ss");
 sheet.getRange(row, column).setValue(time); 
}

Step 4.5: Triggers… more triggers

As with the sending we want to trigger the rotation automatically. We do this at the start of the week.

Wrapping up

We have been using this for a couple of months now and it has helped us a lot. We have automated away a lot of unnecessary frustration within the team.

The code above can surely be made much cleaner. My excuse is that it’s been a while since I was coding last and it is my first stab at doing something in Apps Script. Also, this is only part of the code. We use the same data for some other automated things as well.

When I did this I had huge help from this post about how to automate stand-ups. If something in this post is unclear I’m sure you will find the answer there.