AI

HOME
AI
Googleスプレッドシートのマクロの記録機能を使ってみた

2018年4月17日 / 最終更新日時 : 2024年10月1日 officeの杜 AI

Googleスプレッドシートのマクロの記録機能を使ってみた

2018年4月11日、Googleスプレッドシートに大きな機能追加がありました。それが「マクロの記録」機能。Excelではもはやおなじみの、ユーザが操作した内容を記録し、ボタン一発でその操作を再現する機能です。Googleスプレッドシートなので、記録した操作はGoogle Apps Scriptに変換されて保存されます。

また、保存した操作はそのまま改造することも可能なので、Google Apps Scriptで１から構築することは難しいけれど、ちょっとなら直せるというノンプログラマな人にとって、またオフィス事務作業の自動化に大きく貢献する機能を実際に使ってみたいと思います。

今回使用するスプレッドシート

今回のテーマであるマクロの記録は、カスタム関数と異なりそれそのものはコードを自分自身の手で書くことはありません。自身が記録中にスプレッドシート上で操作した内容がそのままGASのコードとして変換されて保存されます。これはExcelでも同様です。

利用上の注意点

逆にGAS学習の妨げになることも

ノンプログラマーでITに疎い人でも、録画をするが如しコードを生成して業務効率化出来る、それがマクロなのですが、殆どの現場ユーザが「マクロの記録」で止まってしまい、そこから先の学習につながっていないのが現状です。これはExcel VBAでも同様のことが起きています。

その理由は「効率化出来たから学習は必要ない」と判断してしまう為。モチベーションが続かないのです。

Excelの関数でもVLOOKUP関数が使えたらそれでもう十分として、先に進むことなくずーっと中級に到達できずその場に留まり続ける人は多いです。しかし、マクロの記録で実現できる業務効率化は全体のうちのほんの些細な最初の一歩程度に過ぎず、コードが書けるようになると「マクロの記録」はまず使わなくなります。

便利な機能ではありますが、スプレッドシート上の操作に限定される為、使い所も効率化の効果も非常に小さいのがマクロの記録という機能なのです。あくまでも、GASを学ぶ最初の一歩の為に使うべきでしょう。

効率の悪いコードが出来上がる

マクロの記録で保存されるコードは、ユーザが操作した内容がそのまま忠実に記録されます。それが故に以下のような問題を抱えています。

ミスした動作も記録されてしまう
大雑把な作業の記録ではそこまでで無いけれども、１行１行を処理する作業を記録すると、非常に効率の悪いコードが出来上がる（実行スピードが非常に遅い）
実行スピードだけじゃなく、データ量が異なったり、列の数や位置が異なるとそれだけで動作しなくなったり、処理が中途半端で終わる。
冗長な非効率なコードであるため、誰かが手直しする際の大きな障害になることがある。

故に、これら生成されたコードを、効率の良いコードに書き直したり、不要なコードを削除したり、そしてデータ量が増えてもforループで繰り返し処理をするように改造したりする必要性があります。

人間が操作するやり方や手順は、プログラムの世界では非常に冗長であるということを知り、これらコードを直す為にGASを学ぶ必要性があります。

色々出来ない事と注意点

Excelのマクロの記録の場合、複合機での印刷などにも対応しており、最終的に印刷するまでの流れを構築するだけじゃなく、VBAレベルだとExcel VBAでありながら、他のアプリケーションの操作やウィンドウに対してキーボードのキー操作を送信して操縦なんてことまで出来たりします。

RPAが無かった時代はこういったVBAやマクロで連続操作を実現してるようなものまで存在していました。一方で、Googleスプレッドシートのマクロの記録の場合出来ないことがあります（GASの制限でもあります）

