【GAS不要】Googleスプレドシート 一番上のセルが常に入力BOXとなるToDoリストを作ろう

前回のnoteで 👇こんな感じのクリックで表示切替が出来る Googleスプレッドシートで作るToDoリストの作成方法を紹介しました。

先週の note

今回は、このToDoリストをブラッシュアップ。さらに便利に機能強化をしていきます！

改良版 ToDoリストのゴール

前回の ToDoリストをベースに、こんな感じで改良してみましょう。

この改良版ToDoリスト作成の中で、Googleスプレッドシートの学び（Tips）が14個も登場します。

つまり QuickTips14回分の学びになるってことです！

学びはいいから（時間ある時にゆっくりやるから）、とりあえず出来上がったシートをすぐ使いたい！って方には一番下の有料ゾーンに コピーしてそのまま使える今回のフル機能実装ToDoリスト スプレッドシートのリンクを掲載しています。課金してゲットしましょう！

もちろん「有料記事」となっていますが、作り方は全て無料ゾーンに書いてありますのでご安心ください。

1. 表の構成を改良する

まずは表の構成を改良していきます。

1-1. 列を追加する

まず「担当者」列を追加しましょう。

C列を選択して「左に1列挿入」

「全て」タブとして利用しているD1セルは掴んでC1に移しておきましょう。

C2の見出しは「担当者」としておきます。

同じようにA列を選択して「左に1列挿入」で作業列を用意します。

チェックボックスがコピーされてしまうので一度全てDeleteで削除して、見出しは適当に #とでもしておきましょう。（後ほど書き換えるので空白のままでもOK）

これで 3列だった ToDoリストが 5列になりました。

※自分専用だから「担当者」列不要って場合は、カテゴリ（仕事とかプライベートとかのジャンル分け）として利用ください。

1-2. 範囲を200行まで拡張、見出し部分を行固定

次に長く利用できるようにチェックボックスや、交互の背景色を設定する範囲を200行まで拡張しておきます。

B列のチェックボックスの入ったセルを選択した状態で

データ ＞ データの入力規制　として

表示された B3:B20のチェックボックスの設定をクリック。

範囲を B3:B200にして「完了」とします。

同じように 交互の背景色 も200行まで拡張しておきましょう。

さらに、表示 ＞ 固定　で 見出し部分の2行を固定表示にしておきます。

リストが200行まで拡張され、見出し2行が固定されました

200行は目安なので、お好みで調整ください。
※ただし条件付き書式を適用するセルが多いとシートは重くなります

1-3. E列（空白セル）に日付ピッカーを設定する

次にE列（日付列）の日付を MM/dd 表示として、日付ピッカーが利用できるように設定します。

E列全体を選択して、表示形式 ＞ 数字 ＞カスタム日付形式 と進み

年なしの日付表示を選択し「適用」とします。

続けてE列選択状態で データ ＞ データの入力規制 を開き、

「ルールを追加」をクリック

範囲をE3開始に修正して、条件を「有効な日付」としてデータが無効の場合「入力を拒否」を選び「完了」とします。

E列の空のセルをダブルクリックした時に、日付ピッカーが表示されればOKです。

✅日付をピッカー選択できるように

が実装できました。

1-4. 期限超過チェックボックスを設定する

F1:F2セルを使って 期限超過を赤字表示とするスイッチの役割をもたせたチェックボックスを設定します。

F1セルに「期限超過」と入れて赤文字で少しサイズの小さい文字、中央揃えとしたうえで、

F2セルに 挿入 ＞チェックボックス でチェックボックスを1つ入れて

文字の色を赤として、チェックボックスを赤にしてきましょう。

これで改良版の大枠は完成です。

プルダウン設定の前に 条件付き書式の設定をしちゃいましょう。

2. 条件付き書式を設定する

条件付き書式を設定していきましょう。

2-1. A列（作業列）の数式を設定する

条件付き書式の設定の前に、条件付き書式や後で登場する並び替え機能（入力セルを一番上に表示）で利用する数式をA列に設定します。

フィルタビューで並び替えをする列にスピル式を設定する場合は、

✅見出し行に式を入れる
✅絶対参照で範囲指定する

この2つがポイントとなります。

今回は

この式を 見出し部分の A2セルに入れることで、

C列のセルに応じて

3パターンをA列に出力しています。

2-2. 全体の条件付き書式を設定する

非表示にするA列を除いた B3:E200の範囲に設定する条件付き書式は以下の4つです。

■タスク完了

B3セルを選択した状態で条件付き書式を開くと、

既に設定済みの「タスク完了」の書式があるので、

