5/25(Wed)
第1回:2019年12月19日公開
60min
第2回のソースコードを追記しました(2020/01/21)
本授業では、普段プログラミングをしない方を対象として、Googleスプレッドシートをもっと便利に使うための簡単なプログラミング、Google Apps Script(通称:GAS)を学びます。
GASを使うとシートやセルの内容を簡単なプログラミングで操作できるようになります。(ちょうどExcelのVBAと同じようなものですね)
一見難しそうに思えますが、少し背伸びをするだけで、これまで手作業でやっていたことが自動化できるようになり、業務効率UPはもちろん、進行管理やデータ管理が楽になります。
ぜひこの機会に、GASでできるいろいろな自動化を体験してみましょう!
・Google Apps Scriptを使ってみる
・データからシートを自動で生成する
・データからグラフを自動で生成する
・Googleフォームの投稿を自動でSlackに通知する
・LINEボットを作成し、毎日のデータを自動送信する
・定期的にデータ集計や分析を行っている、または行いたい方
・進行管理をしている方
スクー放送部
スクー放送部
2019年12月19日公開
60min.
■授業の内容
・Google Apps Scriptを使ってみる
・データからシート、グラフを自動で生成する
・Googleフォームの投稿を自動でSlackに通知する
・LINEボットを作成し、毎日のデータを自動送信する
■授業中に書かれたソースコード
/* 1.Google Apps Script(GAS)を使ってみる */ function addMoji() { var spreadsheet = SpreadsheetApp.getActive() var sheet = spreadsheet.getSheetByName("テストシート") sheet.getRange(1, 1).setValue("たはらさん") } function getMoji() { var spreadsheet = SpreadsheetApp.getActive() var sheet = spreadsheet.getSheetByName("テストシート") var name = sheet.getRange(1, 1).getValue() var string = name + "さんは赤い服をきている" sheet.getRange(2, 1).setValue(string) } /* 2.データから自動でシートを生成する */ function copySheet(){ var spreadsheet = SpreadsheetApp.getActive() for(var i=1; i<=12; i++){ var sheet = spreadsheet.duplicateActiveSheet() var date = "2019/" + i +"/1" sheet.getRange("A1").setValue(date) sheet.setName(date) } } /* 3.データから自動でグラフを生成する */ function createChart(){ var spreadsheet = SpreadsheetApp.getActive() var sheet = spreadsheet.getSheetByName("家計簿2") var chart = sheet.newChart() .addRange(sheet.getRange(2,1,21,1)) .addRange(sheet.getRange(2,2,21,2)) .setPosition(2,15,0,0) .setChartType(Charts.ChartType.PIE) .setOption('title', '2019年1月のデータ') .setOption('width', 200) .setOption('height', 200) .setOption('legend', {position: 'bottom'}) .build() sheet.insertChart(chart) } /* 4.Googleフォームの回答を自動でSlackに通知する */ // フォームに回答があったら、send_to_slackを呼び出す function getform(){ var spreadsheet = SpreadsheetApp.getActive() var sheet = spreadsheet.getSheetByName("フォームの回答 1") var values = sheet.getRange(sheet.getLastRow(), 2, 1, 3).getValues() var answer = values[0] var q1 = answer[0] var q2 = answer[1] var q3 = answer[2] var text = "【回答がきました!】 \n\n" text += "投稿者: " + q3 + "さん\n" text += "Q1: " + q1 + "\n" text += "Q2: " + q2 send_to_slack(text) } // Slackに「text」を送る function send_to_slack(text) { var payload = { 'text' : text, } var options = { 'method' : 'post', 'contentType' : 'application/json', 'payload': JSON.stringify(payload) } // ここにはSlack Appsで取得したIncoming Webhook のURLを入れる // 取得元: https://slack.com/intl/ja-jp/apps var url = 'https://hooks.slack.com/services/*************' UrlFetchApp.fetch(url, options) }
2020年1月21日公開
60min.
前回学んだGoogle Apps Scriptと、LINEのMessaging APIを組み合わせて、LINEのチャットボットを作成します。自動返信用の辞書や、定期的実行など、Googleスプレッドシートならではの機能も活用していきます。
それほど多くないコードで作成できるので、まずはやり方を見て一緒に流れをつかみましょう。(授業後にソースコードを共有します)
<内容>
・LINE チャットボットを作成する
・自動返信機能を追加する
・一斉送信機能を追加する
▼ LINE Messaging API
https://developers.line.biz/ja/services/messaging-api/
▼ LINEボットの画面イメージ
■授業中に書かれたソースコード
// LINE developers のTOKEN // https://developers.line.biz/ var ACCESS_TOKEN = '*********'; // ここにACCESS_TOKENを入れる var sheet = SpreadsheetApp.getActive().getSheetByName('Line') /* *************** 00: 基本 *************** */ // POSTアクセスが来た場合の処理 (今回はBOTに何かしらのアクションをされた場合が該当) function doPost(e) { // イベント(ユーザーからの何かしらのアクション) var event = JSON.parse(e.postData.contents).events[0] // イベントから主要な要素を抽出 var eventType = event.type var userId = event.source.userId /* 1. 友達登録の場合 → ユーザーデータを保存 */ if (eventType == 'follow'){ stockUserData(userId) } /* 2. メッセージ受信の場合 → 応答メッセージを返す */ else { var replyToken = event.replyToken; var userMessage = event.message.text; replyLine(replyToken, userMessage) } } /* *************** 10: 返信系(Reply) *************** */ // Lineに「返信」する function replyLine(replyToken, userMessage){ /* 返答メッセージを生成する */ var dictionary = sheet.getRange("A6:B100").getValues(); var message = "" // userMessage = スクーのミッションは? // dictionary[i][0] = "ミッション" // 辞書から「キーワード」を検索 for(var i=0; i < dictionary.length; i++){ if(userMessage.match(dictionary[i][0])) message = dictionary[i][1] if(message != "") break } // 辞書にあてはまるものがなければ、相槌を打つ if (message == ""){ message = userMessage + "だってにゃ? それはいいにゃ〜!" } // 「応答」メッセージ用のAPI URL var url = 'https://api.line.me/v2/bot/message/reply'; UrlFetchApp.fetch(url, { 'headers': { 'Content-Type': 'application/json; charset=UTF-8', 'Authorization': 'Bearer ' + ACCESS_TOKEN, }, 'method': 'post', 'payload': JSON.stringify({ 'replyToken': replyToken, 'messages': [{ 'type': 'text', 'text': message, }], }), }); return ContentService.createTextOutput(JSON.stringify({'content': 'post ok'})).setMimeType(ContentService.MimeType.JSON); } /* *************** 20: 送信系(Push) *************** */ // 「Line」シートの「A2」のテキストを、全員に送信する function sendStaticMessageToAll(){ var message = sheet.getRange("A2").getValue(); sendMessageToAll(message) } // 登録されている人全員に送信する function sendMessageToAll(message) { // userIdをすべて取得する var sheet = SpreadsheetApp.getActive().getSheetByName('LineUsers') var userIdsArray = sheet.getRange(2, 4, sheet.getLastRow() - 1, 1) // D2:Dx var userIds = userIdsArray.getValues() // [['aaaaa'],['bbbbb'],['ccccc']] // 各userIdに対してメッセージを送信していく for(var i = 0; i < userIds.length; i++) { userId = userIds[i][0] pushLine(message, userId) } } // Lineに「投稿」する function pushLine(message, user_id){ // メッセージ「送信」用のAPI URL var url = 'https://api.line.me/v2/bot/message/push'; UrlFetchApp.fetch(url, { 'headers': { 'Content-Type': 'application/json; charset=UTF-8', 'Authorization': 'Bearer ' + ACCESS_TOKEN, }, 'method': 'post', 'payload': JSON.stringify({ 'to': user_id, 'messages': [{ 'type': 'text', 'text': message, }], }), }); } /* *************** 30: その他の関数 *************** */ /* Line系 */ // ユーザーの情報をLineAPIで取得し、SpreadSheetに保存する function stockUserData(userId){ var url = 'https://api.line.me/v2/bot/profile/' + userId; var response = UrlFetchApp.fetch(url,{ 'headers': { 'Authorization' : 'Bearer ' + ACCESS_TOKEN, } }) var userData = JSON.parse(response) // APIから得られた情報から、要素を抽出 var name = userData.displayName var pictureUrl = userData.pictureUrl var statusMessage = userData.statusMessage // シートに出力する var sheet = SpreadsheetApp.getActive().getSheetByName('LineUsers') var range = sheet.getRange(sheet.getLastRow() + 1, 2, 1, 3) // Bx:Dx range.setValues([[name, pictureUrl, userId]]) }