LANG SELRCT

Apps Script Reference  (Create: Create new Spreadsheet | Create new Apps Script

Monday, October 30, 2017

シート内でキーワードに一致する値を探す(線形探索)

指定した値がシートの何行目にあるのか探したい場合
いくつか方法はありますが
今回は一行目から順番に見て行き
一致する値があればログに出すというコードを書いてみました

このように与えられたデータを
上から順番に見て行く
という探し方を
『線形探索』といいます


例えばこんなシートがあるとして
「東京都」は何行目にあるのかプログラムでみつけたい

見つけたらこんな感じでログに出したい
ということを実現するコードです


コード.gs
function linear_search() {
  var keyword = "東京都";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var range = sh.getRange("A:A");
  var values = range.getValues();
  var index = 0;
  for (var i = 0; i < values.length; i++) {
    if (values[i][0] == keyword) {
      index = i;
      break;
    } else {}
  }
  Logger.log(keyword + "は" + (index + 1) + "行目です")
}
意訳.gs
この処理は以下を実行する
キーワードを設定する
現在開いているスプレッドシートを取得する
現在開いているシートを取得する
A:Aの範囲を指定して
すべての値を取得する
indexの初期値を0にして
一行目の値から順に以下を繰り返す
もし値がキーワードと一致したら
indexに繰り返した回数を入れて
繰り返しの処理から抜ける
もし一致しなければ何もせず次の値を見に行く

ログに キーワードは何行目 と出す


今回使ったデータ


検索ワード

北海道
青森県
岩手県
宮城県
秋田県
山形県
福島県
茨城県
栃木県
群馬県
埼玉県
千葉県
東京都
神奈川県
新潟県
富山県
石川県
福井県
山梨県
長野県
岐阜県
静岡県
愛知県
三重県
滋賀県
京都府
大阪府
兵庫県
奈良県
和歌山県
鳥取県
島根県
岡山県
広島県
山口県
徳島県
香川県
愛媛県
高知県
福岡県
佐賀県
長崎県
熊本県
大分県
宮崎県
鹿児島県
沖縄県




シート内でキーワードに一致する値を探す(二分探索)

指定した値がシートの何行目にあるのか探したい場合
上から順に見ていく方法を『線形探索』といいますが
今回は探す範囲を半分ずつ絞って
一致する値があればログに出すというコードを書いてみました

この探し方を『二分探索(binary search)』といいます
※探索対象のデータは昇順・降順で順番になっている前提です

線形探索の方法はこちら


例えばこんなシートがあるとして
「s」は何行目にあるのかプログラムでみつけたい

見つけたらこんな感じで探したプロセスをログに出したい


ということを実現するコードです

このプロセスは探す範囲を半分ずつ絞っていくので
まずはaからzの真ん中の位置にあるmを見てmはsよりも小さい(昇順の順番が)ので
次の探索はmより下を見に行き
mからzの範囲の真ん中の位置にあるtはsよりも大きいので
次の探索はtよりも上を見に行くといった感じで
sにたどり着くまで範囲を半分に絞っていきます

コード.gs
function binarysearch() {
  var keyword = "s";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var range = sh.getRange("A:A");
  range.sort([{column: 1, ascending: true}]);
  var values = range.getValues();
  var head = 0;
  var tail = values.length - 1;
  var index = 0;
  while (head <= tail) {
    var middle = Math.floor((head + tail) / 2);
    Logger.log(values[middle][0])
    if (values[middle][0] == keyword) {
      index = middle;
      break;
    } else if (values[middle][0] < keyword) {
      head = middle + 1;
    } else {
      tail = middle - 1;
    }
  }
  Logger.log(index + 1)
}
意訳.gs
この処理は以下を実行する
キーワードを設定する
現在開いているスプレッドシートを取得する
現在開いているシートを取得する
A:Aの範囲を指定して
A列を昇順にソートして
すべての値を取得する
探索範囲の先頭の初期値を0にして
探索範囲の末尾の初期値はA列の値の個数-1にする
indexの初期値は0にする
先頭の数値が末尾の数値以下である限り
対象範囲の中央にある値を
ログに出し
もしその中央の値が探している値なら
indexに中央の数値を入れて
処理から抜ける
もし中央の値が探している値よりも小さいなら
次に探す範囲の先頭は中央の値の下からにする
それ以外(中央の値が探している値よりも大きい)なら
次に探す範囲の末尾は中央の値のひとつ上にする


ログにキーワードの行数を出す


今回使ったデータ


alphabet
a
b
c
d
e
f
g
h
i
j
k
l
m1回目の探索時の半分の位置にあるmはsよりも小さいので次はmから下を見る↓1
1
n
2
o
p3回目の探索時の半分の位置にあるpはsよりも小さいので次はpから下を見る↓3
3
q
r4回めの探索時の半分の位置にあるrはsよりも小さいので次はrから下を見る↓4
4
s5回目の探索時の半分の位置にあるsが探している値なのでこの行数をログに出す←55
t2回目の探索時の半分の位置にあるtはsよりも大きいので次はtから上を見る↑2
u
v
w
x
y
z

Sunday, October 29, 2017

トリガー(不要になったもの)をスクリプトで削除する

プロジェクト内のすべてのトリガーを削除する場合

function deleteTrigger() {
  var allTriggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < allTriggers.length; i++) {
    ScriptApp.deleteTrigger(allTriggers[i]);
  }
}