範囲を B3:E200に修正して 「条件を追加」を押します。

※A列は非表示とするので条件付き書式は不要です。条件付き書式は利用しているセルが多いほどシートが重くなります。

■期限超過

つづいて同じ範囲に対して、

カスタム数式　=AND($F$2,$A3=1,$E3<TODAY())
書式　文字色：赤、塗りつぶし：なし

を設定して、再び「＋条件を追加」を押します。

これで期限超過を赤表示させる際に使う、条件付き書式発動スイッチが実装できました。

E列が空白のセルも シリアル値0扱いで $E3<TODAY() がTRUEと判定されてしまうので、 $A3=1 も加えた 3つの条件でAND判定するのがポイントです。

■次に入力する行

3つ目の「次に入力する行」をフォーカスは、

カスタム数式　=EXACT($A3,0)
書式　太字、文字色：暗い黄3、塗りつぶし：明るい黄3

を設定して再び「＋条件を追加」を押します。

単純に $A3=0 としてしまうと、先ほどと同様に 空白を0と見なされて色がついてしまうので、

ここは厳密な一致を判定するEXACT関数を利用するのがポイントです。

■まだ使わない行

最後に「A列が空白の行のチェックボックスを非表示」にします。

非表示といっても、条件付き書式でチェックボックス（文字の色）を背景と同じ色にして見えなくするだけしか出来ません。

カスタム数式　=$A3=""
書式　文字色：白、塗りつぶし：白

条件付き書式は、手入力による書式や交互の背景色よりも優先されるので、これによってチェックボックスが見えなくなります。

ただ見えないけど B列のセルにはチェックボックスが存在しているので、誤ってクリックしてしまうと、このような表示が出ます。注意しましょう。

動作を確認しておきましょう。

✅未完了で期限超過の行（タスク）を赤字表示できる機能を追加
✅必要なチェックボックスのみ表示、次の入力箇所を色付けでフォーカス

条件付き書式で2つの機能が追加出来ました。

2-3. 見出し部分の条件付き書式で使う作業セルの数式を精査

次に見出し部分の条件付き書式で使っている作業セル（G1:H1）を別シートに移動します。

でも、その前にまずはこの部分の式を精査しましょう。

条件付き書式で使う数式を入れた作業セルを、ビューの数と同じ3つにして、TRUE、FALSEを返す数式とすれば、条件付き書式側ではセルを参照するだけ でOKとなります。

というわけで、

このようにルールと式を定義します。

これを簡潔に1つの式で書くと

こうなります。

ARRAYFORMULA関数で SUBTOTAL関数の第1引数を配列として、一気に最大値、最小値を計算。

この結果をLET関数で xとおいて、SUMとPRODUCTで計算した結果を0、1と比較した結果のブール値を中カッコで横に連結しています。

ビューを切り替えると TRUEとなる位置が切り替わるのがわかりますね。

これを別シートで実現するので、設定シートというシートを作成して

A2セルに

を入れます。

ToDoシートの方のG:I列は削除してOK。

SUBTOTAL式の精査、別シートへ移動が出来ました。

2-4. 見出し部分の条件付き書式で別シートを参照する

まずはB2:E2の見出し行部分の条件付き書式を修正しましょう。

まずグレーの「完了」ビューの設定です。

範囲を B2:E2 に修正して、
カスタム数式を　=INDIRECT("設定シート!B2")

このようにします。

同じように「未完了」も

範囲を B2:E2 に修正して、
カスタム数式を　=INDIRECT("設定シート!A2")

とします。

続いて1行目の設定です。こちらは1つの式でまとめて設定できます。

1番上だけ残して、下の2つは条件付き書式を削除。

1番上の条件付き書式を

範囲　B1:D1

カスタム数式　=INDEX(INDIRECT("設定シート!A2:C2"),,COLUMN()-1)

とします。

これによって一つの条件付き書式設定で、3つのビューのうち選択しているビューを太字・白文字にする切り替えを実現しています。

別シートのフィルタビューに対しても、SUBTOTAL関数はきちんと可視セルのみ計算してますね。

✅条件付き書式に使用する式を見直し作業セルを別シートへ移動

が出来ました。

3. プルダウンを設定して担当者で絞り込めるようにする

次に担当者列のプルダウン設定と担当者で絞り込める機能を実装しましょう。

3-1. 担当者列をプルダウン設定する（シークレット チップ型プルダウンを使う）

まずプルダウンのリストを設定シートに準備しましょう。

A5:A20をリスト範囲として、とりあえずダミーで

を入れておきます。