スプシに印刷機能はあれど、GASに印刷のメソッドが無いため、印刷操作をしてもマクロに記録はされません。
マクロの記録やGASはサーバ上で動作してる為、ローカル環境の操作やデスクトップ上のアプリケーションの操縦は出来ません。
また、同様にローカル環境のNASなどのファイルにアクセスさせることは出来ません。
セルに関数を書き込むことは出来ても、操作上の記録で条件判定をさせることは出来ません。
意識的にシートを選択するような動作を入れてあげないと、全く違うシートでそのまま動いてしまう。意識しながら動作を記録する必要があります。
まとまった表データに対しての繰り返し処理（forループ）などは記録出来ません。
一時的に値をコピーしておき、その値を計算して別のセルに書き込むといったような変数的な動作は出来ません。

などなど。Excelのマクロの記録でも出来ないことが上記には含まれています。

実際に作ってみる

操作を記録する

操作開始をするには、メニューの「ツール」⇒「マクロ」⇒「マクロを記録」をクリック。その後、実際に操作をしてみます。この記録した内容はバックグラウンドで、Google Apps Scriptに変換されて記録されています。ですので、スクリプトエディタで見ると、一連の記録内容を見る事が出来る様になっています。

スプレッドシートのメニューより、拡張機能→マクロ→マクロの記録を開く
下にダイアログが出て、絶対参照を使用のまま、すでにもう記録が開始しています。
一挙手一投足のスプレッドシートの操作が後ろでGoogle Apps Scriptとして記録されます。
作業が終わったら、ダイアログの「保存」をクリックします
マクロの名前をつけて保存をクリックすると完了します。
この時点でスクリプトエディタを開くと、Google Apps Scriptのコードとして生成されています。

VBAのような豊富でトリッキーな事が出来るか？といったら、Google Apps Scriptで対応していないメソッドのものは記録は出来ません。基本的な操作はほぼ全て出来るみたいです。

絶対参照と相対参照の２つがありますが、絶対参照は例えば範囲選択では、どんなシートでも必ず同じ範囲を指定するのに対して、相対参照は自分で選択した領域内で記録をするようです。通常は絶対参照でOKでしょう。

マクロの記録で生成したコード等はファイルをGoogle Apps Script同様にコピー先にもコードまるごと複製されますので、別途作り直しは必要ありません。

※例えば表に罫線を加え、タイトルに色を付け、余計な列を削除するみたいな操作はOKでした。但し、A1に一度カーソルを持っていってアクティブにした状態で記録を開始しないと上手くいかないシーンがあったので、ここがちょっと癖がありますね。

図：マクロを記録中・・・

マクロを実行する

マクロは名前をつけると、「ツール」⇒「マクロ」の中に既に付けた名前で存在していますので、そのまま実行するだけです。但し、記録の仕方によっては、いきなり赤字でエラーがでたりします。まだちょっとこなれていないなぁという印象です。最初の１回だけは、承認してくださいウィンドウが出る点は、Google Apps Scriptと同じで挙動も同じです。

主にエラーが出るケースとしては、記録をする際に明示的にシートをクリックして、シート名をクリックして記録をしなかったり、該当のシートが消えてしまっていたりといったシーンが多いです。空のシートを複数つくっておいて、記録を開始する前には空シートから初めて、記録はまずシートの選択から行うところより記録を開始するとスムーズにコードが生成されます。

図：クリックするだけでマクロは実行されます。

マクロを管理

ここまでで作成した「マクロ」については前述の通りメニューに登録されています。後からこれらマクロについての情報を変更したい場合、マクロを管理をクリックして修正することができます。

できることは

マクロの名前の変更
Ctrl + Alt + Shift + 何かのキーをセットして、ショートカットキーで作動する設定
スクリプトを直接編集する機能
マクロを削除する機能

更新をクリックすることで内容を変更し保存することができます。ショートカットキーについては、入力欄に0〜9までの値を入力するようになっています。英語などは使えないようです。

※但し記録したGoogle Apps Scriptは削除されないようです。スクリプトエディタを開くと残ってました。

図：ここにズラッと記録した内容がでます

マクロをインポート

