LANG SELRCT

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

Thursday, November 23, 2017

指定した日付けの週初と週末を取得する


今週の日曜日と土曜日の日付けを

11/19 - 11/25

の形で取得しようとした時に書いたコードです




コード.gs
function week_start_and_end() {
  var today = new Date();
  var this_year = today.getYear();
  var this_month = today.getMonth();
  var date = today.getDate();
  var day_num = today.getDay();
  var this_sunday = date - day_num;
  var this_saturday = this_sunday + 6;
  var start_date = new Date(this_year, this_month, this_sunday);
  start_date = format_date(start_date);
  var end_date = new Date(this_year, this_month, this_saturday);
  end_date = format_date(end_date);
  var target_week = start_date + " - " + end_date;
  Logger.log(target_week);
}

function format_date(date){
  return Utilities.formatDate(date, 'Asia/Tokyo', 'MM/dd');
}
意訳.gs
この処理は以下を実行する
現在日時を取得
年を取得
月を取得
日付けを取得
曜日の番号を取得→ 0 は日曜日で 1は月曜日~ 6 は土曜日
今日の日付から今日の曜日番号を引いて今週の日曜日の日付を取得
今週の日曜日に6を足して今週の土曜日の日付を取得
今週の日曜日をnew Date(年, 月, 日)にしてから
MM/ddの形式にする
今週の土曜日をnew Date(年, 月, 日)にしてから
MM/ddの形式にする
今週の日曜日 - 今週の土曜日の形にして
ログに出す


この処理は以下を実行する
渡されたdateをMM/ddの形にフォーマットして返す




var today = new Date("2017/11/1");
などとすると指定した日付けの週初と週末を取得できます


Utilities.formatDate(date, 'Asia/Tokyo', 'MM/dd')
はGoogle Apps Scriptのmethodです



参考
https://developers.google.com/apps-script/reference/utilities/utilities#formatDate(Date,String,String)

今日は今月の何週目の何曜日かを求める



今日が今月の何週目の何曜日であるかを出そうとした時に調べて書いたコードです


今週の土曜日の日付けを取得して
それを7で割って今月の何週目かを出し
曜日は曜日番号から取得する例です


コード.gs
function get_week_num() {
  var seven_days = "日月火水木金土";
  var today = new Date();
  var this_year = today.getYear();
  var this_month = today.getMonth() + 1; 
  var date = today.getDate();
  var day_num = today.getDay(); 
  var day = seven_days[day_num];
  var this_sunday = date - day_num;
  var this_saturday = this_sunday + 6;
  var this_week = Math.ceil(this_saturday / 7);
  var today_format = Utilities.formatDate(today, 'Asia/Tokyo', 'yyyy/MM/dd');
  var result = today_format + "は、" + this_year + "年" + this_month + "月" + this_week + "週目の" + day + "曜日";
  Logger.log(result);
}
意訳.gs
この処理は以下を実行する
曜日の一覧を用意する
現在日時を取得
年を取得
月を取得(取得した月は0始まりなので+1)
日付けを取得
曜日の番号を取得→ 0 は日曜日で 1は月曜日~ 6 は土曜日
曜日を取得(seven_days[0]は日曜日)
今日の日付から今日の曜日番号を引いて今週の日曜日の日付を取得
今週の日曜日に6を足して今週の土曜日の日付を取得
今週の土曜日の日付を7で割って余りがあれば繰り上げる(もう一週存在しているから)
todayをフォーマットする(GoogleApps Script)
取得した値を任意の文字列と結合して
ログに出す




具体例


たとえば、2017/11/23木曜日を例にすると


コードの計算はこうなる
var seven_days = "日月火水木金土";
var today = 2017/11/23
var this_year = 2017
var this_month = 11
var date = 23
var day_num = 4
var day = seven_days[4] → 木
var this_sunday = 23 - 4 → 19
var this_saturday = 19 + 6 → 25
var this_week = Math.ceil(25 / 7) → 3.57...を繰り上げると=4
var today_format = 2017/11/23
var result = 2017/11/23 + "は、" + 2017 + "年" + 11 + "月" + 4 + "週目の" + 木 + "曜日";

