スプレッドシートの自動化の方法3つ、関数とGASとAIの使い分けと手順

スプレッドシートの自動化でできることと3つの方法

スプレッドシートの自動化とは、人が毎回手でやっている入力、計算、転記、集計、連絡を、仕組みに任せることです。小さな事業者でよくあるのは、売上や予約の一覧を見ながら電卓で合計する、別のシートに同じ内容を書き写す、締めの日に担当者へメールで知らせる、といった作業です。

やり方は大きく3つに分かれます。関数、GAS、AIです。それぞれ得意なことが違うので、先に全体を見ておきます。

どの作業から自動化するかの決め方

最初に、手作業を紙に書き出します。毎日か毎週くり返していて、手順が毎回同じで、間違えると困る作業が自動化に向いています。反対に、月に1回しかなく毎回判断が変わる作業は、仕組みを作る手間の方が大きくなりがちです。

スプレッドシートに限らず、どの仕事から手を付けるかの全体の考え方は、中小企業のAI業務効率化の始め方でまとめています。

関数でスプレッドシートの集計を自動化する

スプレッドシートの集計の自動化は、関数から始めるのが近道です。元の一覧に1行足すだけで、合計や月ごとの集計が自動で変わるようにしておけば、締めのたびに電卓をたたく作業がなくなります。

集計の自動化でよく使う関数

QUERY関数は、表に対して「この条件の行を、この列でまとめて」という問い合わせを書く関数です(出典: Google ドキュメント エディタ ヘルプ「QUERY 関数」)。書き方が少し独特ですが、月別や商品別の集計表を1つの式で作れます。別のファイルから読み込むIMPORTRANGEは、最初に読み込みを許可する操作が要ります(出典: Google ドキュメント エディタ ヘルプ「IMPORTRANGE 関数」)。

式を書かずに集計するならピボットテーブル

式を書くのが苦手なら、ピボットテーブルから試すのも手です。メニューの「挿入」から作れ、行に担当者、列に月、値に売上の合計、のように選ぶだけで集計表ができます(出典: Google ドキュメント エディタ ヘルプ「ピボット テーブルを作成、使用する」)。元の一覧の範囲を列ごと指定しておけば、行を足したあとも集計に入ります。

集計を自動にするための元の表の作り方

• 1行に1件(1件の売上、1件の予約)だけを書く
• 1つの列には1種類の情報だけを入れる(日付の列に「未定」などの文字を混ぜない)
• セルの結合を使わない
• 見出しは1行目の1行だけにする
• 合計や小計の行を、元の一覧の途中に入れない

関数がうまく動かない原因の多くは、関数ではなく元の表の形にあります。見た目を整えた表と、集計のための一覧は分けて作ると、あとの自動化が楽になります。

Googleスプレッドシートの自動化に使える標準の機能

Googleスプレッドシートには、関数のほかにも、プログラムを書かずに手作業を減らせる機能があります。GASに進む前に、ここで足りないかを確かめておくと、作るものが少なくて済みます。

• Googleフォームの回答を、シートに自動でためる
• データの入力規則とプルダウンで、入力の表記をそろえる
• マクロの記録で、くり返しの操作を覚えさせる
• 通知の設定で、シートが変更されたときにメールで知らせる

Googleフォームの回答をシートに集める

問い合わせや申し込み、日報などをGoogleフォームで受けると、回答をスプレッドシートにためられます。紙やメールの内容を手で書き写す作業がなくなり、入力の形もそろいます。

データの入力規則とプルダウンで表記をそろえる

担当者名や商品名をプルダウンから選ぶ形にすると、「山田」「山田さん」のような表記ゆれが起きにくくなります。集計の関数は1文字違うだけで別のものとして数えるので、入力の段階でそろえておくことが、集計の自動化の土台になります。

マクロの記録でくり返しの操作を覚えさせる

毎回同じ書式を整える、決まった列で並べ替える、といった操作は、マクロの記録で覚えさせられます。「拡張機能」の「マクロ」から記録を始め、操作を終えて保存すると、次からはメニューやショートカットで同じ操作をくり返せます。記録したマクロはApps Scriptのコードとして保存され、あとから書き換えることもできます(出典: Google ドキュメント エディタ ヘルプ「マクロで Google スプレッドシートのタスクを自動化する」)。

GASでスプレッドシートの自動化を進める手順

GAS(Google Apps Script)は、Googleが用意しているプログラムの仕組みで、スプレッドシートやGmail、カレンダー、ドライブなどを操作できます(出典: Google for Developers「Apps Script の概要」)。関数ではできない「決まった時刻に動かす」「メールを送る」「別のファイルに書き出す」といった作業を任せられます。

GASで任せやすい作業の例

• 毎朝、前日の売上を集計して担当者にメールで送る
• フォームの回答が届いたら、内容をまとめて通知する
• 月末に、店舗ごとのシートを1つの集計シートにまとめる
• 一覧の内容からカレンダーに予定を入れる
• シートの内容をもとに、決まった形のドキュメントやPDFを作る

スプレッドシートからGASを開いて動かすまで

1. スプレッドシートの「拡張機能」から「Apps Script」を開く
2. 関数(処理のまとまり)を書いて保存する
3. 実行ボタンを押し、初回はアクセスの許可を求める画面で中身を確かめて許可する
4. 結果をテスト用のシートで確かめる
5. 左のメニューの「トリガー」から、動かす時刻やきっかけを設定する

GASのトリガーの種類と実行時間の上限

GASを自動で動かすきっかけをトリガーと呼びます。シートを開いたときや編集したときに動く「シンプルトリガー」と、決まった時刻やフォームの送信でも動かせる「インストール可能なトリガー」があります。シンプルトリガーには、許可が必要な処理(メールの送信など)ができない、30秒を超えて動けない、といった制限があります(出典: Google for Developers「シンプルなトリガー」)。

