こんにちは。キャスレーコンサルティング・TS(テクニカルサービス)部の金巻です。

今回はReact.jsとServiceWorkerに、触れようかと思っていたのですが、
奥深くまとめきらなかったので、いつも通りGoogleApp関連の知っておくと役立つ(かもしれない)TIPSをご紹介いたします。

これまでも技術ブログでは
実務で使えるGoogleAppsScript 入門編
Google Apps Scriptで、心温まる年賀状を作ろう!
Google Apps Scriptとラベルで実現する業務効率化のすゝめ
Google Apps Scriptでカレンダー連動リマインドのすゝめ
Google Apps Scriptで未回答のイベントをリマインド
Google Apps Scriptでイベントの回答状況を一覧化

と過去に取り扱っていますが、
今回はGoogle Apps Script(以下GAS)のメニューへ登録方法や、スプレッドシートの便利な関数についてお伝えします。

<紹介する内容>

・GoogleAppsScriptでメニュー実行
・NETWORKDAY.INTL
・IMPORTHTML
・IMPORTXML
・IMPORTRANGE

GASとは(復習)

メール・ドライブ・スプレッドシート・ドキュメント・カレンダー・マップなど、
様々なGoogle Appsのアプリケーションに、独自の機能を追加するためのスクリプト言語です。

言語仕様は、JavaScriptをベースにしており、JavaScript + Google APIというイメージです。
各APIについては、GAS公式に網羅されています。

GASをなぜメニューに登録したいのか?

そもそも、なぜメニューに登録したいのか?それは実行が楽だからです。
通常、スクリプトの実行にはスクリプトエディタから手動実行するか、トリガーで実行します。
トリガーは自動なので手間ではありませんが、好きなタイミングでいつでも実行したい場合は前者となります。

・スクリプトエディタから実行する場合のアクションは、以下の通りです。
①スプレッドシート画面から、[ツール]→[スクリプトエディタ]を選択
②スクリプトエディタ画面にて、実行したい関数を選択
③実行ボタンを押下する

・メニューから実行する場合のアクションは、以下の通りです。
①スプレッドシート画面から、[メニュー]から実行したい関数を選択

アクションが2個減りました。
特にスクリプトエディタを立ち上げなくて良いのは、大きな利点です。

メニューに登録する

メニューに登録するには、該当のファイルが①開かれた時、②メニューに実行スクリプトメニューを登録する事になります。
①については、onOpen(event)を利用します。
②については、addMenuメソッドを使用します。

具体的事例は以下の通りです。

function onOpen(event){
  var menuitems = [
    {name:'メニューに表示する関数名', functionName:'自分で定義したファンクション名'}
  ];
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  sheet.addMenu('メニューに表示する文字', menuitems);
}

これだけで以下のようにメニューに登場し、関数を実行することが出来ます。

menu

このようにaddMenuを使いメニューに登録することで、スクリプトエディタを開かず実行する事が出来、
Excelのマクロ実行ボタンのように、使うことが出来ます。

スクリーンショット 2018-04-03 19.27.13

この他にアドインとして公開するパターンがありますが、公開には

・OAuth clientのVerificationを申請
・スクリプトエディタからデプロイ
・Add-on AdvisorのPublication Reviewを受ける
・承認されて完了
などいくつかの工程が必要になるため、数人程度であればこの方法で作り、
あとはプロジェクトを共有してしまう方が楽です。

NETWORKDAY.INTL

今度はGASではないのですが、スプレッドシートの便利な関数のTIPSになります。

年間の平日が何日あるかを調べるのに便利なのが、NETWORKDAY.INTLになります。

構文
NETWORKDAYS.INTL(開始日, 終了日, [週末], [休日])

開始日・・・期間の開始日です。

終了日・・・期間の終了日です。

週末・・・[省略可]で週末とみなされる曜日を表す、数値または文字列を指定できます。
文字列の場合、週末の文字列は7文字で表現し、0 と 1 を使用して指定することができます。
日付セットの最初の数字が月曜日、最後の数字が日曜日を表します。0は平日、1 は週末(休日)であることを示します。
例えば、「0000011」と指定すると、土曜日と日曜日が週末になります。
数値の場合、上記の文字列を使用する方法の代わりに、1 つの数字を使用することも可能です。
1〜7 = 土曜日/日曜日を始めとして、1曜日ずつずれる2曜日が週末、
11〜17 = 日曜日を始めとして、1曜日ずつずれる1曜日が週末となります。

休日・・・[省略可]で祝日とみなす日付を含む、範囲または配列定数を指定できます。

