業務のデータが表計算ソフトの共有シートに入っている、というのはよくあります。データベースに入っていないので取りにくそうに見えますが、が用意されているので取得自体は難しくありません。
取得の形は の書き出しでもでも構いません。難しいのは取得ではなく、人が編集し続けているという性質のほうです。列が増える、行が挿入される、書式が変わる、値が入力途中の状態になる。データベースなら起きないことが日常的に起きます。
この回では、人の運用を止めずに機械が読み続けるための作り方を扱います。要点は、シート側に規約を持ち込みすぎないことです。守ってもらえない規約は、いずれ破られます。
位置ではなく名前で読む
最もよくある壊れ方が、列の位置がずれることです。誰かが列を1つ挿入すると、それ以降の列が全部ずれます。取得側が「3列目が金額」という前提で書いていると、その日から別の値を読み始めます。
対策は、見出しの行を読んで列名から位置を求めることです。列が増えても、名前が変わらなければ動き続けます。逆に名前が変わったときは、その場で例外にして気づけるようにします。
def build_index(header: list[str], required: list[str]) -> dict[str, int]: """見出し行から、列名 -> 位置の対応を作る。 位置を直接書かないことで、列の挿入に耐えられる。""" cleaned = [h.strip() for h in header] index = {name: i for i, name in enumerate(cleaned) if name}
missing = [c for c in required if c not in index] if missing: raise KeyError(f"必要な列が見つからない: {missing}。見出しの変更を疑う")
dup = [c for c in cleaned if cleaned.count(c) > 1 and c] if dup: raise ValueError(f"同じ名前の列が複数ある: {sorted(set(dup))}")
return index人が編集していると、同じ見出しの列が2つできることがあります。コピーして貼り付けたときの残骸です。名前で引くとどちらが取れるか分からないので、重複を見つけたら止めるのが安全です。
型は揃わないものとして扱う
同じ列に、数値と文字列が混ざります。「1000」と「1,000」と「約1000」が並ぶ。日付の列に「未定」と書かれる。これは入力する人が悪いのではなく、表計算ソフトが何でも受け付けるからです。
で読み込むと、こうした混在は文字列の列として現れます。取得側で厳格に弾くと、業務が止まります。かといって黙って捨てると、数字が合わなくなります。変換できなかった値を記録して、件数を報告するのが現実的な落としどころです。
import pandas as pd
def to_number_with_report(s: pd.Series, col: str) -> tuple[pd.Series, list[str]]: """数値に直せなかった値の実物を返す。件数だけだと直しようがない。""" cleaned = ( s.astype(str) .str.replace(",", "", regex=False) .str.replace("円", "", regex=False) .str.strip() .replace({"": None, "-": None}) ) num = pd.to_numeric(cleaned, errors="coerce")
failed = s[num.isna() & s.notna() & (s.astype(str).str.strip() != "")] samples = failed.astype(str).unique().tolist()[:10] if samples: print(f"{col}: 数値に直せない値 {len(failed)} 件 例: {samples}") return num, samples取得のたびに全件読むか、差分にするか
シートの規模が小さいうちは、毎回全件を読むのが単純で確実です。数千行を超えてくると読み込みに時間がかかり始めますが、行が入れ替わったり並び替えられたりするので、EP.06 で扱った差分取得は当てはめにくくなります。
人が並び替えたり行を挿入したりする以上、位置を頼りにした差分は成立しません。全件を読んで、内容の要約値で変化を判定するほうが確実です。読み込みの費用より、取りこぼしの損失のほうが大きい場面が多い。
取得した結果はにそのまま残しておいてください。誰かが誤って行を消したときに、前日の状態が手元に残っていれば復元できます。シート側の履歴に頼れないこともあるので、保険として効きます。
import pandas as pd
def require_unique_key(df: pd.DataFrame, key: str) -> pd.DataFrame: """行の同一性を判断する列を決めておく。 人が編集するシートでは、行番号を鍵にしてはいけない。""" if key not in df.columns: raise KeyError(f"鍵にする列が無い: {key}")
blank = df[key].isna() | (df[key].astype(str).str.strip() == "") if blank.any(): raise ValueError(f"鍵が空の行が {blank.sum()} 件ある。入力途中の可能性")
dup = df[df.duplicated(subset=[key], keep=False)] if not dup.empty: raise ValueError(f"鍵が重複している行が {len(dup)} 件ある: {dup[key].tolist()[:5]}") return df権限の設計を先に決める
取得には認証が要ります。個人のアカウントで認証すると、その人が異動や退職をした時点で取得が止まります。EP.04 で触れたとおり、自動化にはを使うのが基本です。
サービスアカウントを作ったら、そのアドレスをシートの閲覧者として追加します。編集権限は与えないでください。取得するだけなら読めれば十分で、書き込みの権限があると事故の余地が増えます。
人が編集するデータ源は、どうしても不安定です。取得できなかった日があること自体は異常ではない、という前提で作っておくと、運用が楽になります。次回は、もう少し構造のあるデータ源を扱います。
共有シートは、業務の現場にいちばん近いデータ源です。だからこそ動きが速く、壊れやすい。取得側が寛容に作られていれば、現場の運用を変えずに済みます。
この記事の感想を教えてください
あなたの 1 クリックで、本当にこの記事は更新されます。「もっと詳しく」「続編希望」が一定数集まった記事は、 ふくふくが 実際に内容を拡充したり続編記事を公開 します。 送信したリアクションはお使いのブラウザに記録され、再カウントされません。