これは、自分で作成したGoogle Apps Scriptのコードをマクロとしてインポート＆登録する機能です。試しにメッセージボックスが出るだけの関数を自分で書いて取り込んでみました。自作のコードが無い場合にはグレーアウトの状態です。

マクロメニューの中の「インポート」を実行すると、自分が書き足したコードの名前が出てくるので、「関数を追加」をクリックします。これでマクロとして登録されました。マクロですので、マクロの管理でショートカットキーを割り当てれば、自分が作成したスクリプトにショートカットキーが割り当てられます。これはちょっと便利ですね。

マクロメニューに登録するだけの機能であるため、GASを書けるのでああるならば、onOpenで表示するスプレッドシートのメニューで構築したほうが自由度が高いのでオススメです。

あえて使う利点としては自作のコードでもショートカットキーを割り当てることができるという点。

図：マクロのインポート画面

作られたマクロの正体

スクリプトエディタで見てみる

マクロはスクリプトエディタを開くと閲覧・編集が可能になっています。全てmyFunctionに数字がついた形でFunction名が付けられており、表でマクロを削除しても、コード自体は残されています。また、そのコードの冒頭には「/** @OnlyCurrentDoc */」という文字列が付け加えられています。

さて、マクロのメニューに登録してる場所なのですが、スクリプトエディタで開いた時に出てくる「appsscript.json」の中に記述されています。閲覧や編集をするためにはスクリプトエディタの全般設定からappscript.jsonの表示をオンにする必要性があります。

このappsscript.jsonの該当箇所の内容ですが。

sheetsセクション内のmacrosセクション内に登録されています。
メニューはオブジェクトの配列といった形で追加されています。
menuNameがメニュー名、functionNameが関数の名前、defaultShortcutが設定したショートカットキーの組み合わせとしてセットされています。

通常このファイルを手作業で修正することは無いと思いますが、このような形でメニューが作られているということは頭の片隅にでも控えておくと良いかと思います。

{
  "timeZone": "America/New_York",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "sheets": {
    "macros": [{
      "menuName": "整形マクロ",
      "functionName": "myFunction2"
    }, {
      "menuName": "helloscript",
      "functionName": "helloscript"
    }]
  }
}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