triggerのIdを指定して削除する場合

function deleteTrigger(triggerId) {
  var allTriggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < allTriggers.length; i++) {
    if (allTriggers[i].getUniqueId() == triggerId) {
      ScriptApp.deleteTrigger(allTriggers[i]);
      break;
    }
  }
}


triggerの名前を指定して削除する場合

function deleteTrigger(functionName) {
  var allTriggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < allTriggers.length; i++) {
    if (allTriggers[i].getHandlerFunction() == functionName) {
      ScriptApp.deleteTrigger(allTriggers[i]);
      break;
    }
  }
}


おまけ


試したこと

シートにトリガーを登録する

  • スクリプトでトリガーを作成する
  • シートにトリガーの日付けとIDを書き込む


トリガーを削除する

  • 登録されたトリガーの日付けが現在日時よりも古ければ削除する
  • シートに書き込まれたデータも削除する

本当は同じことをPropertiesServiceで実装しようとしましたが
いろいろと試しているうちに頭がくしゃくしゃになってきたので
別の方法としてシートに書き出す方法から試してみることにしました

ちなみにトリガーで実行するmyFunctionはログを出すだけです


コード.gs
var SS_URL = "URL";

function myFunction() {
  Logger.log("success");
}

function createTrigger() {
  var date = new Date("2017/10/29/19:54");
  var trigger = ScriptApp.newTrigger('myFunction')
    .timeBased()
    .at(date)
    .create();
  var trigger_id = trigger.getUniqueId();
  set_values(date, trigger_id);
}

function set_values(date, trigger_id) {
  var ss = SpreadsheetApp.openByUrl(SS_URL);
  var sh = ss.getSheets()[0];
  var row = get_last_data_row(ss, sh);
  sh.getRange(row, 1).setValue(date);
  sh.getRange(row, 2).setValue(trigger_id);
}

function get_past_data() {
  var ss = SpreadsheetApp.openByUrl(SS_URL);
  var sh = ss.getSheets()[0];
  var row = get_last_data_row(ss, sh);
  var past_data = [];
  for (var i = 0; i < row; i++) {
    var date = sh.getRange((i + 2), 1).getValue();
    var id = sh.getRange((i + 2), 2).getValue();
    if (date < new Date()) {
      past_data.push(id);
      sh.deleteRow(i + 2);
    }
  }
  return past_data;
}

function deleteTrigger() {
  var past_data = get_past_data();
  var allTriggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < allTriggers.length; i++) {
    for (var j = 0; j < past_data.length; j++) {
      if (allTriggers[i].getUniqueId() == past_data[j]) {
        ScriptApp.deleteTrigger(allTriggers[i]);
      }
    }
  }
}

