Googleスプレッドシート 月末までの日付で検索した一覧を取得したい

やりたいこと。

有効期限とかの日付の入っている次のようなデータのマスターシートがあるとして、検索月を入力したら有効期限が今月末までのデータをマスターシートから取得して一覧が表示されるようにしたい。

A	B	C	D	E	F
区分	ID	タイトル	URL	有効期限	備考

特定のシートからデータを取得するにはIMPORTRANGE関数でスプレッドシートのデータを取得し、QUERY関数で表示するカラムを設定し、WHERE節で表示するデータの絞り込みを行えばOKです。
検索月を入力するセルの値から、当月末の日付を作成してWHERE節で有効期限セル <= 今月末とすれば良さそうです。

データを表示するシートは次のような想定です

	A	B
1	検索月を入力=>	{入力エリア}
2	{データを取得する関数}

B1セルに月を入力し、A2セルでB1セルの値を元にデータを取得する関数を作成します。

1. 入力値から当月末の日付を作成する

まず、検索セルB1に入力された月から、該当する月の月末の日付を作成します。

1-1. DATE 関数で今月の日付データを作成する

入力値が月のみなので、DATE関数を使って日付の形式にします。

DATE
指定した年、月、日を日付に変換します。
DATE(年, 月, 日)

= DATE( YEAR(TODAY()), B1, 1 )

年数は YEAR(TODAY())で本日の日付から年だけを取り出しています。
日付は、後で月末日に変換するので、間違いのない1日を指定しています。

1-2. EOMONTH関数で月末の日付を取得する

EOMONTH
起算日から指定した月数だけ前または後ろの月の最終日の日付を返します。
EOMONTH(開始日, 月数)

第二引数を0にすると今月末が取得できます。

= EOMONTH( DATE( YEAR(TODAY()),B1, 1 ), 0 )

これで、検索条件に使う月末の日付が完成しました。

2. IMPORTRANGE関数でマスターシートからデータを取得する

IMPORTRANGE
指定したスプレッドシートからセルの範囲を読み込みます。
IMPORTRANGE(スプレッドシートキー, 範囲の文字列)

スプレッドシートキーはスプレッドシートのURL https://docs.google.com/spreadsheets/d/< spreadsheet_key >/ から取得できます。
今回はA列(区分)とF列(備考)は不要なので、B列〜E列を取得したいとします。第二引数の「範囲の文字列」はシート名!B:Eとなります。

マスターシートからデータ取得はこんな感じ。

= importrange("spreadsheet_key", "シート名!B:E")

3. QUERY関数で表示したいセルを絞り込み

QUERY
Google Visualization API のクエリ言語を使用して、データ全体に対するクエリを実行します。
QUERY(データ, クエリ, [見出し])

データは 2 のIMPORTRANGEで取得したデータになるので、次のような感じになります。

= query( importrange("spreadsheet_key", "シート名!B:E"), "SELECT Col1, Col1 ... WHERE ..." )

QUERY関数で別のシートのデータを参照させた場合、SELECT文内で列の指定はAやBではなく、取り出した範囲に対してCol1、Col2という形式で記述しないとエラーになってしうようです。
この場合範囲をB:Eとしているので、Col1がB、Col2がCという扱いになります。

B:E列でD列(Col3)を除いて表示させるのは次のような感じ (長くなるので、SELECT文のみ書いています)

SELECT Col1, Col2, Col4

全列そのまま表示させるなら、* を使えばOKです

SELECT *

4. WHERE 節に条件を追加して有効期限が今月末までのデータのみを表示する

今回の例ではE列(Col4)が有効期限なので、WHERE Col4 <= 月末の日付とすれば良さそうです。

WHERE節で条件に日付を使う場合は

SELECT * WHERE Col4 <= date '2018-08-31'

のように date 'YYYY-MM-DD' という形式にする必要があります。 (この形式でないとエラーになる)

1.で作成した月末の日付をTEXT関数でYYYY-MM-DDの形式にフォーマットします。

TEXT
指定した表示形式に従って、数値をテキストに変換します。
TEXT(数値, 表示形式)

= TEXT( EOMONTH(DATE(YEAR(TODAY()),B1,1),0), "YYYY-MM-DD" )