出力されるログは最上部の画像の通り

データが入力されている最終行を取得する


テータ(値)が入力されている最終行を取得したい時にぶつかった壁があり
それを乗り越えた時に書いたコードです

以下の3つのパターンについて書きます

  1. シート内で値が入力されている最終行を取得する
  2. 特定の範囲で値が入力されている最終行を取得する
  3. 特定の列で値が入力されている最終行を取得する


例として以下のようなシートを使います



1. シート内で値が入力されている最終行を取得する


これが1番簡単で
シートを指定して.getLastRow()で取得できます


コード.gs
function get_last_row(){
  var last_row = SpreadsheetApp.getActiveSheet.getLastRow();
  Logger.log(last_row);
}
意訳.gs
この処理は以下を実行する
今開いているシートの値が入っている最終行を取得して
ログに出す



ログを見ると6(行目)が返ってきています
これはA列で値が入っている最終行の6行目を返しています




2. 特定の範囲で値が入力されている最終行を取得する




次にB列とC列の範囲内で
データが入力されている最終行を取得するコードの例です


コード.gs
function get_last_row() {
  var sh = SpreadsheetApp.getActiveSheet();
  var values = sh.getRange("B:C").getValues();
  for1:
  for (var i = values.length - 1; i >= 0; i--) {
    for(var j = 0; j < values[i].length; j++){
      if(values[i][j] != ""){
        break for1;
      }
    }
  }
  var row = i + 1;
  Logger.log(row);
}
意訳.gs
この処理は以下を実行する
今開いているシートの
B列からC列の範囲にある値をすべて取得して
1つ目のfor文にfor1というラベルを付けて
1つ目のfor文で一番下の行から上に向かって以下を繰り返す
2つ目のfor文で対象行内の列の数だけ(つまりセルの数)以下を繰り返す
セルの値が空でなければ
1つ目のfor文から抜ける

  
 
iは0から始まっているので i に1を足したものを行数として
ログに出す



ログを見ると4(行目)が返ってきています
これはB列で値が入っている最終行の4行目を返しています




3. 特定の列で値が入力されている最終行を取得する




最後はC列で
データが入力されている最終行を取得するコードの例です


コード.gs
function get_last_row() {
  var sh = SpreadsheetApp.getActiveSheet();
  var values = sh.getRange("C:C").getValues();
  for (var i = values.length - 1; i >= 0; i--) {
    if (values[i] != "") {
      break;
    }
  }
  var last_row = i + 1;
  Logger.log(last_row);
}
意訳.gs
この処理は以下を実行する
今開いているシートの
C列の値をすべて取得して
一番下の行から上に向かって以下を繰り返す
セルの値が空でなければ
for文から抜ける


iは0から始まっているので i に1を足したものを行数として
ログに出す



ログを見ると3(行目)が返ってきています
これはC列で値が入っている最終行の3行目を返しています







補足

上記をアレンジして、対象の列を列番号(Aは1, Bは2, Cは3...)で指定して、最終行の次の行を取得したい場合


コード.gs
function get_next_row(){
  var sh = SpreadsheetApp.getActiveSheet();
  var col = 3;
  var next_row = get_last_row(sh, col) + 1;
  Logger.log(next_row);
}

function get_last_row(sh, col) {
  var start_row = 1;
  var num_cols = 1;
  var sh_last_row = sh.getLastRow();
  var values = sh.getRange(start_row, col, sh_last_row, num_cols).getValues();
  for (var i = values.length - 1; i >= 0; i--) {
    if (values[i] != "") {
      break;
    }
  }
  var last_row = i + 1;
  return last_row;
}
意訳.gs
この機能がやること
対象のシートを取得する
対象の列を番号で指定する
その列に入力されている最終行を取得して1足して
ログに出す