実際に、去年一年間の平日数を調べてみましょう。

スクリーンショット 2018-04-03 21.37.39

土日を除いた日数、土日+祝日を除いた日数、水土日+祝日を除いた日数をそれぞれ表示しています。

関数の指定としては、以下のようになっております。

//2017年一年間の土日を除いた日数
=NETWORKDAYS.INTL(C2,D2,1)
//2017年一年間の土日、祝日を除いた日数
=NETWORKDAYS.INTL(C2,D2,1,A2:A18)
//2017年一年間の水土日、祝日を除いた日数
=NETWORKDAYS.INTL(C2,D2,"0010011",A2:A18)

祝日設定が出来るので、どこの国についても対応出来、ちょっとした時に使える関数です。

IMPORTHTML

IMPORTHTML関数を使うと、WEB上の表やリストからデータをインポートすることができます。

構文
IMPORTHTML(URL,クエリ,指数)URL・・・対象とするURLになります。

クエリ・・・”list”か”table”で指定します。

指数・・・何番目のクエリを表示するかを指定します。弊社のサイトからリストを持ってくると・・・

=IMPORTHTML("https://www.casleyconsulting.co.jp/","list",1)

スクリーンショット 2018-04-03 22.15.05

このように、リストからデータを持ってくることが出来ました。
例えばWikipediaから色々な情報を、取ってくることも出来ます。

IMPORTXML

IMPORTHXML関数を使うと、構造化データより情報をインポート出来ます。

構文
IMPORTXML(URL,XPathクエリ)
URL・・・対象とするURLになります。

XPathクエリ・・・構造化データで実行する、 XPath クエリです。

今回は、Googleのサジェストを取得するパターンを紹介します。

以下はA1セルに入力された文字で、googleのサジェストします。

=ImportXML("http://www.google.com/complete/search?hl=en&output=toolbar&q="&A1,"//suggestion/@data")

スクリーンショット 2018-04-04 8.41.01

入力直後Loading中です。

スクリーンショット 2018-04-04 8.41.43

Resultが表示されます。

スクリーンショット 2018-04-04 8.41.57

このようにスプレッドシート内だけで、サジェスト結果を簡単に見ることが出来ます。

IMPORTRANGE

最後に、IMPORTRANGE関数になります。
IMPORTRANGEを知っているかそうでないかで、
スプレッドシートの使い勝手が10%は上がる!(と個人的には思っています)
外部のスプレッドシートの1シートを、別のスプレッドシートに読み込むことが出来る関数です。

読み込む際にはすべてのセルがもちろん、特定の範囲で絞り込んで読み込むことができる関数です。
まずこちらをご覧ください。こちらのシートを参照元とします。

スクリーンショット 2018-04-04 9.02.09

別のスプレッドシートにて、IMPORTRANGEを使い先ほどのシートからデータを取得しています。

スクリーンショット 2018-04-04 9.01.03

参照元のデータを修正した場合も

スクリーンショット 2018-04-04 9.02.09

IMPORT側が、自動的に変更されます。

スクリーンショット 2018-04-04 9.02.31

マスター情報として一元管理が出来、その活用方法は色々考えられると思います。

構文
importrange(“シートへのアクセスキー”,”[シート名!]範囲”)

シートへのアクセスキー・・・シートのURLで言えば下記がアクセスキーとなっております。
“https://docs.google.com/spreadsheets/d/アクセスキー/”
[シート名!]範囲・・・”シート1!A1:C10″

=importrange("1lgNBqgig6IIaT5t7kdUpEvpEYi4YOUK0vmyWN6fxyB4","シート1!A2:AD31")

インポートしているセルを、使うことが可能であることはもちろんですが、
インポートしているセルに対して操作した場合に、どうなるかというと、
入力をすると全てのインポート表示が消え、関数の結果が#REF!になってしまいます。

以下をご覧ください。B3セルに入力すると

スクリーンショット 2018-04-04 9.16.40

関数の結果が#REF!になりました。

スクリーンショット 2018-04-04 9.16.49

あくまでインポートなので、参照先からの元データの修正は行われることはないので安心してインポート出来ますね!

まとめ

いかがでしたでしょうか?
今回ご紹介したもの以外にも、csvやtsvファイルを読み込めるIMPORTDATAがあります。
スプレッドシートを単なる方眼紙としてではなく、表計算として活用するために色々な関数を知ることは有益です。
たまにはふと手を止めて、関数の世界に足を踏み入れてみてはいかがでしょうか?

最後までお読みいただき、ありがとうございました。