財務プロセス

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

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

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

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

別の記事で、私はどのように Python プログラミング言語は、金融専門家にとって強力な分析およびタスク自動化ツールになり得ます。

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

ベンチャーキャピタル会社はいくつありますか

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

スクリプトエディタ

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

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

これで、コードの変更を開始できます。必要な変更は軽微ですが、これを初めて行う場合でも、Sheets AppsScriptをすばやく検索する必要がある場合があります。ドキュメンテーションおよび/または日付の操作などの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つの理由でさらに高度になりました。

スプレッドシートに加えて、Googleスライド（およびGoogleドライブ）の操作方法を理解する必要があります。
スライドでは、または一般的にG Suiteアプリ間で作業する場合、「マクロの記録」機能は利用できません。これは、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 templateId = 'insert_your_template_presentation_id_here'; 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 トップレベルのオブジェクトであること。上記のコードスニペットでは、編集する要素（この場合はテキストボックス内のテキスト）に到達するために、この階層を段階的に移動する必要があります。実際には、これはプレゼンテーション、滑り台、 PageElement 、および形状オブジェクト、最終的に到達するまで TextRange 編集するオブジェクト。

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

プレゼンテーションを作成してタイトルを更新したら、新しいグラフの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バージョンは古く、オンラインスクリプトエディターの機能は非常に制限されています。マクロを記録したり、数十行を書いたりしているだけでは、実際には気付かないでしょう。ただし、週次または月次のレポートのすべての側面を自動化するという野心的な計画がある場合、またはプラグインを構築したい場合は、コマンドラインツールこれにより、お気に入りの開発環境を使用して開発できます。

このようなレベルの習熟度をお持ちの場合は、JavaScriptが提供する最新の機能を利用することもできます。コマンドラインツールを使用すると、 TypeScript 。

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

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

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

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

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

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

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