この機能がやること
開始行をしていして
対象の列数をしていして
シート内でデータが入力されている最終行を取得して
範囲内の値を取得して
値の数だけシートの下から順番に以下を繰り返す(配列のindexを表すiは0始まりなので、最終行の数から1引いた数が配列内の最後のindex番号)
値が空じゃなかったら
for分から抜ける


iは配列内の要素のindex番号で、0始まりなので、実際の行数は+1して
返す




関連記事
データが入力されている最終行の次の行へカーソルを移動する



参考
https://developers.google.com/apps-script/reference/spreadsheet/sheet#getLastRow()

シートの1行目(ヘッダ行)の値を取得する


以下のようなデータがあるとき
1行目の値をすべて取得してログに出す
ということをやった時に書いたコードです





コード.gs
function get_headers() {
  var sh = SpreadsheetApp.getActiveSheet();
  var last_col = sh.getLastColumn();
  var range = sh.getRange(1, 1, 1, last_col);
  var headers = range.getValues();
  Logger.log(headers);
}
意訳.gs
この処理は以下を実行する
今開いているシートの
値が入っている最終列を取得して
1行目の1列目から1行分、last_col列までの
値を取得して
ログに出す



Sunday, November 19, 2017

Slack BOTでMessage Buttonsを使う


Slackの特定のchannelで
helloと投稿したら「Good」と「so so」のボタンが表示され
クリックしたボタンに応じた処理をしてメッセージを返す

ということを試した時の手順です

もっと簡単なやり方がありそうな気もしますが
意図した仕組みを実現できた方法を書いていきます



事前準備
  • Slack App側の設定をする
    • Appを作成する
    • Incoming Webhookを設定する
    • Interactive Componentsを設定する
    • Outgoing Webhookを設定する

実行するプロセス
  1. Trigger Wordを投稿する
  2. BOT(Outgoing WebHook)がキャッチして.gsにデータを送る
    • ボタン付きのメッセージをSlackに返す
  3. 任意のボタンをクリックする
  4. BOT(Interactive Components)がキャッチして別の.gsにデータを送る
    • クリックされたボタンに応じてSlackにメッセージを返す



今回使うコード.gsは2つあります
それぞれやっていることと設置する場所が異なります

  • コード1.gs
    • ボタンを表示するためのコードです
    • これをWebアプリにしたURLをOutgoing WebhookのRequest URLに設定する
  • コード2.gs
    • クリックされたボタンに応じて処理するためのコードです
    • これをWebアプリにしたURLをInteractive ComponentsのRequest URLに設定する


Webアプリにする方法はこちら



コード1.gs
function doPost(e) {
  var INCOMING_WEBHOOK_URL = "URL";
  var data = {
    "text": "choices",
    "attachments": [{
      "text": "how are you?",
      "fallback": "fallback message",
      "callback_id": "callback_button",
      "color": "#3AA3E3",
      "attachment_type": "default",
      "actions": [{
          "name": "feeling",
          "text": "Good",
          "type": "button",
          "value": "Good"
        },
        {
          "name": "feeling",
          "text": "so so",
          "type": "button",
          "value": "so so"
        }
      ]
    }]
  }
  var params = {
    'method': 'post',
    'payload': JSON.stringify(data)
  };
  UrlFetchApp.fetch(INCOMING_WEBHOOK_URL, params);
}
意訳.gs
この機能は以下を実行する
作成したincoming web hookのurlを設定する
dataを作る
textを設定する
attachmentsの
textを設定
fallbackを設定
callback_idを設定
colorを設定
attachment_typeを設定
actionsの1つ目の
nameを設定
textを設定
typeをbuttonに設定
valueを設定

2つ目のactionの
nameを設定
textを設定
typeをbuttonに設定
valueを設定




paramsを作る
methodをpostに設定
payloadを設定

paramsを付けてURLをたたく