これをWHERE節の条件に入れます。
QUERY関数のWHERE節内に他のセルの参照や式を入れるには、"& A1 &"の形式にします。 (QUERY関数内では、SELECT文全体が"(ダブルコーテーション)で囲まれているので、文字列連結の形にしないとエラー)

SELECT * WHERE Col4 <= date '"& TEXT( EOMONTH(DATE(YEAR(TODAY()),B1,1),0), "YYYY-MM-DD" ) &"'

最終的な関数は次のような感じになっているかと思います。

= query( importrange("spreadsheet_key", "シート名!B:E"), "SELECT * WHERE Col4 <= date '"& TEXT( EOMONTH(DATE(YEAR(TODAY()),B1,1),0), "YYYY-MM-DD" ) &"'" )

検索条件の月を変更しても意図したデータが表示できていればOKです。
これで、入力された月を条件に一致するデータだけが表示されるようになりました。

---

[参考]

• DATE - ドキュメント エディタ ヘルプ
• EOMONTH - ドキュメント エディタ ヘルプ
• IMPORTRANGE - ドキュメント エディタ ヘルプ
• QUERY - ドキュメント エディタ ヘルプ
• Query Language Reference (Version 0.7)  |  Charts  |  Google Developers
• GoogleスプレッドシートのQuery関数で結果が空になる謎仕様と回避策
• TEXT - ドキュメント エディタ ヘルプ

はじめてのスプレッドシートの教科書2017: データ入力・フィルター・ピボットテーブルetc...大学生・新卒のためのはじめてのスプレッドシートの教科書

Python3 自作モジュールのインポートにハマる

Python3 (3.6.5)でオレオレモジュールを作成してインポートしようとした際に結構ハマったのでメモ

importの基本

import <module>キーワードでモジュールをインポートする

import math
print( math.pi )
# => 3.141592653589793

モジュールのメソッドはmodule.methodで実行する

from <module> import <method> でインポートするとモジュール名を都度記述しなくてもメソッドの呼び出しができる

from math import pi, sqrt
print( sqrt(9) )
# => 3.0

from ... import ...でインポートする際にメソッド名が被ると、後からインポートしたものが有効になる

from math import pi
from my_modules import pi
pi # => my_modules.pi 

一度インポートされたモジュールは再インポートされない

モジュール echo.py

# echo.py
print( f'echo.py: name is {__name__}' )

実行ファイル base.py

# base.py
import echo
# => echo.py が実行される
print('---')
import echo
# => 再実行はされないので何も出力されない

実行↓

$ python base.py
echo.py: __name__ is echo
---

同じ階層にあるディレクトリ内のファイルをインポート

フォルダ構成

/root
  |- /modules
  |    |- echo.py
  |- base.py

# modules/echo.py
def say_name():
  print("I'm " + __name__)

import <ディレクトリ>.<ファイル名>でインポート

# base.py
import modules.echo
modules.echo()
# => I'm modules.echo

from <ディレクトリ> import <ファイル名>でインポート

# base.py
from modules import echo
echo.say_name()
# => I'm modules.echo

import <ディレクトリ>.<ファイル名>・from <ディレクトリ> import <ファイル名>でのインポートはどの階層からbase.pyを実行しても問題なくインポートすることができるようです。実行位置ではなくファイルから相対パスで読み込まれるという認識で良いのでしょうか？(この部分正確にな挙動はまだ理解できていません)

$ python root/base.py
> "I'm modules.echo"

$ cd root
$ :root python base.py
> "I'm modules.echo"

$ cd modules
$ :modules python ../base.py
> "I'm modules.echo"

更に深い階層にあるファイルのインポート

フォルダ構成

/root
  |- /modules
  |    |- /sub
  |        |- echo.py
  |- base.py

import ... - ディレクトリを.で繋げる

# base.py
import modules.sub.echo
modules.sub.echo.say_name()
# => I'm modules.echo

from ... import ... - fromの後に.区切りでディレクトリを指定する。

# base.py
from modules.sub import echo
echo.say_name()
# => I'm modules.echo

from ... import ...でimportの後にディレクトリを.区切りで表記するとエラー

# base.py
from modules import sub.echo
# =>    from modules import sub.echo
# =>                           ^
# => SyntaxError: invalid syntax

実行ファイルの上の階層にあるファイルのインポート

フォルダ構成

.
|- /root
|    |- base.py
|- /modules
|    |- echo.py

Pythonでは実行ファイルより上の階層にアクセスできない!?

モジュールのインポートで階層を.で表しているのだと思い、次のようにしてみた所エラーになってしまいました。

# base.py
from ..modules import echo
# => ValueError: attempted relative import beyond top-level package

# base.py
import ..modules.edho
# => SyntaxError: invalid syntax

どうやらPythonは現在実行されているファイルをモジュールのルートとみなすという仕様があり、その結果ルートより上の階層が見えなくなっていてるためエラーになるようです。

sys.path にモジュールのあるパスを追加するとインポートできる

sys.pathにはモジュールを検索するパスのリストが入っており、ここにインポートしたいファイルのあるパスを追加しておけば、実行ファイルの上の階層であってもインポートすることが出来るようになるみたいです。

# root/base.py
import sys
sys.path.append('../modules')
print(sys.path)
import echo

どうやらsys.pathにあるパスは実行した位置を起点にして探すようで、上記のように実行ファイルからの相対パスで指定した場合/rootディレクトリ以外で実行するとエラーになってしまいます。

$ python root/base.py
> ['/Users/.../root', '/User/.../.pyenv/versions/3.6.5/lib/python3.6', ... , '../modules']
  # ↑ sys.path に '../modules' がそのまま入っている
> ...
>     import echo
> ModuleNotFoundError: No module named 'echo'

パスの問題を解決するには、環境依存にならないよう動的に絶対パスを取得する方法があれば良さそうです。

pathlib でモジュールの絶対パスを取得する

Python 3.4 以上ではpathlibを使って絶対パスを取得するのが良さそうです。

# root/base.py
import sys
import pathlib
# base.pyのあるディレクトリの絶対パスを取得
current_dir = pathlib.Path(__file__).resolve().parent
# モジュールのあるパスを追加
sys.path.append( str(current_dir) + '/../' )

# モジュールのインポート
import echo
echo.say_name()
# => I'm echo

pathlibで取得できるパスはpathlib.PosixPathオブジェクトなので、そのままパスを編集しようと文字列連結しようとするとエラーになってしまいます。文字列連結などでパスを編集する際にはstr()で文字列にする必要があるのがハマりどころかもしれません。
これで実行ファイルの上の階層のファイルも読み込めるようになりました。

　
node.jsやruby・phpにくらべて、自分の位置の絶対パスの取得や上の階層のファイルのインポートが面倒だな〜という印象を持ちました。何かしら意味があって実行ファイルをrootとして上の階層にアクセスできなくしている経緯があると思うのですが、そのあたり詳しく説明されているサイトを見つけることが出来なかったので、少しもやっとしています。
それと、上の階層のファイルを読み込むためにsys.path にモジュールのあるディレクトリのパスを渡すという方法がお作法的に良いのかどうかいまいち判断できていません。
お作法的なものがあれば知りたいです。 (このドキュメント読めとかあればお願いします。)

また__init__.pyなどでディレクトリ内のモジュールをまとめてインポートとか出来るようなので、そのあたりは追々試してしていきたいと思います。

---

[参考]

• Python 3でのファイルのimportのしかたまとめ - Qiita
• Python 3：sys.pathとos.pathについて（機械学習，手書き数字認識） - Qiita
• Pythonでディレクトリの上層にあるモジュールをimportするときの注意点 - 長時間睡眠記
• Pythonで型を取得・判定するtype関数, isinstance関数 | note.nkmk.me

ペンギン・ハイウェイ (角川文庫)

森見登美彦作品好きなので映画化うれしい。

Python3 ファイルの絶対パスを取得したい。

os を使う

import os
currnet_dir = os.path.dirname( os.path.abspath(__file__) )

os.path.abspath(__file__) で現在のファイルの絶対パスを取得し、os.path.dirname(path)でファイルのあるディレクトリを取得。
参照: 10.1. os.path — 共通のパス名操作 — Python 2.7.14 ドキュメント

pathlib を使う

Python 3.4 以上

import pathlib
currnet_dir = pathlib.Path(__file__).resolve().parent

pathlib.Path(__file__)で現在のファイルのパスを取得し、resolve()で絶対パス化した後に、.parentで親であるディレクトリを取得。という感じでしょうか。
参照: 11.1. pathlib — オブジェクト指向のファイルシステムパス — Python 3.6.5 ドキュメント

ぐぐる検索するとos.pathを使用した例が多く出てきたのですが、Python3.4以上なら新しく追加されたpathlibを使ったほうが良いのかな？と思いました。

---

[参考]

• 11.1. pathlib — オブジェクト指向のファイルシステムパス — Python 3.6.5 ドキュメント
• 10.1. os.path — 共通のパス名操作 — Python 2.7.14 ドキュメント
• スクリプトの存在するディレクトリーの絶対パスを取得する
• [Python] スクリプト実行ディレクトリを絶対パスで取得する

レイメイ藤井 パスケース グロワール リール付き ブラック GLP164B