Slack + GAS + SpreadSheet - System for managing office equipment

19 minute read A

Background of this article

You can skip this section if you aren’t interested in.

I’ve been thinking vaguely that “ GAS can do lots of things” since I started using it, but it really seems almost perfect to make some simple systems. It’s great for students who don’t have $ to spend

Initially, I created this system using slack + GAS + spreadsheet, then I realized that I could reuse it in the equipment-management system for club activities, so I modified it a bit.

The club’s system was kind of iffy and my offer to improve it was approved.

I refered the following pages. Sorry there are no articles in English…

Overwhelming thanks…!!

Image of finished product

A system that allows you to post your equipment purchase history, storage and receipts (or lack thereof) in a specific channel of Slack, and it will automatically save the content to a spreadsheet and the images to a specific folder in Google Drive.

So, maybe this is a system for managing office equipment. Moreover, this is a system where Bot will post a submission template for newcomers.

Specifically, I wanna make like this↓

Demo

System requirements

The rough scheme is as follows.

systemFlow

  • Once a day, the BOT checks the Supplies Buying Channel in Slack and saves the content of new posts, if any, in a spreadsheet (but ignores posts that do not adhere to the template)
  • Delete all posts posted that day at the same time as above so as not to oppress the workspace capacity
  • Bot posts the template text to the channel everyday for newcomers

so, I assemble the system following above conditions.

Procedure

  1. Create a SpreadSheet(SS)
  2. Create a new script from “SS’s script editor” (to store Slack’s information of equipment)
  3. Write the code to be introduced later
  4. Create and add a new Slack API’s application to your work space
  5. Set up Slack’s Incoming Webhook
  6. Set up a trigger to run the code(In my case this it works once a day, from 3 a.m. to 4 a.m.)
  7. Create a new script in Google Drive for sending a template message, copy&paste the code to be introduced at the end and set up the trigger

GAS code that goes back a day’s worth of Slack posts and stores the posts and images

Suddenly, this is the finished pruduct of the GAS code.


Perhaps if you are new to this kind of code, you would say “What the hell”. I know I should have written codes with higher readability. Explanation of the code is below.

Environment / Global variables

Strange variables are defined in the first three lines of the script. These are variables that contain data such as the slack API and the channel Url, which can be misused if they are passed out.

They can be set from the File -> Project Properties -> Script Properties in the script editor, and can be freely referenced in the script.

By making important information into environment variables, you can greatly prevent the leakage of information to the outside world.

  • botToken and userToken
    Neccessary Tolen for running Slack API
  • Schannel
    Slack channel’s ID for using this application

setSlackLog function

See the folloeing image for the big picture. Roughly, the system loads channel’s posts and modifies the process with a conditional branching. Since Slack’s free plan has a 5 GB workspace limit, I decided to delete all recorded information at the end.

how2fork

  • Reasons to reverse the message information(Around 4~6 lines)

I use Conversations methods in the getSlackLog function described later, but the JSON data that comes across is in anti-chronological order, meaning that the data in the first array relates to the most recent post. Therefore, this process is necessary if you want to record in the order of purchases. There are other methods tho.

  • Reasons to write lastrow -= 1(30 and 54 line)

lastrow is a necessary variable that detemine which line in SS contains the infomation. Since the code loads posts that are not related to the equipment’s information, such as BOT’s posts, I use it to adjust the lines to exclude such posts.

getSlackLog function

I used one of the methods named Conversation.history method. Official document is below.

Although Slack API Token is needee, we can obtain almost all of the information about posts in Slack.

getSlackUserName function

The aforementioned Conversations method alone does not give usernames, including the real names of applicants. Threrefore, we need to fetch the user_id using Users method.

getFileInfo function

This section was the trickiest point. The description of the Conversations method shows that

messages.files["which array"].url_private_download

So, I thought I could get the download URL of the file posted to Slack, but it didn’t work. A few months after implementing this system, I noticed that this time, the variable messages contains data reversed from the chronological order of the variable response, and this is why the data in the messages.files cannot be pulled out individually.

Thus, if you wanna pull out information, such as images of recieipt and attached files, with chronological order, you have to use getFileInfo(Files.list method) function etc. to avoid any errors related to it.

It took me about more than 20H to solve this issue…hahaha….haha

GAS code to send template to a channel everyday from BOT



All of the remaining is to set up the trigger for running GAS code at approproate time. This is it.

Summary + Reflection points

I was moved by the possibilities of GAS, even if you don’t have money, you can do so much…

If I work hard, I can add an equipment information update features, but I’ll hold off on that for now. I’ll do it again when I’m energized.

One Comment. ...Actually I’m still not sure about how API works… I want a mentor