コード2.gs
function doPost(e) {
  var payload = JSON.parse(e["parameter"]["payload"]);
  var value = payload["actions"][0]["value"];
  var link = "<https://www.google.co.jp/search?q=" + value + "|" + value;
  if (value == "Good") {
    var text = link + ">が選択されました。Goodの応答文"
  } else {
    var text = link + ">が選択されました。Good以外の応答文"
  }
  var reply = {
    "replace_original": false,
    "response_type": "in_channel",
    "text": text
  };
  var output = ContentService.createTextOutput(JSON.stringify(reply));
  output.setMimeType(ContentService.MimeType.JSON);
  return output
}
意訳.gs
この機能は以下を実行する
送られてきたpayloadのJSONを解析する
payloadの中のactionsの0番目のvalueを取得し
Google検索のリンクを作成し
もしvalueがGoodなら
Goodの応答文をtxtに入れて
それ以外なら
Good以外の応答文をtextに入れて

replyを作る
replace_originalを設定する(もとのメッセージを置き換えるならtrue)
response_typeを設定する
textを設定する

replyをJSONに変換してContentServiceでoutputする
MimeTypeを設定して
outputを返す




1. Slack App側の設定をする


Appを作成する


左サイドのexpand_moreメニューを開いてManage appsをクリック


AppsのBuildをクリック


Start Buildingをクリック


Create an Appをクリック


App Nameに任意の名称を入力
Development Slack Workspaceを選択
右下のCreate Appをクリック


Incoming Webhookを設定する


①Featuresの Incoming Webhooksをクリック
②offをクリックしてonにする


Add New Webhook to Workspaceをクリック



Post toにchannelを設定して
Authorizeをクリック




このWebhook URLが
コード1.gsのINCOMING_WEBHOOK_URLのURLになります




Interactive Componentsを設定する


①Featuresの Interactive Componentsをクリック
②Enable Interactive Componentsをクリック


コード2.gsのWebアプリのURLをここに設定する
下部のEnable Interactive Componentsをクリックする






Outgoing Webhookを設定する


以下の記事に書きました



Trigger Word(s)をhelloに設定しておきます
(上記のリンク先の記事ではbot@になっている箇所です)




試してみる


helloと投稿すると「Good」と「so so」のボタンが表示されます


「Good」を選択すると対応した応答メッセージが返ってきます


「so so」を選択すると対応した応答メッセージが返ってきます




参考

Attaching interactive message buttons
https://api.slack.com/docs/message-buttons

Attaching content and links to messages
https://api.slack.com/docs/message-attachments

A field guide to interactive messages
https://api.slack.com/docs/interactive-message-field-guide

上記公式のリファレンス以外にも多くの記事を参考にさせていただきました

Slack BOTで投稿されたメッセージをスプレッドシートに入力する

bot@hello と投稿した時に
指定したシートにhelloを入力して
そのシートの何行目に入力したかSlackに返す例です

bot@helloと投稿後
SlackにシートのURLと行数が返ってくる

指定したシートにhelloが入力される




  1. Google Apps Scriptで応答用のアプリケーションを作る
  2. Slack側の設定をする

1, 2の方法は以下の記事に書きました



コード.gsの例です



コード.gs
var TOKEN = "取得したtoken"
var POST_URL = 'https://slack.com/api/chat.postMessage';

function doPost(e) {
  var text = e["parameter"]["text"];
  var value = text.replace("bot@", "");
  value = value.replace(/\s/g, "+");
  var ss_url = "URL";
  var ss = SpreadsheetApp.openByUrl(ss_url);
  var sh = ss.getSheets()[0];
  var row = sh.getLastRow() ;
  var next_row = row + 1;
  sh.getRange("A" + next_row).setValue(value);
  reply(ss_url, row);
}

