Googleスプレッドシート -Google Apps Scriptでの自動化TIPS-

■授業の内容

・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("家計簿２")
  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)
}

前回学んだ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]])
}