How to notify the Slack channel when Google Sheets is updated
I was able to notify the Slack channel several times when the Google Sheets were updated. I read an article on a web blog.
Ref.
- Official Document: Class Sheet | Apps Script | Google Developers
- 【Google Apps Script入門】セルの取得・変更をする | UX MILK
Useful Google Spreadsheet’s method
Cell’s something to doing method is getRange(), Active Cell’s something to get method is spreadsheet.getActiveCell().
I learned to create GAS code using an article at uxmilk.jp.
1
2
3
4
5
6
7
// Get cell A1:A3
var range = sheet.getRange(1, 1, 3);
// Logger
// getValues() is Array, getValue() is Array first = array[0].
Logger.log(range.getValue());
Logger.log(range.getValues());
I made a following script from refer some articles. Once you make it, you can reuse it.
1
2
3
4
5
6
7
8
9
10
11
12
// Get Email Address
var editorEmail = Session.getActiveUser().getEmail();
// Get Sheet Information
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // Current Spreadsheet
var sheet = spreadsheet.getActiveSheet(); // Current Active sheet
var sheet_url = spreadsheet.getUrl(); // Active sheet's URL
var cell = spreadsheet.getActiveCell(); // Active sheet
// Note: getRange Usage
// sheet.getRange(row, column ,[Number of Range Lows default:1], [Number of Range Column default:1])
Sample of Notification to Slack
1
2
3
4
5
6
7
8
9
10
11
function postToSlack(body, channel) {
var url = "[slack webhook url]";
var data = { "channel" : channel, "username" : "Google Notification Bot", "text" : body, "icon_emoji" : ":dog:" };
var payload = JSON.stringify(data);
var options = {
"method" : "POST",
"contentType" : "application/json",
"payload" : payload
};
var response = UrlFetchApp.fetch(url, options);
}
On Script Editor, you can choose the debugSlack() method and execute/debug execute.
If you need to debug/testing on script, I finished create/debug on following testing method.
1
2
3
function debugSlack() {
sendToSlack("Google Notification Tsting", "[#slack_channnel_name]");
}
Sample: Get the latest row on Spreadsheet
Ref. Notify Chatwork when new rows are added to Google Spreadsheet-Qiita You can use breakpoints. Debug execution is possible
E.g.: Assuming there is an item named check in some column of the first row
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
// Get Email Address
var editorEmail = Session.getActiveUser().getEmail();
// Get Sheet Information
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); // Current Spreadsheet
var sheet = spreadsheet.getActiveSheet(); // Current Active sheet
var sheet_url = spreadsheet.getUrl(); // Active sheet's URL
var cell = spreadsheet.getActiveCell(); // Active sheet
function onSheetTest(){
// Search Colmn No. of Sheet
var checkCol = colSearch("check");
// Get last-low
var lastRow = sheet.getLastRow();
var thisChecks = sheet.getRange(lastRow, checkCol);
// Get ALL values of Columns
var thisAllChecks = sheet.getRange(2, checkCol, lastRow);
// a value: getValue()、several values: getValues()
var message = "Status:" + thisChecks.getValue();
var messageAll = "all status:" + thisAllChecks.getValues();
}
// Search column of Label and return number of columns
function colSearch(label) {
for (i = 1; i <= sheet.getLastColumn(); i++) {
if (sheet.getRange(1, i).getValue() == label) {
return Number(i);
}
}
}