function reply(ss_url, row){
  var payload = {
    token: TOKEN,
    channel: '#general',
    text: ss_url + "\n" + row + "行目に入力しました",
    icon_emoji: ':sunny:',
    username: 'hello bot'
  };
  var params = {
    'method': 'post',
    'payload': payload
  };
  UrlFetchApp.fetch(POST_URL, params);
}
意訳.gs
取得したtokenを設定する
メッセージを送るurlを設定する

この機能は以下を実行する
送信されたテキストを取得して
"bot@"を削除して
空白(スペース、タブ、改行)を+に置き換えて
スプレッドシートのURLを設定して
スプレッドシートを取得して
先頭のシートを取得して
そのシートでデータが入っている最終行を取得して
次の行を選択して
A列のその行に入力して
reply()にss_url, rowを渡す


この機能は以下を実行する
payloadのデータを作る
取得したtokenを設定
投稿するchannelを設定
投稿するtextを設定
アイコンの絵文字を設定
usernameを設定

paramsのデータを作る
methodをpostに設定
payloadのデータを設定

paramsを付けてslackのAPIをたたく





Slack BOTでGoogle検索のリンクを返す



bot@を入力したときだけ
Google検索のリンクを返す例です


  1. Google Apps Scriptで応答用のアプリケーションを作る
  2. Slack側の設定をする

1, 2の方法は以下の記事に書きました



コード.gsの例です



コード.gs
var TOKEN = "取得したtoken"
var POST_URL = 'https://slack.com/api/chat.postMessage';

function doPost(e) {
  var text = e["parameter"]["text"];
  var value = text.replace("bot@", "");
  value = value.replace(/\s/g, "+");
  var link = "https://www.google.co.jp/search?q=" + value;
  var payload = {
    token: TOKEN,
    channel: '#general',
    text: link,
    icon_emoji: ':sunny:',
    username: 'hello bot'
  };
  var params = {
    'method': 'post',
    'payload': payload
  };
  UrlFetchApp.fetch(POST_URL, params);
}
意訳.gs
取得したtokenを設定する
メッセージを送るurlを設定する

この機能は以下を実行する
送信されたテキストを取得して
"bot@"を削除して
空白(スペース、タブ、改行)をすべて+に置き換えて
Google検索のリンクを作り
payloadのデータを作る
取得したtokenを設定
投稿するchannelを設定
投稿するtextにlinkを設定
アイコンの絵文字を設定
usernameを設定

paramsのデータを作る
methodをpostに設定
payloadのデータを設定

paramsを付けてslackのAPIをたたく





Saturday, November 18, 2017

Slack BOTで投稿に応答する



bot@と送るとhelloと返ってくるBOTをコードで実現する例です

コードを使わずに実現する方法は明日書きます
Customize Slack > Slackbot

  1. Google Apps Scriptで応答用のアプリケーションを作る
  2. Slack側の設定をする


1. Google Apps Scriptで応答用のアプリケーションを作る


コード.gsを書いて保存する

コード.gs
var TOKEN = "取得したtoken"
var POST_URL = 'https://slack.com/api/chat.postMessage';

function doPost(e) {
  var payload = {
    token: TOKEN,
    channel: '#general',
    text: "hello",
    icon_emoji: ':sunny:',
    username: 'hello bot'
  };
  var params = {
    'method': 'post',
    'payload': payload
  };
  UrlFetchApp.fetch(POST_URL, params);
}
意訳.gs
取得したtokenを設定する
メッセージを送るurlを設定する

この機能は以下を実行する
payloadのデータを作る
取得したtokenを設定
投稿するchannelを設定
投稿するtextを設定
アイコンの絵文字を設定
usernameを設定

paramsのデータを作る
methodをpostに設定
payloadのデータを設定

paramsを付けてslackのAPIをたたく




公開>ウェブアプリケーションとして導入...をクリックする



プロジェクトバージョン:説明を入力(例ではfirst version)
次のユーザとしてアプリケーションを実行:自分
アプリケーションにアクセスできるユーザー:全員(匿名ユーザーを含む)


許可を確認をクリックする