ここでポイントとなるのが、一番下のセル A20に '（シングルクォート）を入れておくことです。

これは後で プルダウンチップを見せない仕掛けに使います。

Todoリストシートに戻り、D3:D200を選択して、挿入 ＞ プルダウン を選択

プルダウン（範囲内）を選び、範囲を 設定シート!A5:A20 としましょう。

一番したにシングルクォートが含まれていますね。お好みで各担当のチップに色をつけてもOK。

既にタスクが入ってるセルにはダミーで適当な担当をセットしておきます。

ただ、せっかく条件付き書式で綺麗にした下の部分に空のプルダウンチップが表示されてしまうのが、ちょと残念ですよね。

ここを プルダウンで 一番下のシングルクォートを選択（見た目は空白）することでチップを非表示にするテクニックを使います。

このセルをコピーして、プルダウンの入っている 200行まで選択して値貼付け。

これで空白のように見えて、ダブルクリックすると実はチップスタイルという 隠れチップ表示プルダウンを実現しています。

これは プルダウンが空白を選択肢には出来ない仕様なのに、見た目上空白となる シングルクォートを選択した際のバグのようなものです。

✅未選択のプルダウンチップを非表示に

が実装できました。

さらに D1に 担当者 と小さめのフォントで文字を入れ

プルダウン適用範囲 D3:D200に追加で G2を加えます。

検索用のプルダウンも準備が出来ました。

3-2. 「未完了」「完了」のフィルタビュー時に G2セルで指定した担当者で絞り込む

それでは G2で選択した担当者で絞り込む機能を、「未完了」「完了」のフィルタビューに追加していきましょう。

ここで

条件でフィルタ　＞　完全に一致するテキスト　として

=$G$2 としたくなりますが、

これだと G2のプルダウン未選択で、担当者で絞り込みたくない場合に、「空」と一致を条件としてしまい正しく機能しません。

ここは 条件でフィルタ ＞ カスタム数式 を選び

とします。

フィルタ条件のカスタム数式は、条件付き書式のものと同じ感覚で使えます。

指定した列のデータの開始セル（今回の場合は D3）で下方向に相対参照して TRUEを返す行がフィルタで表示されるので、

$G$2が空の時は担当者で絞り込まないので TRUEを返すとして、空白以外の時だけ D3=$G$2を条件とする式にします。

G2セルは固定なので絶対参照としましょう。

名前をつけたフィルタビューは、設定を変更した時に自動保存となるので注意です。

式をコピーして、「完了」ビューにも同様に設定しておきましょう。

注意点として フィルタビューをかけた後でG2のプルダウンを変更しただけでは表示は切り替わりません。

ブラウザを更新（Windowsであれば Ctrl +F5キー）、または一旦違うビューに切り替えてから元のビューを再クリックが必要となります。

フィルタビューの仕様上、どうしてもこの手間が必要です。

また「全て」のタブに、この担当者絞り込み機能を追加すると、最後の 1行目を入力行とする機能の邪魔になる為、担当者フィルタは「未完了」と「完了」のビューにのみ追加としています。

✅担当者列を追加、担当者での絞り込み可能に

こちらの機能が追加できました。

4. 一番上の行を入力セルとする機能を追加する

最後に一番上に入力行を表示する機能を「全て」ビューに実装しましょう。

4-1. フィルタビューでA列を並び替えを使う

一番上を入力行にする為の仕込みは、実はA列の数式で既におこなっています。

ビューは現状B2:E200なので、これをＡ列も含めた A2:E200として

A列を「昇順で並び替え」とすれば、

A列の数字が小さいものが上、大きいものが下に並び変わり、空白セルはその下に並ぶため、一番上が入力行（A列が0の行）となります。

ただ、これだと

1つタスクを入力完了してから、次を入力する為に一番上に入力行を表示させる為には、表示を他に切り替えてから再度 全てビューに戻すか、Ctrl+F5で更新をかける必要があってイマイチです。

これを数式で解決します！

4-2. C列の値の入ったセルの個数で2つの「全て」ビューを切り替える

まず「全て」ビューを複製します。

名前は「全て2」とでもしておきましょう。

この「全て」と「全て2」のビューを D1セルに設定したリンクで、タスクを入力する毎に切り替わるように数式を組みます。

D1セル「全て」のハイパーリンクの編集画面を開き

このアドレスをコピーしてから D1セルの中身をDeleteで削除し、HYPERLINK関数の以下の式を入れます。

全て2にフィルタビューを切り替えて

「全て2」のフィルタビューID（fvid=の後ろの数字部分）だけをコピーしておきます。