{

"timeZone": "America/New_York",

"dependencies": {

},

"exceptionLogging": "STACKDRIVER",

"sheets": {

"macros": [{

"menuName": "整形マクロ",

"functionName": "myFunction2"

}, {

"menuName": "helloscript",

"functionName": "helloscript"

}]

}

sheetsの中にあるmacrosに名前と実行する関数名が登録されていました。手動でここに自分の関数を登録もできそうですね。自分が登録したhelloscriptがきちんとインポートされていました。マクロ.gsの中でなくとも大丈夫みたいですね。

図：マクロ.gsとして追記されてます

スクリプトトリガーを使えば・・・

作成したマクロは通常は手動で実行することが前提になっています。そのため、スプレッドシートを開く→メニュー操作してマクロを実行といった手順を人間が行ってあげる必要があります。一方で、作成したマクロはGoogle Apps Scriptそのものなので、例えば以下のようなケースの場合スクリプトトリガーを使って自動化したくなります。

外部からインポートするデータはimportrange関数で自身のスプシにロードしてる
定期的にこのデータを切り貼り・加工して自身の別のシートに転記してる
これを深夜0時に自動的にマクロで処理をさせたい

そのまま、スクリプトトリガーを使っての時間駆動型トリガーにてセットして実行するとほぼ実行に失敗します。

理由はマクロの記録で記録される処理は「対象のスプシがユーザによって開かれてる状態」を前提として記録されているため。この問題を解決するためにはスクリプトトリガーの注意点にも記載があるように

SpreadsheetApp.getActive()についてはSpreadsheetApp.openById("スプシのID").getSheetByName("対象のシート名")に置き換えて上げる必要がある。
スプシのIDやシート名はグローバル変数などで別途用意して変数に値を入れておき、上記のコードを修正する

といった手修正が必要になります。直接、スプシのID指定とシート名を指定してるので、ユーザが開かずともアクセスさせることが可能になります。このようにマクロの記録で生成したコードは、手修正を伴うケースが多々あります。

図：マクロにトリガーの合せ技

Google Apps Scriptでトリガーを活用しよう【GAS】

@OnlyCurrentDocについて

マクロの記録を実行して記録しコードを生成すると、コメントの形でコードの最上部に以下のような文字列が追加されます。

/** @OnlyCurrentDoc */

1	/** @OnlyCurrentDoc */

コメントなのでコードの実行に直接影響しないと思われがちですが、この１行は実は意味があります。その意味とは初回認証時の許可対象として

「このアプリケーションがインストールされているスプレッドシートの表示と管理」に限定される

という意味合いがあります。この効果なのですが

スプレッドシートの操作に限定して実行可能という状況になる
他人がこのスクリプトを実行時に「このアプリは Google で確認されていません」という確認ダイアログが出なくなる

アクセス承認が簡略化されるというメリットがあります。では、ここに例えばメールを送信するMailAppのコードを足したら動かなくなるのか？といったらそういったことは無く、その場合は追加でアクセス承認を求められるので、動作を阻害されるといった心配はありません。

図：自動で記録される文字

図：初回認証時の様子

コードの手直し

マクロの記録の大きな弱点としては「基本、スプシの操作を記録する点に限られる」ということ。例えば、ファイルメニューにある「PDFとしてダウンロード」であったり、「ファイルをメールで送信」であったり、操作をして記録をしたとしても該当の部分はコードとしては生成されることはありません。

よって、自動化ができると言っても相当限定された範囲内しか実現ができません。

スプシである程度自動化した「その先の自動化」は自身でGoogle Apps Scriptを使って実現する必要があります。マクロの記録はあくまでもGAS入門編の初歩の初歩を学ぶために限定し、その先を学んでなるべく早く卒業することが肝要です。

その先の自動化に踏み込まずにスプシの関数に逃げてみたり、結局人間が相当量手作業をやることが混じってる業務フローでは効果的なDXを実現することはできません。

また、同じスプシの操作といっても、1行ずつデータを処理していくような「ループ作業」は記録しようがないため、マクロの記録でできる自動化はかなり早い段階で限界が来ますので承知の上で使うようにしましょう（一行ずつコピペや作業を記録して動かすようなものを作ろうとするのは辞めましょう）。

VBAからの変換

Google Apps Scriptを始めるユーザの多くが、GAS初心者ではあってもVBAは経験しているといったような人は多いと思われます。そういったケースでGoogle Workspaceに乗り換えるといった場合、VBAで構築されたコードをGoogleスプレッドシートに「移植する」といった作業が必要になります。

しかし、VBAとGASとではそもそも言語体系も内容も異なります。両方の言語について学習が必要になりますが、移植はなかなか大変な作業とスキルを要求されます。そこでVBA→GASへ変換する手段は無いだろうか？ということで調査したものをまとめてみました。

Macro Converter

概要

Google Workspace Enterprise Plusという最上位の高価なプランでのみ利用することのできるGoogle公式が供出してるMacro Converterというアドオンが2020年10月にリリースされています。公式のドキュメントはこちらに掲載されています。

しかし、調査をしてみると

高額なEnterprise Plusでしか使えないため、この為だけに契約するのは不毛
実際に使ってみたが、僅かなコードであったにも関わらず、やたら長いコードに変換されたり複雑な変換されたりと評判がよろしくない
関数に日本語名を使ってるとエラーになる
利用者が圧倒的に居ないため、知見も必然的に殆ど存在しない
参照設定に色々入ってると変換すらされずに完了するようだ。

といった具合で、変換はできなくはないけれど、積極的に使いたいとは思えない。なぜEnterprise Plusに限定してるのかも理解できない。公式とは言えこのツールに期待するとかなりガッカリだと思います。

実際に使ってみたけれど

実際に、マクロの記録でModule1,2とVBAの入ってるxlsm形式をアップしてみましたが、結論から言うと極めて単純なマクロですら変換出来ていませんでした。

更にいうと、アップしたファイルを指定しても変換のリストに出てこず、手動で更新をしないといけない。おまけに変換結果のレポートは出てくるのだけれど、全部英語表記。自分が作ったGeminiで変換掛けるほうが何倍もマシです。

しかも変換結果の中にSheets APIとだけあってコードは１つもない。Enterprise Plusだけのスペシャリティがコレ？

図：追加しても更新しないと出てこない・・・

図：変換中の様子

図：レポートは何の役にも立たない

生成AIを利用する

生成AIを使ったコードの生成は、ゼロからの生成ではまだまだ懐疑的な現状です。おかしなコード、動かないコードが生成される点は本家本元でも同様。

一方で、すでに存在して動作してるVBAのコードをGASに変換するといったような「既存のものを変換するケース」では、割とまともな変換をして動作するコードが作成可能です。

VBAからマクロの標準Moduleの内容をコピー
Geminiなどでコードを変換する
Googleスプレッドシートに同じシート構成を作成するか？Excelから変換する
スクリプトエディタに変換したコードを貼り付ける

といったような手間をファイル毎に行う必要があるため、マクロが多数ある場合はそこそこの手間になります。また、VBAとGASの構造上の違いにより、メール送信であったり印刷系は変換が難しかったりできないケースもあるため、手修正が必要なこともあります。

これらの手間を大幅に減らす手法をGeminiで構築してみたので、Excelのアドオン化などの一手間は必要ですが、マクロの記録で作ったようなものであれば、GASへの変換はかなり手間が減らせると思います。

Macro Converterなどを使うよりかはずっとマシな結果は手に入るのではないかと思います。

図：Geminiで変換してみた

Geminiを使ってVBAからGoogle Apps Scriptに変換する仕組みを作る【GAS】

RPAで代用

VBAでの自動化の変換というわけではないのですが、変換以外の手段としてGoogleスプレッドシートをRPAで操作して記録するという手法を考える人もいるかと思います。例えばWindows11標準搭載のMicrosoft Power Automate for Desktopなどを利用して、自動化が実現できるか？という課題に対して、実際に検証して見た結果は以下のとおりです。

ローカルアプリではなくWebブラウザの操作となるので、Excelの操作と違い細かな制御は基本できません。
レコーダーを使ってマウス操作を記録した場合、メニューの操作や印刷ダイアログ、セルの操作などは正しく記録されません。
キー送信フローを使った場合、割と正しく操作はできるものの、すべてキーボードおよびショートカットキーの組み合わせで操作を手動で作成しなければならず煩雑です。
Google Apps Scriptで作成したWebアプリケーションの操作は、レコーダーを使って記録させ動作させることは可能です。
Google Apps ScriptではなくGoogle Sheets APIを利用してのREST API操作はやや高度になりますが正確な操縦を実現可能です。

となっています。

よって、RPAで実現は可能とは言え、直接的にセルをマウス操作して記録といったことはできない為、実質REST APIを操作して目的の作業を実現する手法一択になるかと思います。

図：GASでやったほうが近道です

動画資料

現場の人がマクロを記録して、作業を自動化する業務最適化を実践する動画。RPAなど使う前に、まずはマクロの記録でノーコストで業務を最適化するべきでしょう。

Googleスプレッドシートのマクロを使ってみた

この動画を YouTube で視聴.

【誰でもできる自動化】スプレッドシート「マクロの記録」でルーティン業務を効率化しよう。【GoogleWorkspace #91】

この動画を YouTube で視聴.

Googleスプレッドシートのマクロの記録機能を使ってみた

今回使用するスプレッドシート