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…
- Linking Slack and spreadsheet for muscle training management
- Qiita: How Slack logs are used in conjunction with spreadsheets to organize technical notes
- [GAS] Automatically save Slack logs to GoogleDrive, including the uploading data
- Google Apps Script Primer: Saving Images on the Web to Google Drive
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↓
The rough scheme is as follows.
- Once a day, the BOT checks the
Supplies Buying Channelin 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.
- Create a SpreadSheet(SS)
- Create a new script from “SS’s script editor” (to store Slack’s information of equipment)
- Write the code to be introduced later
- Create and add a new Slack API’s application to your work space
- Set up Slack’s Incoming Webhook
- 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.)
- 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
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
Slack channel’s ID for using this application
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.
- 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.
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.
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.
This section was the trickiest point. The description of the Conversations method shows that
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