マクロをマスターするためのGoogleAppsスクリプトチュートリアル

公開: 2022-03-11

効果的な幹部は、時間が制限要因であることを知っています…おそらく、時間の優しい愛情のこもったケアほど効果的な幹部を区別するものはありません。

ピータードラッカー

時間は私たちの最も貴重なリソースです。 私たちは、通常最も高い金銭的価値をもたらすだけでなく、継続的に自分自身に挑戦し、仕事の満足度を最大化するために、可能な限り最も影響力があり、最も価値の高い活動にそれを費やしたいと考えています。

時間を有効に活用するために、効率と生産性を向上させる方法はたくさんあります。 Googleスプレッドシートに関する前回の記事では、オンラインコラボレーションの力が生産性を向上させるためのそのような鍵の1つである方法について詳しく説明しました。

別の記事では、Pythonプログラミング言語が金融専門家にとって強力な分析およびタスク自動化ツールになる方法を示しました。

これからインスピレーションを得て、GoogleAppsScriptチュートリアルを紹介したいと思います。 Google Apps Scriptを使用すると、JavaScriptでスクリプトやプログラムを記述して、スプレッドシート、ドキュメント、スライド、Gmail、ドライブなど、GoogleのG Suiteの製品を自動化、接続、拡張できます。 スクリプトを書くのと同じように、それを学ぶには時間の投資が必要ですが、生産性が向上し、それが開く追加の機会はそれを十分に価値のあるものにします。

最初のステップとして、おなじみの概念であるマクロを見てみましょう。

Googleスプレッドシートでのマクロの記録と使用

Excelでの作業にかなりの時間を費やした場合は、ある時点でExcelのVBA（Visual Basic for Applications）マクロインターフェイスに接触することになります。 自分で録音または書き込みするか、他の人が作成したものを便乗させることによって。

マクロは、反復的で退屈なワークフローを自動化するための優れた方法です。 VBAは、学習に多くの時間を費やした言語ではないかもしれませんが、その美しさは、生産性を高めて独自のマクロを作成するために実際に必要がなかったことです。 自動化するワークフローを記録してから、コードに移動して、マクロをより一般的にするために必要な小さな変更を加えることができます。

ある意味で、VBAは、技術者以外の人にコーディングを紹介する方法についての、忘れられがちな素晴らしいレッスンです。 アクションを記録し、後で確認するためにコードを入力する方法は、実際、教科書を読んだり、チュートリアルを受動的に見たりすることを学ぶ、はるかに実用的な方法です。

VBAと同じ記録機能がGoogleスプレッドシートで利用できます。 使用方法の簡単な例を次に示します。

IMPORTHTMLクエリを使用してテーブルをインポートし、いくつかのサンプルデータから始めましょう。 この例では、世界で最大の15のヘッジファンドのリストをウィキペディアからダウンロードしました。 言うまでもありませんが、これは恣意的な例です。 意図は、主題よりもアプリケーションにもっと集中することです。

テーブルのインポートに使用されるサンプルデータ。

マクロ記録プロセスは、次のメニューパスを介して開始されます：[ツール]>[マクロ]>[マクロの記録]。

次に、記録するアクション（PC形式）について説明します。

  1. 最初の行を選択します
  2. Shift +Ctrl+下矢印を押してすべてを選択します
  3. Ctrl+Cでコピー
  4. Shift + F11を押して、新しいシートを作成します
  5. シートに新しい名前を付けます
  6. Shift + Control+Vを押して値を貼り付けます

完了したら、下部にあるマクロウィンドウの[保存]ボタンを押して、名前とオプションのキーボードショートカットを指定します。

これらの同じ手順で正確に複製できる単純なアクションの場合、プロセスはここで終了し、マクロの使用をすぐに開始できます。 ただし、この場合、コードを使用する前にいくつかの変更を加える必要があります。 たとえば、コピー先のシートには、毎回異なる名前を付ける必要があります。 これを行う方法を見てみましょう。

GoogleAppsスクリプトを手動で作成する

これで、GoogleAppsScriptの骨が初めて表示されます。 Googleのサーバーで実行されるプログラミングプラットフォーム。 これにより、マクロが強化され、アプリケーション自体の非常に複雑なワークフローやアドオンを作成できるようになります。 スプレッドシートの作業だけでなく、実際にはGoogleのGSuite内で相互接続されているほとんどすべてのものを自動化するために使用できます。

