GAS Using Slack’s Outgoing Webhook API to Write Posts in Google Spreadsheets｜Part2

Slack had a convenient API “Outgoing Webhook” that we can use when we want to “write messages posted to a Slack channel into a Google Spreadsheet.

Previously, we have completed the preparation of “Outgoing Webhook”, so we will continue to make it.

Create a new GAS

Once the “Outgoing Webhook” in Slack is ready, the next step is to prepare the GAS side.

First, we will create GAS as a container binding for the spreadsheet.

Create a new spreadsheet.

Write data into the spreadsheet

First, prepare variables to write data to the spreadsheet.

Create a function doPost(e) (a function that is executed when a POST request is sent to the web app) so that you can retrieve data from the spreadsheet and write data to the spreadsheet.

function doPost(e) {
 const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('データ取得');
}

Data is sent to the “Get Data” sheet.

Writing data to the row following the last row

If we do not write data in the row following the last row, it will be overwritten, so we prepare a variable with 1 added to the last row.

function doPost(e) {
 const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('データ取得');
 const lastrowNext = sheet.getLastRow()+1;
}

Now you can write data to the last row plus 1.

Allowing messages posted in Slack to be written to a spreadsheet

We will get a parameter so that we can receive messages posted in Slack.

You can receive the username in .user_name and the text (message) in .text.

function doPost(e) {
 const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('データ取得');
 const lastrowNext = sheet.getLastRow()+1;

 const username = e.parameter.user_name;
 const text = e.parameter.text;

}

Writing messages posted in Slack to a spreadsheet

Enables messages posted in Slack to be written to a spreadsheet.

function doPost(e) {
 const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('データ取得');
 const lastrowNext = sheet.getLastRow()+1;

 const username = e.parameter.user_name;
 const text = e.parameter.text;

 sheet.getRange( lastrowNext, 1).setValue(username);
 sheet.getRange( lastrowNext, 2).setValue(text);

return;
}

The username is written in the first column and the message in the second. Finally, return to receive the result.

Deploying

At this point, we are ready to deploy. Now it is time to deploy GAS.

At this point, change the users who have access to the system, as they must all be the same user. Then deploy.

Deployed! Now, let the issued URL connect to the previously created application.

Slack messages are written to the spreadsheet.

Let’s test to see if any data is written to the spreadsheet.

Data has been written to the spreadsheet!

Summary

Now you can write data from Slack to a Google Spreadsheet. Each time you send the data, it is conveniently written to the next row of the trailing business.

Please refer to Slack’s “Outgoing Webhook” for more information 😃.