[GAS] Write posts to Google Spreadsheet using Slack’s Outgoing Webhook API

Slack has a convenient API “Outgoing Webhook” that you can use when you want to write messages posted on Slack’s channel to Google Spreadsheet.

Up to the last time, preparations for “Outgoing Webhook” have been completed, so let’s continue.

Create a new gas

Once Slack’s “Outgoing Webhook” is ready, we will prepare the GAS side.

First, create GAS as a container bind for the spreadsheet. What is container binding? If you are interested , please refer to here.

Create a new spreadsheet.

Write data to spreadsheet

First, prepare a variable to write data to the spreadsheet.

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

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

The data is sent to the “Data Acquisition” sheet.

Write data to the line after the last line

If you do not write data to the line following the last line, it will be overwritten, so prepare a variable that adds 1 to the last line.

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

Now you can write data to the row with 1 added to the last row.

Allow messages posted in Slack to be written to a spreadsheet

Get parameters so that you can receive messages posted on Slack.

You can receive the user name 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;

}

Write messages posted on Slack to a spreadsheet

Allow messages posted on 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 user name is written in the first column and the message is written in the second column. Finally, return to receive the result.

deploy

If you’ve made it this far, you’re ready to go. Then deploy GAS.

[rml_read_more]

At this time, it is necessary for all users to have access, so change it. Then deploy.

Deployed! After that, connect the issued URL to the application created last time.

Slack messages written to spreadsheet

I’ll test to see if any data is written.

Data has been written to the spreadsheet!

summary

You have now successfully written data from Slack to Google Sheets. It is convenient that the data is written to the next line of the end work each time it is sent.

Slack has a convenient mechanism called “Outgoing Webhook”, so please refer to it.

[GAS] Write posts to Google Spreadsheet using Slack’s Outgoing Webhook API | Part2