また、1回の実行時間や1日に送れるメールの数などには上限があり、1回の実行は6分までとされています(出典: Google for Developers「Google サービスの割り当て」)。上限はアカウントの種類によって違い、変わることもあるので、作る前に公式ページで確認してください。GASで扱える作業の全体は、Google Apps Scriptでできることでまとめて紹介しています。

スプレッドシートの自動化にAIを使うときの使い方と注意

スプレッドシートの自動化にAIを使う場面は、大きく2つあります。1つは、式やGASのコードを書く手伝いです。もう1つは、問い合わせの文章の分類や要約のように、関数では扱いにくい作業です。

式やGASのコードをAIに書いてもらう

AIのチャットに「A列が日付、B列が担当者、C列が売上の表で、担当者ごとの今月の合計を出す式」のように、表の形とほしい結果を具体的に伝えると、式やコードの下書きを出してくれます。列の名前と、実際に入っている値の例を添えると、外れにくくなります。

出てきた式やコードは、そのまま本番の表に入れず、コピーしたテスト用のシートで動かして、手で計算した結果と合っているかを確かめます。GASの場合は、何をするコードなのかをAIに説明させ、メールの宛先やシートの名前が自分の環境と合っているかを見てから動かしてください。

スプレッドシートの中でAIを使う

Google Workspaceの一部のプランでは、スプレッドシートの中でGemini(GoogleのAI)を使い、表の作成や式の提案、データのまとめを頼めます。使える機能とプランは変わることがあるので、Google Workspaceの公式サイトで確認してください。AIのチャットを仕事でどう使うかは、ChatGPTの業務での活用でまとめています。

関数とGASとAIのどれを使うかの判断基準

迷ったときは、やりたいことを1文で書き、次の表に当てはめます。関数で済むなら関数、時刻やメールが絡むならGAS、文章を読んで判断する作業ならAI、という分け方が基本です。

スプレッドシートでは足りなくなるサイン

スプレッドシートは便利ですが、向かない使い方もあります。1つのスプレッドシートに入るのは最大1,000万セルまでとされていて(出典: Google ドライブ ヘルプ「Google ドライブに保存できるファイル」)、行や列が増えるほど動きも重くなります。次のようなことが起きていたら、作り方を見直す時期です。

• 何人もが同時に書き込み、上書きの事故が起きる
• お客様にも入力や閲覧をしてもらいたい
• 見てよい範囲を人ごとに細かく分けたい
• 予約や在庫など、二重の登録を防ぐ必要がある

こうした場合は、スプレッドシートの代わりに業務アプリを作る方が合うこともあります。かかる費用の考え方はWebアプリの開発費用で、お客様の情報をシートで管理するときの作り方は顧客管理をスプレッドシートで行う方法で説明しています。

スプレッドシートの自動化でつまずきやすい失敗と防ぎ方

仕組みを作る側から見ると、つまずきやすいのは作るときより、作ったあとの使い続け方です。よくある失敗と防ぎ方を並べます。

列を足したら集計がずれた

GASのコードが「3列目」のように列の位置で値を読んでいると、途中に列を足しただけで別の値を読みます。列を足すときは右端に足す、見出しの名前で列を探すコードにする、といった決まりを作っておきます。

作った人がいなくなると止まる

インストール可能なトリガーは、設定した人のアカウントで動きます(出典: Google for Developers「インストール可能なトリガー」)。作った人が辞めてアカウントが消えると、仕組みが止まることがあります。会社で使うアカウントで作る、説明のシートに作った人と設定の場所を書いておく、の2つで防げます。

止まっていたことに気づかない

トリガーで動くGASがエラーで止まると、設定に応じてGoogleから失敗を知らせるメールが届きます。このメールを受け取れる宛先にしておき、届いたら誰が見るかを決めておきます。月末の集計のように、たまにしか動かないものほど、止まったことに気づくのが遅れます。

共有の範囲が広すぎる

手軽さを優先して、リンクを知っている全員が編集できる設定にしてしまう例があります。お客様の情報が入ったシートは、共有を必要な人だけに絞り、編集できる人と見るだけの人を分けてください。

スプレッドシートの自動化を自分で進める範囲と頼む目安

関数と標準の機能、AIに下書きを手伝ってもらう小さなGASまでは、自分で進められることが多いです。まずは次の順に試してください。

1. 手作業を書き出し、くり返しが多いものを1つ選ぶ
2. 元の表を1行1件の一覧に整え、入力規則で表記をそろえる
3. 関数かピボットテーブルで集計を自動にする
4. 時刻やメールが絡む作業だけをGASにする(式やコードはAIに下書きを頼み、テスト用のシートで確かめる)
5. 説明のシートを作り、誰が見ても分かるようにする

頼んだ方がよい条件

• 会計ソフトや予約システム、LINEなど、ほかのサービスとつなぎたい
• 止まると売上や請求に響くのに、すぐに直せる人が社内にいない
• 作った人が辞めても動き続けるように、説明と引き継ぎまで整えたい
• スプレッドシートでは足りなくなるサインが出ている
• 試してみたが、時間をかけても思いどおりに動かない

請求書づくりのように、スプレッドシートと別のサービスをつなぐ作業の進め方は請求書の自動化の方法で、ほかの会社がどんな作業からAIと自動化を取り入れているかはAI業務効率化の事例で紹介しています。

uparia では、手作業の書き出しから、関数とGASでの仕組みづくり、AIの組み込み、説明のシートの用意まで、小さな会社の自動化をお手伝いしています。どこから手を付けるかの整理だけのご相談でもかまいません。進め方は業務自動化とAI導入支援のページでご覧いただけます。