Automated Slack reminders for your meetings with Google Apps Script

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:

Skärmavbild 2017-07-08 kl. 22.52.23.png

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.

Skärmavbild 2017-07-08 kl. 21.32.11

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:

Skärmavbild 2017-07-08 kl. 21.05.46

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.

Skärmavbild 2017-07-08 kl. 22.23.44

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:

Skärmavbild 2017-07-08 kl. 22.27.49

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.

Skärmavbild 2017-07-08 kl. 22.43.05.png

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.

 

Advertisements

Stand-up Cheat Sheet

Since the Planning Cheat Sheet seems to have been useful to a few people I figured I’d go ahead and share another Cheat Sheet I put together a while ago. This time around it’s the Stand-up Cheat Sheet. The way I’ve used this is by printing it and posting it next to the boards of a few teams I’ve been helping lately.

The first bit under “How we do it” assumes you are doing Walk the board rather the old “what I did yesterday, what I’m going to today, impediments” style.

If you want more good ideas on how to improve your stand-ups I really recommend the blog post It’s Not Just Standing Up: Patterns for Daily Standup Meetings by my colleague at Spotify Jason Yip.

Stand-up Cheat Sheet

Planning Cheat Sheet

A while ago I got approached by a team that was struggling with their sprint plannings. To help them help themselves I put together a Planning Cheat Sheet to give them some pointers. It has come to good use since and has caught some attention where I’m working so I thought I would share it here as well for others to use.

Please comment if you found it useful and/or if you would like some tweaks to it.

planning cheat sheet

Team Mission and Definition of Awesome

In this post I’ll describe some exercises that can be used when kicking off a new team or if you want to get an existing team aligned on how it wants to work and what value they provide to the organization.

The package contains of the parts below. Depending on how far you want to take it and how you work with improvements you might want to settle for the first two parts.

  1. Setting the stage and warming up
    1. What is an awesome team?
    2. Appreciative inquiry
  2. Mob writing: Mission and Definition of Awesome
  3. Are we there yet? Identifying the current condition and obstacles.
  4. Identifying candidates for first steps in reaching the definitions of awesome.

All of these usually take about 3 hours to get through (including breaks).

Setting the stage and warming up

The first thing you want to do is to talk about why you are there (you probably already touched on that in the invite) and why it is important that the whole team participates.

After that you will want to get people in the right mindset with some warm up. Especially if this is the first thing you do in the morning.

What is an awesome team?

In this part you can throw up examples of teams and ask the group if they are awesome or not. Discuss why or why not. You can find your own but here are some examples and points that you can discuss.

  • The A-team
    Cross functional
    Have clear missions
    Help each-other to solve tricky problems
    … and like the A-team all teams need a crazy helicopter pilot. 😉
  • Fellowship of the ring
    A clear mission
    Not that aligned on how to reach that mission
  • Orchestra or a rock band
    Specialists
    Sound better when they work together
    Practice really hard individually on their skill but also need to play and practice with other types of competences.
  • Sports team (in this particular case Chelsea when they won the Champions league)
    This picture tells the story about a sports team that were the underdogs throughout the kick out phase but managed to get to the finals. In the finals they met Bayern Munich and were again underdogs. In the end Chelsea had done all their substitutions and a defender got injured. Fernando Torres (a forward) took a place in the defense of the team. He went outside of his expertise to to what the team needed at the time and in the end the team won after overtime and penalties.
  • People working out at a gym (compared to a sports team)
    From the outside it might look like everyone is really busy and working on the same kind of thing (like a sports team). In reality there is little to no cooperation and everyone is only focused on their own goals/agendas. Do the team participating in the workshop want to be people working out at a gym or a sports team?

Appreciative inquiry

This warm-up is a segway into the third part – Mission and Definition of Awesome. The purpose being that the team members start telling each other what they think is working well from their perspective.

This exercise builds on appreciative inquiry where you only focus and talk about what is working well or what could be working well.

Stage 1

Ask the participants to write on post-its what is working well today. One thing per post-it. After about 3-4 minutes you ask the participants to present their post-its one by one and talk about what they have written.

Stage 2

Ask the participants to imagine that you have created a time machine. You all step through this time machine and travel into the future. Depending on what context you might want to select different amounts of time but it can for instance be a year into the future. In this future you walk over to the office and meet your future selves. They say that it has never been this great. Everything is working perfectly. You are productive, happy etc. Ask the participants to write on post-its what their future selves tell them and what they see around them. Present as you did in stage 1.

Mob writing: Mission and Definition of Awesome

The next part is to as a group agree on what the mob-writing-300x156.pngpurpose of the team is (Mission) and
what identifies them when they are working really well (Definition of Awesome) is.

Start by explaining the format
and the rules:

  • Format
    • You will have one or two sentences describing the teams mission. Try to make it snappy.
    • You will have five (not more!) statements describing what identifies the team when it is working really well. The team can draw inspiration from the post-its on the wall from the previous exercise as inspiration.
  • Rules
    • Everyone participates Everyone can write, erase, reformulate… It is done when no one has objections or adjustments.

Invite everyone to the whiteboard and hand out pens. What usually happens is that people start adding statements without getting questioned. The magic happens when all five slots are full. Then the teams needs to start communicating, arguing and agreeing on what they think is important. Try to give the team as much room as possible but if discussions derail ask the team to move on the something else and then go back to pick up the statement discussed later on.

When the discussions are cooling down you can enter the group again and point at the first statement and ask if everyone agrees. Thumbs up = agree, thumbs to the side = could be improved, thumbs down = this is not ok.

When the team is in agreement move on to the next part.

Are we there yet? Identifying the current condition and obstacles.