Apps Scriptのプログラミング言語はJavaScriptです。これは、最も人気のあるプログラミング言語の1つです。つまり、広範囲に学習したい人のための豊富なリソースがあります。 ただし、VBAの場合と同様に、実際に行う必要はありません。同じ記録機能を使用して、自動的に繰り返すことができるようにする手順を実行するだけです。 レコーディングからの出力は粗雑に見えるかもしれず、おそらくあなたがやりたいことと完全には一致しないでしょうが、それは十分にしっかりした出発点を提供します。 記録したスクリプトに対して実行してみましょう。

録音するときは、最終的な録音でキャプチャしたくない追加のステップを誤って録音しないように注意するのが理にかなっていますが、回避するのが難しい場合があります。停止を押す前に別のセルを選択するだけの簡単な方法です。スクリプトを実行するたびに、記録ボタンがキャプチャされ、その後繰り返されます。 スクリプトを編集するときの最初のステップは、スクリプトをクリーンアップして、そのようなステップを削除することです。 ファイルメニューの[ツール]>[スクリプトエディター]に移動して、詳しく見ていきましょう。

スクリプトエディタ

JavaScriptを知っていれば、これをすぐに認識できます。また、最新のJavaScriptで見られるように、「let」や「const」ではなく「var」キーワードが表示されることに驚かれるかもしれません。 これは、Apps ScriptのJavaScriptバージョンが非常に古く、言語の最近の機能の多くをサポートしていないという事実を反映しています。 最後に、最新の言語機能を使用したい場合の回避策を紹介します。

スクリプトを初めて実行すると、承認が求められます。これは、スクリプトがすべてのデータを変更（および場合によっては削除）する可能性があるためです。 ほとんどの場合、他のGoogle製品からの承認プロセスに気付くでしょう。

これで、コードの変更を開始できます。 必要な変更は軽微ですが、これを初めて行う場合でも、Sheets Apps Scriptのドキュメントをすばやく検索したり、日付の操作などのJavaScriptの概念をすばやく検索したりする必要がある場合があります。 ここで、JavaScriptが非常に普及している言語であるという事実が役立ちます。検索語を簡単な方法で表現すると、通常、直面する問題や機能が頭に浮かぶ問題の解決策をすばやく見つけることができます。

このバージョンのスクリプトに元の記録されたバージョンから加えられた変更は、作成する新しいシートのハードコードされた名前の代わりに、代わりに今日の日付で名前を付けることです。 さらに、この新しいシートを参照するために、最後のコピーパスも変更します。 最後の4行は、セルの値の変更、列のサイズ変更、グリッド線の非表示など、いくつかの書式設定操作を実行する方法も示しています。

 function createSnapshot() { var spreadsheet = SpreadsheetApp.getActive(); var date = new Date().toISOString().slice(0,10); var destination = spreadsheet.insertSheet(date); spreadsheet.getRange('HTML!A1:F1').activate(); spreadsheet.getSelection() .getNextDataRange(SpreadsheetApp.Direction.DOWN) .activate(); spreadsheet.getActiveRange() .copyTo(SpreadsheetApp.setActiveSheet(destination) .getRange(1,1), SpreadsheetApp.CopyPasteType.PASTE_VALUES,false); var sheet = spreadsheet.setActiveSheet(destination) sheet.getRange("D1").setValue("AUM $bn") sheet.setHiddenGridlines(true); sheet.getRange("A1:D1").setFontWeight("bold"); sheet.autoResizeColumns(1, 4); };

ここでスクリプトを実行すると、新しいシートに実際に今日の日付の名前が付けられ、メインシートから値（数式ではなく）としてコピーされた情報が含まれていることがわかります。

同じ記録プロセスを使用して、チャートの視覚化を追加できるようになりました。 これを使用して、3つの簡単なグラフを作成しました。

チャートの視覚化。

