Automating Slack Reports of Attendance Schedules Using Box SDK

Introduction

Hello. My name is Chris and I work in the Global Development Department at KINTO Technologies, where I work on front-end development.
Today, I'd like to write about automation of business tasks, instead of front-end development.
According to Slack's Productivity Report released last month, 77% of workers said that being able to automate routine tasks would greatly improve their productivity, saving them about 3.6 hours per week. So, it's important to automate your daily work as much as possible, so you can focus on what you really need to do and get more done.
Sorry for the sudden change of subject, but I'd like to talk about our company's attendance rules, which were revised from July this year. Following the rules, we have a fixed maximum number of remote workdays every month. When to take those days is generally up to the individuals, as long as it's coordinated within the team and shared with other members of the division. In order to know the attendance schedule of the members, we have to report the schedule for the following week in advance.
So in the Global Development Department, each member writes down their schedule for the following week on a monthly Excel spreadsheet stored on a cloud service called Box, and the leader then compiles their team's data and shares it with their manager on Slack.

What's the Problem?

Due to various departmental circumstances, the most efficient way to manage information is to use Excel to manage it all at once, but the problem is the flow of sharing information with managers. The Global Development Department has many members, and as a result, there are a fair number of leaders. It takes some time for all the leaders to take screenshots of Excel sheets and share them every week, and switching between tasks is a mental burden. In addition, some team members don't have assigned leaders, so their schedules aren't shared. The only way to check their availability is by looking directly at the Excel sheet.
I thought it would be great to eliminate these two difficulties through leveraging my engineering skills with minimal time and efforts. So, I used the SDK provided by Box and Slack to automate the process of extracting information from Excel and uploading the schedule details to Slack!

Development Environment

This automation was achieved using Node.js with the following libraries. The actual code was created using Typescript, but this article will show the Javascript code. The following are also used for the implementation.
dotenv
When using the Box SDK or Slack SDK, I need to enter sensitive information such as tokens, so I want to make them environment variables using dotenv.
https://github.com/motdotla/dotenv
box-node-sdk
SDK for Node.js provided by Box.
https://github.com/box/box-node-sdk
node-slack-sdk
SDK for Node.js provided by Slack.

node-xlsx
A library that converts information from Excel files to JSON.
https://github.com/mgcrea/node-xlsx
canvas-table
A library that turns tables into images.
https://github.com/el/canvas-table
node-canvas
A library on which canvas-table is based.

Implementation

Now I would like to explain the implementation step by step.

Step1: Retrieve files from Box

First, an application needs to be created from the Box admin console to enable the use of the Box SDKs. You can create a new one from the Box Developer Console (xxx.app.box.com/developers/console). After creation, a client ID will be issued for the app, but an access token must be issued separately. If your workspace is managed by your company, you will generally need to get approval from the company administrator on the administrator's screen.
box_my_app_list
Once you have obtained the token, you should have been issued a service account ID from the app details screen. If you do not share the folder or file you want to access with this service account, you will get a 404 error when you try to get it from the SDK.
box_service_account
Next, I'd like to move on to the code. First, install the Box SDK.

yarn add box-node-sdk

After that, you can write code like this to download the file to the specified location. A description of the download process can also be found in the official documentation.

import BoxSDK from "box-node-sdk";
// Put the issued token here.
const boxClient = BoxSDK.getBasicClient("token information");
// After that, use async/await for the process to retrieve information from the file.
await downloadFile();
async function downloadFile() {
  return new Promise((resolve, reject) => {
    boxClient.files.getReadStream(
      // File ID
      "1234567",
      // Query parameter, for example, use if you want to get an older version of a file
      // https://developer.box.com/reference/get-files-id-content/
      null,
      // Callback function
      function (error, stream) {
        if (error) {
          reject(error);
        }
        const output = fs.createWriteStream("output path of the file");
        // Resolve Promise when finished writing
        output.on("finish", resolve);
        stream.pipe(output);
      }
    );
  })
}

Run the above code, and if the file exists and access permissions are correctly granted, the file should be exported to the specified path.

Step2: Retrieve necessary information from a file

Next, I want to retrieve the necessary information from the file downloaded from Box. Since it is an Excel file, I'll use node-xlsx to parse the Excel information.

yarn add node-xlsx