function get_last_data_row(ss, sh) {
  var values = sh.getRange("A:A").getValues();
  for (var i = values.length - 1; i >= 0; i--) {
    if (values[i] != "") {
      break;
    }
  }
  var last_row = i + 2;
  return last_row;
}
意訳.gs
スプレッドシートのURLを設定する

この処理は以下を実行する
設定したテキストをログに出す


この処理は以下を実行する
新しい日付けを設定する
指定した処理(例ではmyFunction)をトリガーに設定する
時間主導型で
指定した日付けで
トリガーを作成する
作成したトリガーのIDを取得する
set_valuesに日付けとトリガーのIDを渡す


この処理は以下を実行する
スプレッドシートを取得する
0番目のシートを取得する
データを入力する行を取得する
その行の1列目のに日付けを入力する
その行の1列目にトリガーのIDを入力する


この処理は以下を実行する
スプレッドシートを取得する
0番目のシートを取得する
データが入力されている最終行(の次)を取得する
past_dataという入れ物を用意する
最終行になるまで2行目から以下を繰り返す
1列目のi+2行目の値を取得する(日付)
2列目のi+2行目の値を取得する(trigger_id)
もし日付けが現在日時よりも前なら
past_dataにtrigger_idを追加して
i+2行目を削除する


past_dataを返す


この処理は以下を実行する
past_dataを取得する
現在のプロジェクトのトリガーをすべて取得する
その数の分だけ以下を繰り返す
past_dataに入っている個数分以下を繰り返す
もしallTriggersのi番目とpast_dataのj番目のトリガーIDが同じなら
そのトリガーを削除する





この処理は以下を実行する
指定されたシートのA列の値をすべて取得する
一番下から上に向かって以下を繰り返す
もし値が空でなければ
繰り返しから抜ける


値が入っている最終行の次の行番号を
返す


試してみる


トリガーを登録する


createTrigger()の中の
var date = new Date("2017/10/29/19:54");
この日時を変えていくつか登録します

例として以下の日時を設定して
createTrigger()を5回実行します

  1. 2017/10/29 20:51
  2. 2017/10/29 20:52
  3. 2017/10/29 20:53
  4. 2017/10/30 20:00
  5. 2017/10/31 20:00


編集>現在のプロジェクトのトリガーを選択します



createTrigger()を実行して登録した5つのトリガーが
現在のプロジェクトのトリガーに表示されていると思います



var SS_URL = "URL";
で設定したスプレッドシートを開くと
A列に日付け
B列にトリガーのID
が書き込まれています


トリガーを削除する


deleteTrigger()を実行すると
現在日時よりも前に設定さているトリガーが削除されます




シートからも削除されます




トリガーをスクリプトで制御する

スクリプトでトリガーを制御する方法の覚書その1です

ここで言う「トリガー」とは
Google Apps Scriptで書いた処理を
指定した日時に自動的に実行する機能のことです

スクリプトエディタのUI上からトリガーを設定する方法はこちら

以下のコードを実行すると
1分ごとにmyFunctionを実行するトリガーが追加され
その追加したトリガーを65秒ごとに削除します
(削除しないと永遠にトリガーが増えていく)

これはUI上で1分おきに実行するトリガーを設定するのとほぼ変わらないので
実用的なコードではないですが
トリガーのIDを指定して削除する例を書きたくて書いてみました



コード.gs
function createTimeDrivenTriggers() {
  var trigger = ScriptApp.newTrigger('myFunction')
    .timeBased()
    .everyMinutes(1)
    .create();
  var trigger_id = trigger.getUniqueId();   
  Utilities.sleep(65000);
  deleteTrigger(trigger_id);
}  

function deleteTrigger(triggerId) {
  var allTriggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < allTriggers.length; i++) {
    if (allTriggers[i].getUniqueId() == triggerId) {
      ScriptApp.deleteTrigger(allTriggers[i]);
      break;
    }
  }
}