それぞれのコードをクリーンアップすると、次のようになります。

 function createColumnChart() { var spreadsheet = SpreadsheetApp.getActive(); spreadsheet.getRange('C1:D16').activate(); var sheet = spreadsheet.getActiveSheet(); chart = sheet.newChart() .asColumnChart() .addRange(spreadsheet.getRange('B1:D16')) .setMergeStrategy(Charts.ChartMergeStrategy.MERGE_COLUMNS) .setTransposeRowsAndColumns(false) .setNumHeaders(-1) .setHiddenDimensionStrategy(Charts.ChartHiddenDimensionStrategy.IGNORE_BOTH) .setOption('useFirstColumnAsDomain', true) .setOption('curveType', 'none') .setOption('domainAxis.direction', 1) .setOption('isStacked', 'absolute') .setOption('series.0.color', '#0b5394') .setOption('series.0.labelInLegend', 'AUM $bn') .setPosition(19, 6, 15, 5) .build(); sheet.insertChart(chart); };

繰り返しになりますが、一部のオプションが混乱しているように見えても心配しないでください。これはすべて自動生成されます。不要な手順を削除し、後で微調整するために十分に理解する必要があります。

高度なGoogleAppsスクリプトの例：スプレッドシートをGoogleドライブとスライドに接続する

すべてが形になり始めていますが、実際に必要な出力がスプレッドシートではなくプレゼンテーションである場合はどうでしょうか。 その場合、ここからの作業のほとんどはまだ手動で行われる可能性があり、これを定期的に行う必要がある場合は、多くの時間を節約できませんでした。

次に、スプ​​レッドシートのサンプルデータを使用して、プレゼンテーションの作成を自動化するとどのように見えるかを調べてみましょう。

この演習は、次の2つの理由でさらに高度になりました。

  1. スプレッドシートに加えて、Googleスライド（およびGoogleドライブ）の操作方法を理解する必要があります。
  2. スライドでは、または一般的にG Suite Apps間で作業する場合、「マクロの記録」機能は利用できません。 つまり、スクリプトを最初から作成するには、Apps Scriptについて十分に理解している必要があります（また、各G Suite製品のドキュメントを快適にナビゲートする必要があります）。

この次の例は、開始して慣れるための基本的な構成要素を提供することを目的としています。

まず、スクリプトを使用して後でコンテンツを入力するテンプレートを作成しましょう。 これが私がまとめた2つの簡単なプレゼンテーションスライドです。

週報テンプレート。

次に、スクリプトで参照する必要があるため、このテンプレートのIDを取得する必要があります。 このIDは、実際にはブラウザのURLに表示されるランダムに見える文字と数字のシーケンスであるため、無意識のうちに何度も表示されます。

https://docs.google.com/presentation/p/ this_is_your_presentation_ID /edit#slide=id.p

次に、元のスクリプトに次の行を追加する必要があります。 これにより、今度はGoogleドライブにアクセスするための認証が再度求められます。

 function createPresentation() { var template; var template = DriveApp.getFileById(templateId); var copy = template.makeCopy("Weekly report " + date).getId(); var presentation = SlidesApp.openById(copy); }

このコードスニペットを実行してもすぐに視覚的なフィードバックは表示されませんが、テンプレートを保存したGoogleドライブのフォルダを見ると、そのコピーが実際に作成されており、今日のファイル名の日付。 良いスタートを切りました！

次に、より多くのビルディングブロックを使用して、手動ではなくプログラムでコンテンツを埋め始めましょう。 同じ関数に次の行を追加します。

 presentation.getSlides()[0] .getPageElements()[0] .asShape() .getText() .setText("Weekly Report " + date);

最初のページを今日の日付を含むように変更したため、状況はもう少し興味深いものになっています。 スライドでは、スプレッドシートと同様に、それぞれがプロパティとメソッド（つまり、アタッチされた機能）を持つオブジェクト（クラスで表される）を操作します。 これらは階層に編成されており、SpreadsheetsApp、DriveApp、またはSlidesAppが最上位のオブジェクトです。 上記のコードスニペットでは、編集する要素（この場合はテキストボックス内のテキスト）に到達するために、この階層を段階的に移動する必要があります。 実際には、これは、最終的に編集するTextRangeオブジェクトに到達するまで、Presentation、Slide、PageElement、およびShapeオブジェクトを介して到達することを意味します。

扱っているオブジェクトのタイプを追跡することは混乱を招く可能性があり、間違ったオブジェクトに操作を適用しようとした結果として生じるバグを解決するのは難しい場合があります。 残念ながら、スクリプトエディタ自体のヘルプ機能とエラーメッセージは、ここで常に多くのガイダンスを提供するとは限りません。そのような注意が少なくとも品質管理の実践を改善するという銀色の裏打ちです。