アカウントを選択する


許可をクリックする


現在のウェブアプリケーションのURLを
Slack側の設定のIntegration SettingsのURLに入力する




2. Slack側の設定をする(Outgoing Webhookの設定)


左サイドのexpand_moreメニューを開いてManage appsをクリック



開いたページで
①Custom Integrationsをクリック
②SearchボックスでOutgoing WebHooksを検索
③検索結果からOutgoing WebHooksを選択


Add Configurationをクリック



Add Outgoing WebHooks integrationをクリック


Integration Settingsで
Channel #general
Trigger Word(s) hello
URL(s)に WebアプリケーションのURL を貼り付ける


Save Settingsをクリック




試してみる


#generalのchannelで
 bot@ と送る
すると hello が返ってきます



おまけ


JSON.stringify(e)でdoPost(e)の e の中身を見る


コード.gs
var TOKEN = "取得したtoken"
var POST_URL = 'https://slack.com/api/chat.postMessage';

function doPost(e) {
  var json = JSON.stringify(e);
  var payload = {
    token: TOKEN,
    channel: '#general',
    text: json,
    icon_emoji: ':sunny:',
    username: 'hello bot'
  };
  var params = {
    'method': 'post',
    'payload': payload
  };
  UrlFetchApp.fetch(POST_URL, params);
}



おまけ2

@hereは<!here>

bot@ ではなく @here をTrigger Word(s)にする場合
そのまま@hereと指定しても反応しなかったので
調べてみると
<!here>
で反応しました




参考
https://api.slack.com/methods/chat.postMessage

Outgoing WebHooks
https://slack.com/apps/A0F7VRG6Q--web

Basic message formatting
https://api.slack.com/docs/message-formatting

Material iconsでアイコンを表示する

GoogleのMaterial iconsを利用する方法を調べた時に書いたコードです


例ではrefreshアイコン(右回転の矢印)を表示しました
その他のアイコンはこちら→https://material.io/icons/


refreshアイコンの場合




利用方法
  1. <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
    をhead内に書く
  2. <i class="material-icons">refresh</i>
    のように利用したいアイコン名をiタグで囲む
※左隣の more vert のようにスペースで区切られている場合は
アンダーバーでつなげて more_vert とすると表示できました



コード.gs
function doGet() {
  return HtmlService.createHtmlOutputFromFile('index');
}
意訳.gs
この機能は以下を実行する
指定したファイルのHTMLを表示する

index.html
<!DOCTYPE html>
<html>
<head>
  <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
</head>
<body>
  <i class="material-icons">refresh</i>
</body>
</html>
意訳.gs
これはHTML5文書です


Material Iconsを利用できるようにする


表示したいアイコンを指定する(ここではrefreshアイコン)





おまけ


アイコンのスタイルを変える


styleタグを追加して
.material_icon というclass名を作って上の画像の様な見た目にするindex.htmの例です
マウスが重なった時に色をsilverにもしています


index.html
<!DOCTYPE html>
<html>
<head>
  <link href="https://fonts.googleapis.com/icon?family=Material+Icons" rel="stylesheet">
</head>
<style>
  .material_icon {
    width: 25px;
    font-size: 25px;
    color: gray;
    background-color: transparent;
    border: solid 2px lightgray;
    vertical-align: middle;
  }
  i:hover {
    color: silver;
  }
</style>
<body>
  <label id="refresh"><i class="material-icons material_icon">refresh</i></label>
</body>
</html>


参考

Material icons
https://material.io/icons/

Material Icons Guide
https://google.github.io/material-design-icons/

Latest post

スプレッドシートA列にある複数のテキストをスライドに追加したい(Google Apps Script)

今回Google Apps Scriptでやりたいこと GoogleスプレッドシートA列にある複数の値を取得して Googleスライドに渡して 図形オブジェクトのテキストとして追加したい ① スプレッドシートのA列に値を入れておく ② Code.gsのinsertNewShape...