そしてD1セルのHYPERLINK関数の式を

このように修正。

タスクを入力する C2:Cの値の入ったセルの個数をCOUNTA関数でカウントし、IF関数と数値が偶数ならTRUEを返すISEVEN関数を組み合わせて、タスクの数が偶数なら「全てビュー」、奇数なら「全て2ビュー」とリンク先のビューを切り替える式です。

偶数、奇数はタスクを入力する度に入れ替わるので、

このように入力して「全て」のリンクを押すだけで、「確定ボタン」のように入力した内容は一番下に下がり、再び入力行を一番上に表示させることができました。

✅一番上を入力セルとする機能を追加

も実装できました。

4-3. タスク入力後、更新すると入力位置にフォーカスが戻るようにする

一番上の行を入力用にすることは出来ましたが、

リンクを押したあとどうしても、D1セルもしくは期限を設定するE3セルに選択が残ってしまいます。

連続で入力する際に、出来ればセルの選択を入力開始セルの C3に戻したいです。

これは先ほどの式を少し修正するだけで簡単にできます。

第1引数のリンクURLの末尾に &"&range=C3" を追加しただけですが、これだけで

入力確定したタスクの行を一番下に移動させるのと合わせて、入力セルのC3にフォーカスを移すことが出来ます。

特定セルへジャンプするハイパーリンクは 過去noteで紹介しましたが、
これが フィルタビューと組み合わせても使えるってことです！

オマケで上のリンクを押すのはちょっと面倒なんで タスク入力確定ボタンとして

入力したすぐ横の F3 セルに式をコピペして第2引数を " 🔽 " としてボタン風に設定しましょう。

少し操作は複雑ですが、キーボードだけで流れるように連続でToDoの 入力▶確定 を繰り返すことが出来ます。

並び替えは 範囲外の列は影響を受けないので、「全て」ビュー（「全て2」ビュー）の時は常に F3に 入力確定ボタンが表示されるのもポイントです。

最後の機能

　✅タスクを入力して更新すると入力位置にフォーカスが戻る

が追加できました。

5.仕上げと完成

最後にA列を非表示として

列サイズを150くらいに設定すれば完成です。

が全て実装できました。

使用の注意点

利用にあたっては幾つか注意点があります。

反映は表示切替、またはCtrl+F5更新時

担当者を絞り込んで検索の際と同様に、チェックの反映はリアルタイムではなく「更新時」もしくは「表示切替」のタイミングです。

👆上の画像でわかる通り、未完了のビュー状態で終わったタスクにチェックを入れると 「全て」を選択した時の条件付き書式 が適用されてしまいます。

一度他のタブに切り替えて再度「未完了」をクリックするか、Ctrl+F5で更新することで表示が正しくなります。

チェックを2つ付けて更新したいケースもあるので、ここでは ビューをコピーして偶数・奇数で切り替え方式が使えません。

ここは妥協しておきましょう。

1件も完了がない、未完了が無い状態は正しく切り替え表示とならない

ToDoリストに1件も完了がない（チェックありが無い）場合は、全てを表示していても「未完了」がアクティブと判断されてしまいます。

数式を工夫すれば回避できるんですが、ここは気にしないでよいでしょう。

スマホアプリ版では使えない

利用しているフィルタビュー機能がスマホアプリ版では使えない為、これはパソコンブラウザでのみでの利用に限定されます。

ハイパーリンクとなっているセルをタップしても、フィルタビューのURLはアプリ版では反応しません。（切り替わりません）

スマホで使えないのは非常に残念。

シートコピーした場合はハイパーリンクの再設定が必要 ※スプレッドシート丸ごとコピーはOK

Todoリストシートは、シートをコピーしてもコピーしたシートではうまく機能しません。

そもそも設定シートとTodoリストシートは、お互いに参照しているので1対1の関係ですし、コピーしてしたシートは シートIDやfvidが変わってしまうので、設定されてるリンクでは元のシートをシートビューにした画面にジャンプしてしまいます。

一方で、スプレッドシート自体をコピーした場合は

シートIDやfvidは全て引き継がれるので、そのまま利用できます！

ということで、ここまで読んだけど（もしくは読み飛ばしたんで）自分で作るのは面倒！という人向けに 有料でスプレッドシートコピーですぐ使える 多機能Googleスプレッドシート ToDoリストを 公開します！

欲しい方は注意点を理解した上で、有料noteをご購入ください。

次回は再びQUERY関数シリーズに戻る予定。

👇以下有料ゾーン。

【有料】タブ風切り替え、一番上が入力行となる多機能スプレッドシート ToDoリスト 配布用