function myFunction() {
  //実行したい処理
}
意訳.gs
この処理は以下を実行する
指定した処理(例ではmyFunction)をトリガーに設定する
時間主導型で
指定した分毎(例では1分毎)に実行する
トリガーを作成する
作成したトリガーのIDを取得する
指定した秒数待機する(例では65秒)
deleteTriggerにトリガーのIDを渡す



この処理は以下を実行する
現在のプロジェクトのすべてのトリガーを取得する
トリガーの数だけ以下を繰り返す
triggerIdを見つけたら
そのトリガーを削除する
繰り返しの処理から抜ける



この処理は以下を実行する
実行したい処理を書く


おまけ


IDや時間の設定が本当に意図したとおりになっているのか
ログを出してみました



コード.gs
function createTimeDrivenTriggers() {
  var start = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd/HH:mm:ss');
  var trigger = ScriptApp.newTrigger('myFunction')
    .timeBased()
    .everyMinutes(1)
    .create();
  var trigger_id = trigger.getUniqueId();    
  Logger.log(trigger.getUniqueId());
  Utilities.sleep(65000);
  deleteTrigger(trigger_id);
  var end = Utilities.formatDate(new Date(), 'Asia/Tokyo', 'yyyy/MM/dd/HH:mm:ss');
  Logger.log(["removed: " + trigger.getUniqueId(), "created_at: " + start, "end_at: " + end]);
}

function deleteTrigger() {
  var allTriggers = ScriptApp.getProjectTriggers();
  for (var i = 0; i < allTriggers.length; i++) {
    if (allTriggers[i].getUniqueId() == triggerId) {
      ScriptApp.deleteTrigger(allTriggers[i]);
      break;
    }
  }
}

function myFunction() {
  //実行したい処理
}
意訳.gs
この処理は以下を実行する
現在日時を取得する
指定した処理(例ではmyFunction)をトリガーに設定する
時間主導型で
指定した分毎(例では1分毎)に実行する
トリガーを作成する
作成したトリガーのIDを取得する
それをログに出す
指定した秒数待機する(例では65秒)
deleteTriggerにトリガーのIDを渡す
現在時刻を取得する
トリガーIDと開始時間と終了時間をログに出す


この処理は以下を実行する
現在のプロジェクトのすべてのトリガーを取得する
トリガーの数だけ以下を繰り返す
triggerIdを見つけたら
そのトリガーを削除する
繰り返しの処理から抜ける




この処理は以下を実行する
実行したい処理を書く




参考
https://developers.google.com/apps-script/guides/triggers/installable

LINE BOTでメッセージを送る(push message)


送信されたメッセージに対して応答するのではなく
LINE BOTの方からトークルームにメッセージを送ることができます
それを「push message」と言います

公式リファレンスでここに書かれていることを実際にやってみます

LINE BOTをつくる方法はこちらの記事にまとめました

上の作り方通りにやれば
Available featuresに「PUSH_MESSAGE」が含まれていると思います



↑ここがREPLY_MESSAGEだけだと
push messageは使えません
新しくLINE BOTを作成してみてください


以下のコードは
Google Apps Scriptのpush_message()を実行したら
トークルームにhelloと送る例です

コードの中のUSER_IDは
LINE BOTの作り方のChannel settingsの一番下に記載されている
Your user IDです


コード.gs
var CHANNEL_ACCESS_TOKEN = "TOKEN"; 
var USER_ID = "ID";

function push_message() {
  var postData = {
    "to": USER_ID,
    "messages": [{
      "type": "text",
      "text": "hello",
    }]
  };

  var url = "https://api.line.me/v2/bot/message/push";
  var headers = {
    "Content-Type": "application/json",
    'Authorization': 'Bearer ' + CHANNEL_ACCESS_TOKEN,
  };

  var options = {
    "method": "post",
    "headers": headers,
    "payload": JSON.stringify(postData)
  };
  var response = UrlFetchApp.fetch(url, options);
}

意訳.gs
アクセストークンを設定する
ユーザIDを設定する