プレゼンテーションを作成してタイトルを更新したら、新しいグラフの1つをプレゼンテーションに挿入します。 オブジェクトの階層を念頭に置いて、次のコードが意味をなすようになりました。

 var spreadsheet = SpreadsheetApp.getActive(); var sheet = spreadsheet.getSheetByName(date); var chart = sheet.getCharts()[0]; var position = {left: 25, top: 75}; var size = {width: 480, height: 300}; presentation.getSlides()[1] .insertSheetsChart(chart, position.left, position.top, size.width, size.height);

完全なスクリプトを実行すると、出力プレゼンテーションは次のようになります。

サンプルプレゼンテーション

うまくいけば、この例は原理を説明し、独自の実験を開始するためのインスピレーションを提供します。 あなたがそれについて考えるならば、あなたは本当にこの方法で自動化されるべきである今日あなたの会社で行われた手作業の少なくともいくつかの例を見つけることができると確信しています。 ある形式や場所から別の形式にデータを機械的にシャッフルするのではなく、考え、分析し、判断を適用するための時間を解放するのに役立ちます。 開発エクスペリエンスの向上前述のように、Google Apps ScriptでサポートされているJavaScriptバージョンは古く、オンラインのScriptEditorの機能は非常に制限されています。 マクロを記録したり、数十行を書き込んだりしているだけでは、実際には気付かないでしょう。 ただし、週次または月次のレポートのすべての側面を自動化する野心的な計画がある場合、またはプラグインを構築したい場合は、お気に入りの開発環境を使用して開発できるコマンドラインツールがあることを知って喜ぶでしょう。 。

このようなレベルの習熟度がある場合は、JavaScriptが提供する最新の機能を利用することもできます。コマンドラインツールを使用すると、TypeScriptで開発することもできるため、さらに多くの機能を利用できる可能性があります。

GoogleスプレッドシートプログラミングにPythonを使用する

Apps Scriptでの作業があなたのお茶ではないことがわかった場合は、ユースケースに応じて他のオプションがあります。 より高度な数値計算を実行したり、APIやデータベースに接続したり、JavaScriptよりもPythonプログラミング言語を好んだりする場合は、GoogleのColaboratoryが貴重な製品です。 これにより、Googleのサーバーで実行されるJupyterノートブックが提供され、Googleドライブファイルとシームレスに統合するPythonスクリプトを記述でき、「gspread」ライブラリを介してスプレッドシートデータを簡単に操作できます。

Pythonを財務機能に使用する方法についての記事で、Pythonの利点の多くを概説しました。これは、ビジネスおよび財務のコンテキストでPythonおよびJupyterノートブックを操作するための穏やかな入門書としても役立ちます。 私にとって非常に重要な利点の1つは、Apps Scriptとは異なり、ColaboratoryのPythonノートブックはインタラクティブであるため、各行またはコードの小さなブロックを実行した後に結果（またはエラーメッセージ）が表示されることです。

自動化は中毒性があります

このGoogleAppsScriptチュートリアルでは、Googleのコーディング言語で何ができるかを垣間見ることができました。 可能性は事実上無限です。 ただし、技術的なバックグラウンドがない場合、コード例は気が遠くなるように見える可能性があり、Google Apps Scriptの学習によって得られる生産性の向上は、必要な時間の点で多額の投資を上回るのに十分ではない可能性があると考えているかもしれません。それを学ぶために。

もちろん、これは、あなたが将来どのような役割を担うか、または持つことを期待するかなど、多くの要因に依存します。 ただし、ここに示す例のようなことを期待していなくても、何が可能で、実装にどれだけの作業が必要かを理解しておくと、会社の生産性を向上させる方法についての考えやアイデアが生まれます。あなたのクライアント、またはあなた自身。

個人的には、座ってボタンを押すだけで、1時間に相当する面倒な手作業を1分以内で完了できるという満足感を証明できます。 これを50回行った後、最初にすべてをまとめて数時間費やしたことに感謝します。これにより、最終的には、より付加価値の高い追求のために時間を解放することができます。 しばらくすると、これらのスケーラビリティの利点は中毒性になります。