Integrating Slack with G-Drive, using Google Apps Script

But why?

At ST6 we ❤️ Slack. It enables productivity with its user-centric features and we definitely get the most out of their user groups. We have dedicated user groups for each project, circle, and their respective roles.

We also use G-Drive for organization-related documents where we describe the structure, circle definitions, the roles, their members, and so on. More often than not, we catch ourselves having outdated documents that mislead us about who is a member of what circle and what roles are occupied by whom.

As Slack naturally settled down as the single source of truth for such matters, we decided to solve this once and for all. We are about to use Slack as the source for the relevant data and present it in nice-looking tables and matrices in Google Sheets. Not only this would keep everything in sync, but also provide us with a holistic overview of the building blocks of our organization. Now let's dive right into the solution!

Solution overview

Set up the script environment

Create a new Google Apps Script by clicking "Extentions -> Apps Script" in the Google Sheets document toolbar menu.
From the sidebar editor tab, create a file named appscript.json.

Setup the script metadata with the following "oauthScopes". This allows external http requests via the UrlFetchApp API and updating the spreadsheet content via the SpreadsheetApp API.

{
  "timeZone": "Europe/Sofia",
  "oauthScopes": [
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/spreadsheets"
  ],
  "runtimeVersion": "V8"
}

Create an index file where we'll place our code.

Get a Slack auth token

Go to api.slack.com/apps.
Click "Create New App -> From scratch" and pick the desired workspace.
From the side menu on your app's page, go to "OAuth & Permissions".
Scroll down to "Scopes -> Bot Token Scopes -> Add an OAuth Scope" and include the following scopes to enable calls to Slack API methods users.list and usergroups.list
- users:read
- usergroups:read
Scroll to the top to "OAuth Tokens for Your Workspace" and click "Install to Workspace".
Copy the "Bot User OAuth Token" for later use in the script.

Retrieve data from Slack

const fetchFromSlack = apiMethod => {
  const apiUrl = `https://slack.com/api/${apiMethod}`;
  const headers = { Authorization: `Bearer <SLACK_AUTH_TOKEN>` };
  const response = UrlFetchApp.fetch(apiUrl, { headers });
  const result = JSON.parse(response.getContentText());

  return result;
};

const getEmployees = () => {
  const { members } = fetchFromSlack('users.list');
  const result = members
    .filter(
      ({
        name,
        deleted,
        is_bot,
        is_app_user,
        is_restricted,
        is_ultra_restricted,
      }) =>
        name !== 'slackbot' &&
        !(
          deleted ||
          is_bot ||
          is_app_user ||
          is_restricted ||
          is_ultra_restricted
        ),
    )
    .map(({ id, profile: { display_name } }) => ({
      id,
      displayName: display_name,
    }));

  return result;
};

const getUserGroupsWithMembers = employees => {
  const employeesById = employees.reduce(
    (result, { id, displayName }) => ({
      ...result,
      [id]: { id, displayName },
    }),
    {},
  );

  const result = fetchFromSlack(
    'usergroups.list?include_users=true',
  ).usergroups.map(
    ({ user_count: membersCount, users: membersIds = [], ...rest }) => ({
      ...rest,
      membersCount,
      members: membersIds.map(id => employeesById[id]),
    }),
  );

  return result;
};

Handle the circles participation table formatting

const formatCirclesParticipation = ({ employees, userGroups }) => {
  const circleHandleSuffix = '-circle';
  const conventionDisclaimerRow = [
    `To display a circle user group, its handle must be suffixed with "${circleHandleSuffix}".`,
  ];
  const circlesGroups = userGroups.filter(({ handle }) =>
    handle.includes(circleHandleSuffix),
  );

  const circleNamesWithMembersCount = circlesGroups.map(
    ({ name, membersCount }) => `${name} (${membersCount})`,
  );
  const tableHeaderRow = ['Name', ...circleNamesWithMembersCount];

  const rows = employees.map(({ displayName }) => {
    const participationChecks = circlesGroups.map(({ members }) =>
      members.find(m => m.displayName === displayName) ? '✅' : null,
    );
    const participationCount = participationChecks.filter(Boolean).length;

    return [`${employeeName} (${participationCount})`, ...participationChecks];
  });

  return [conventionDisclaimerRow, [' '], tableHeaderRow, ...rows];
};

Update the Google Sheet

const updateContent = () => {
  const employees = getEmployees();
  const userGroups = getUserGroupsWithMembers(employees);
  const updatedRows = formatCirclesParticipation({ employees, userGroups });

  // the spreadsheet ID from your sheet document URL e.g.
  // https://docs.google.com/spreadsheets/d/1a2b3c/edit#gid=0 -> id is "1a2b3c"
  const spreadSheetDoc = SpreadsheetApp.openById('<SHEET_DOCUMENT_ID>');
  const [firstSheet] = spreadSheetDoc.getSheets();

  const activeSheet = SpreadsheetApp.setActiveSheet(firstSheet);

  activeSheet.clearContents(); // clears all but keeps existing formatting
  activeSheet.getRange(1, 1).activate();
  updatedRows.forEach(row => activeSheet.appendRow(row));
};

Run the script

Manually - while developing, testing or one-off accidental updates:
1. From Google Apps Script Editor, select the "updateContent" function from the dropdown menu at the top.
2. Click "Run".
3. Watch your sheet getting cleared and updated row by row.
with a time-based trigger - to get a truly automated experience once the script is all set and done:
1. From the sidebar menu select "Triggers".
2. Create a new trigger and for "Select event source", pick "Time-driven".
3. Choose the desired time pattern.

End result

Depending on the formatting you defined via the Google Sheets graphic user interface (GUI), you might end up with something like this:

Bonus tips (security)

Remove hardcoded values for <SLACK_AUTH_TOKEN> and <SHEET_DOCUMENT_ID>.
You can safely store these values as script properties and remove them from the code:
1. Go to your script page and select "Project settings" - the gear icon at the sidebar.
2. Scroll down to the "Script properties" section and add the respective key-value pairs.
3. Then replace the hardcoded values with: PropertiesService.getScriptProperties().getProperty('<YOUR_SCRIPT_PROPERTY_KEY>');
Restrict the usage of the Slack "Bot User OAuth Token" to the Google Scripts IP ranges only:
1. Go back to the Slack app created in the earlier steps and navigate to "OAuth & Permissions" from the sidebar.
2. Scroll down to the "Restrict API Token Usage" section.
3. Enter these IP ranges used for Google Apps Script.
  In case you wonder where these come from, they are buried in this Google docs section.

Final thoughts

At ST6 we use the same approach to generate other tables overviews as well.
We rely on Slack user groups conventions to parse and present the data in an easy-to-view manner.
Here's a glimpse of some circle roles in Slack with their corresponding Google Sheets table:

Slack

Slack user groups auto-complete

Google Sheets

There's definitely room for creativity here. Unleash yours and