この処理は以下を実行する
postDataの
toにUSER_IDを設定し
messagesの
typeを設定して
textを設定する



LINEのpushAPIのurlを設定する
headersの
Content-Typeを設定して
Authorizationを設定する


optionsの
methodをpostに設定して
headersを設定して
payloadにはpostDataをJSONに変換したものを設定する

optionをつけてLINEのpush message APIをたたく




push messageを送ってみる


コードを保存後
実行>関数を実行>push_message
を選択します


すると以下のようにトーク画面にメッセージが飛んできます


Saturday, October 28, 2017

LINE BOTで「リッチメニュー」を表示してみる

Google Apps ScriptでLINE BOTを作る過程で、いろいろと管理画面を触っていた時に
「リッチメニュー」を表示する方法を知ったので、今回はその方法について書こうと思います


上の画面のようにトーク画面にメニューを表示することができます
これを「リッチメニュー」というようです


LINE BOTをつくる方法はこちらの記事にまとめました

記事の中の「Auto-reply messages」を使えるように「enabled」にしておきます




「リッチメニュー」を使うために
まずはこちらの管理画面にアクセスします
https://admin-official.line.me/

アカウント一覧の中から、設定を変更したいタイトルを選択します


リッチコンテンツ作成をクリックします



新規作成をクリックします



設定はデフォルトのままで「デザインガイド」をクリックします



デザインガイドを見ると
全体は2500px ✕ 1686pxで
ひとつのエリアは833px ✕ 843pxのようです
(CとFは834px ✕ 843px)




というわけでガイドのサイズ通りの画像を作ってみます


実際に作った画像はこちら↓



アップロードをクリックして
作った画像を選択します

アップロードした画像の6つのエリアに
それぞれ応答メッセージを設定します