import xlsx from "node-xlsx";
const workSheets = xlsx.parse("path of the downloaded file");
console.log(workSheets)
// [
//   {
//     name: "sheet name",
//     data: [
//       [],
//       [],
//       []
//     ]
//   }
// ]

This will allow you to extract the information for each Excel sheet as a nested array, allowing you to process the data or delete any unnecessary data.

Step3: Convert the information into an image

Frankly, many of you may be wondering, "Why do we need this?" In fact, even when I first tried the automation, I had no idea. However, after obtaining the necessary information, I tried several ways to post table information to Slack in an easy-to-read format. For example, I tried creating a table using Markdown, but Slack does not support it, so when I actually tried, the layout was quite messed up. As a result, when I turned the table information into an image, the members' schedule information was neatly arranged.
To this end, I used canvas-table for creating the table image.

import { CanvasTable } from "canvas-table";
import { createCanvas } from "canvas";
// First, create a blank image (Canvas)
const canvas = createCanvas(image width, image height);
// Define information about the table
const tableConfig = {
    // Column information
    columns: [
      {
        title: "title"
      }
    ],
    // Information for each cell
    data: [
      [
        {
          value: "text",
        }
      ]
    ],
    // Optional information
    options: {
      borders: {
        column: { width: 1, color: "#555" },
        row: { width: 1, color: "#555" },
        table: { width: 1, color: "#555" },
      },
      title: {
        text: "title",
      },
    }
  };
}
const ct = new CanvasTable(canvas, tableConfig);
await ct.generateTable();
await ct.renderToFile(fileName);

This will generate the table image shown below.
fe_attendance

Step4: Post to Slack

The next step is to post the image to Slack. Use @slack/web-api's files.upload provided by Slack.

yarn add @slack/web-api

import fs from "fs";
import { WebClient } from "@slack/web-api";
// Set Slack OAuth Tokens
const slackClient = new WebClient("token information");
const result = await slackClient.files.upload({
  channels: "channel ID",
  initial_comment: "accompanying comment text",
  file: fs.createReadStream("file path")
});

fe_attendance_on_slack
Upload is now complete!

Step5: Autorun with GitHub Action

With the steps above, the script is complete, but it still needs to be run locally. Now it would be perfect if this script could run automatically, wouldn't it?
We use GitHub Actions a lot in our company, regardless of department, so we will use it again this time. First, create a yml file.

name: Name of the workflow
# Runs every Wednesday at 1:00 p.m. JST (listed at 4:00 a.m. UTC)
on:
  schedule:
    - cron:  '0 4 * * 3'
jobs:
  build:
    runs-on: ubuntu-latest
    steps:
      # Checkout a repository
      - name: Checkout
        uses: actions/checkout@v3
      # Set up the Node environment
      - name: Setup Node.js environment
        uses: actions/setup-node@v3
        with:
          # Specify the appropriate Node version
          node-version: '18'
      # Install the library with Yarn
      - name: yarn install
        run: yarn install
      # Run the script (if the js file you want to run is index.js, as follows)
      - name: Run index file
        run: node index

Now it will be executed automatically at the time specified by cron (although it may be slightly delayed).

Step Extra: Change the font

While this step is not necessary, I tried it as an extra step. As a group company of Toyota, we use Toyota's own font. I would like to apply it to the schedule table.
I used the library called cavnas to create the image, but you can actually set the font as well. Since the Toyota Font is proprietary, a font file must be provided so that it can be referenced by the project.

// Import registerFont
import { registerFont, createCanvas } from "canvas";
// Always place before createCanvas
registerFont('Font file path', { family:  'Font name' });
const canvas = createCanvas(canvasWidth, canvasHeight);
// Specify the font to be used for the image
const config = {
    columns: [
      // ...
    ],
    data: [
      [
        // Define the cell information
        {
          value: "text",
          fontFamily: "font name",
        }
      ]
    ]
    options: {
      // Define the title
      title: {
        fontFamily: 'font name',
        text: "title",
      },
    }
  };
}
const ct = new CanvasTable(canvas, config);
// ...

If all goes well, you will have an image with the font applied like the one below.
fe_attendance_Toyota_font

Conclusion

There are still many areas to improve on what I created this time, so when I have time I would like to refactor it and add some nice features. If your company is also considering automating some business tasks, I hope this will be helpful!