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

ピータードラッカー

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

時間を有効に活用するために、効率と生産性を向上させる方法はたくさんあります。 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を押して値を貼り付けます

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

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

GoogleAppsScriptを手動で作成する

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

AppsScriptのプログラミング言語は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 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が最上位のオブジェクトです。上記のコードスニペットでは、編集する要素（この場合はテキストボックス内のテキスト）に到達するために、この階層を段階的に移動する必要があります。実際には、これは、最終的に編集する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バージョンは古く、機能があります。オンラインスクリプトエディタの数は非常に限られています。マクロを記録したり、数十行を書き込んだりしているだけでは、実際には気付かないでしょう。ただし、週次または月次のレポートのすべての側面を自動化する野心的な計画がある場合、またはプラグインを構築したい場合は、お気に入りの開発環境を使用して開発できるコマンドラインツールがあることを知って喜ぶでしょう。 。

このようなレベルの習熟度がある場合は、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ノートブックはインタラクティブであるため、各行またはコードの小さなブロックを実行した後に結果（またはエラーメッセージ）が表示されることです。