まずは左上の「入力」エリアにキーワードを設定してみます
(キーワードを設定する方法はこちら

「キーワード選択」をクリックします




設定するキーワードの「選択」ボタンをクリックします



設定されるとこうなります


次に上段の中央のエリアにURLを設定してみます
以下の例ではGoogle検索のURLを設定しています




次に右上のエリアにテキストを設定してみます
タップしたら こんにちは と返します




同じように下の3つのエリアも任意で設定をします


保存する前に以下の設定をします

表示設定:反映する
表示期間:任意の期間を設定
タイトル:任意タイトルを設定
メニュー初期表示:表示する



設定が終わったら保存します


保存後、トーク画面を開くと設定した画像が表示されます


それぞれのエリアを押すと設定した反応が返ってきます


FYI すでにこういう記事がありました↓
https://developers.line.me/ja/docs/messaging-api/using-rich-menus/
http://manual-at.line.me/archives/1020056348.html

LINE BOTで「キーワード応答メッセージ」を使う



LINE BOTにはプログラミング不要で応答メッセージを設定する機能があります

「キーワード応答メッセージ」を設定して自動でメッセージを返してみましょう

設定したキーワードが入力されると設定した応答メッセージが返ってきます


LINE BOTをつくる方法はこちらの記事にまとめました

記事の中の「Auto-reply messages」を使えるように「enabled」にしておきます



↑でenabledにしたら
こちらの管理画面にアクセスします
https://admin-official.line.me/

アカウント一覧の中から、設定を変更したいタイトルを選択します


メッセージ>キーワード応答メッセージを選択して
新規作成をクリックします


応答メッセージを設定します
①代表キーワードを設定します
②返すコンテンツを選択します
③返すテキストを設定します
④保存します

以下の例では
helloと入力すると
hiというテキストを返します




キーワードが設定されるとこういう画面になります



実際に試してみましょう
hello
と送ると
hi
と返ってきます


LINE BOTで「画像カルーセル」テンプレートを表示する




テンプレートメッセージの「画像カルーセル」表示してみましょう
カルーセルテンプレート同様
最大5個まで並べられるようです
LINE BOT Image carousel Template messages (max 5 columns)

公式リファレンスでここに書かれていることを実際にやってみます


LINE BOTをつくる方法はこちらの記事にまとめました

以下のコードは、image carouselと入力すると、画像カルーセルテンプレートメッセージが返ってくる例です

postback, message, uriのボタンを押すと
それぞれ設定したactionが実行されます
https://developers.line.me/en/docs/messaging-api/reference/#template-messages

コード.gs
var CHANNEL_ACCESS_TOKEN = "TOKEN"; 
 
function doPost(e) {
  var contents = e.postData.contents;
  var obj = JSON.parse(contents);
  var events = obj["events"];
  for (var i = 0; i < events.length; i++) {
    if (events[i].type == "message") {
      reply_message(events[i]);
    } else if (events[i].type == "postback") {
      post_back(events[i]);
    }
  }
}

function reply_message(e) {
  var input_text = e.message.text;
  if (input_text == "image carousel") {
    var postData = {
      "replyToken": e.replyToken,
      "messages": [{
        "type": "template",
        "altText": "this is a image carousel template",
        "template": {
          "type": "image_carousel",
          "columns": [{
              "imageUrl": "https://~.png",
              "action": {
                "type": "postback",
                "label": "postback",
                "data": "get url"
              }
            },
            {
              "imageUrl": "https://~.png",
              "action": {
                "type": "message",
                "label": "message",
                "text": "https://developers.line.me/en/docs/messaging-api/reference/#image-carousel"
              }
            },
            {
              "imageUrl": "https://~.png",
              "action": {
                "type": "uri",
                "label": "uri",
                "uri": "https://developers.line.me/en/docs/messaging-api/reference/#image-carousel"
              }
            }
          ]
        }
      }]
    };
  }
  fetch_data(postData);
}

function post_back(e) {
  var data = e.postback.data;
  if (data == "get url") {
    var replay_text = "https://developers.line.me/en/docs/messaging-api/reference/#image-carousel";
  }
  var postData = {
    "replyToken": e.replyToken,
    "messages": [{
      "type": "text",
      "text": replay_text
    }]
  };
  fetch_data(postData);
}

function fetch_data(postData) {
  var options = {
    "method": "post",
    "headers": {
      "Content-Type": "application/json",
      "Authorization": "Bearer " + CHANNEL_ACCESS_TOKEN
    },
    "payload": JSON.stringify(postData)
  };
  UrlFetchApp.fetch("https://api.line.me/v2/bot/message/reply", options);
}
意訳.gs
アクセストークンを設定する

この処理は以下を実行する
送信されたデータの内容を取得し
そのJSONをオブジェクトに変換し
eventsを取得する
eventsの数だけ以下を繰り返す
もしtypeが message なら
reply_messageに渡す
もしtypeが postback なら
post_backに渡す




この処理は以下を実行する
入力されたtextを取得して
もしそれが carousel なら
postDataに
replyTokenを設定し
messagesの
typeをtemplateに設定し
altTextを設定し
templateの
typeをimage_carouselに設定し
columnsを以下のように設定する
1つ目のcolumnのimageUrlを設定する
actionの
typeを設定し
labelを設定し
dataを設定する



2つ目のcolumnのimageUrlを設定する
actionの
typeを設定し
labelを設定し
textを設定する



3つ目のcolumnのimageUrlを設定する
actionの
typeを設定し
labelを設定し
uriを設定する







fetch_dataにpostDataを渡す


この処理は以下を実行する
受け取ったpostbackからdataを取得し
もしdataがget urlだったら
replay_textに指定した値を入れる

postDataに
replyTokenを設定し
messagesの
typeをtextに設定し
textにreplay_textを設定する


fetch_dataにpostDataを渡す


この処理は以下を実行する
optionsに
methodを設定し
headersに
Content-Typeを設定し
Authorizationを設定し

payloadにはpostDataをJSONに変換したものを設定する

optionをつけてLINEのmessage reply APIをたたく


Latest post

Extracting data from Google Sheets with regular expressions

Introduction Regular expressions are a powerful tool that can be used to extract data from text.  In Google Sheets, regular expressions ca...