roads-300x172.pngSo now we have identified where the team wants to be – the Definition of Awesome. Let’s figure out how to get there. Here we use the classic coaching format of identifying the target condition (Definition of Awesome), current condition, obstacles and actions/improvements. In the picture to the right the rainbows and unicorns is the definition of awesome. This is where the team wants to be. The five roads represent the five statements and the swamp is where the team is today.

Step 1: Current condition

In this step the team writes post-its describing the current condition connected to each track. These are represented by the yellow post-its on the picture.

Step 2: Obstacles

Here the team looks at what is preventing them from reaching the target condition. These are represented by the pink post-its.

First steps

improvement.pngIn this part the team will identify how Awesome can be reached. We use a rotating flipchart style where you set up five flip charts or Magic Charts spread out around the room. Split the team and spread out among the charts. On each chart you talk about one of the definitions and what you can do to reach it. If the idea is complex you might want to use a Kata style approach – we put them on the upper part. Actions that are quick and straight forward are put in the bottom.

After about 5 minutes it’s time to rotate and you go to the next chart and discuss that. After four rotations everyone should have visited each chart/definition.

To have something concrete to bring back and to get started when right away when you come back to the office it can be a good idea to vote on one quick fix per chart to do right away. Assign people to take responsibility for each quick fix that are voted on.

Following up

After these exercises the team will have a lot of materials to drive improvements from. Bring it all back and try to make as much of the results visibal and present near the team. If you aren’t already using a Kata-style approach to improvements perhaps you can find some inspiration in this blog post: Toyota Kata – an alternative to retrospectives by Håkan Forss.

However you do it, the important thing is that you continue to work with the results so the time wasn’t wasted and just a nice experience in a conference room somewhere.

Summary

This set of exercises have proved to work quite well for me. Off course you might want to tweak them to fit the teams needs or add other exercises if you do this as part of a full day.

I hope this post was helpful and don’t hesitate to comment below or reach out to me if you have any questions or want help in facilitating a workshop.

Good luck!

Time for a new manager – let the group choose

At the company I work we faced the task of assigning two new managers for a group of consultants that were without a manger and also had grown too big for just one person to look after their needs. Traditionally this was done by our CEO and some other managers who looked for someone in-house by talking to people they thought were suitable for the job or sometimes by looking for people from the outside. Historically this had sometimes worked well and other times not so well.

This time we thought we should try something new. Why not let the members of the group choose?

3-leader

Our process looked something like this:

  1. We informed the group of what we were about to do and why we were doing it.
  2. We created a form with Google Forms that was sent to all the members of the group. It contained these two questions:
    1. Would you be interested in being the new manager for this group? (yes/no/maybe – tell me more)
    2. Who do you think would be suitable to be the next manager?
  3. After everyone had answered we gathered up the results and started talking to everyone who had given a “yes” or “maybe” answer to the first question. During that meeting we discussed their view on leadership and the challenges they saw from their point of view. We also answered any questions about what having this role would mean.
  4. We selected two candidates based on the number of nominations (this was the main thing we considered) but also looked at their view on leadership.
  5. We split the group mainly based on the nominations but we also considered social connections and other similar factors.
  6. We then talked to all the members of the group individually and asked them if they were happy with their manager to be or if they wanted to belong to the other group. If someone wanted to swap we let them without any questions asked.
  7. We talked to the new managers and told them what the groups would look like and helped them get started.
  8. Done.

This process became quite lengthy but the really great thing about this approach is that we have eliminated a lot of risks involved in a change like this. The new manager was instantly accepted by the group and the managers knew what they were getting themselves into and knew that they were accepted in their new role by their group.

This is still quite new and we will see how it plays out in the long run but from we have seen so far this is a really good approach when appointing a new manager or leader for a group.

Shorten your feedback loops with Cafe Testing

One of the teams I have been working with lately develop and maintain a retailing web site. Historically what they should focus on was decided by someone high up in the organization with little to no involvement of the team. After a lot of time and money invested most of those ideas turned out to have little to no impact on the number of purchases made through the site. To remedy this we wanted to leverage the collective intelligence of the team and also use a more scientific approach when deciding what to do. The approach we took drew inspiration from Lean UX and Lean Startup. We also used tools like Impact Mapping where the idea is to have a clear goal of what we want to achieve, see what actors could affect that goal and then look at what assumptions we have about those users related to that goal. After that we have our Impact Map and it’s time to get to work with the assumptions. We did this in really short iterations to keep the investments small if assumption turned out be false. One really useful way of validating ideas we found was to do lightweight usability testing. The way we did it often included theses steps: 

  • We have an idea or an assumption about how we can better achieve one of the goals we are striving for at the moment. We selected the assumption that we thought could best contribute to the goal.
  • In the most lightweight way possible we create a mockup, prototype or MVP that could in some way validate or invalidate our assumption.
  • The next step is to do the validation and now it was time to get out of the building. We found that a good place to find test subjects was at a café at the grand central station in Stockholm. The benefit of this is that you find people from all over the country of all different ages. They are very often just waiting for their train and because of that very open to answering some questions in exchange for us buying them a cup of coffee.
  • When we have interviewed about 3-5 persons representing different personas we usually have enough data to go back to the drawing board. Did our assumptions hold true or do we need to focus on something else? Of course we can’t blindly go by what the test subjects were saying but it is usually a good indication.

We can usually go through this cycle in a day and that gives us really fast feedback loops and we avoid investing too much time and money in developing something just because someone high up in the hierarchy decided it was a good idea.

This approach has helped us a lot and perhaps it is something that can help you as well if it fits your context. Please post a comment below if you found this post helpful or if you have tried it yourself and have some insights or experiences to share.

coffee