技術

GoogleスプレッドシートとAppsScriptの操作

スプレッドシートは、現在のビジネスの世界で最も一般的なツールの1つです。表計算ソフトウェアには複数の実装があり、それらすべてにいくつかの違いがありますが、どれを使用する場合でも、それらに依存する作業を行う人にとっては、それらは絶対に不可欠です。

「スプレッドシート：習得が容易で、習得が難しい」

スプレッドシートの操作を開始するのは簡単ですが、スプレッドシートが提供するすべての強力な機能を使用するためのスキルを習得するには時間がかかります。数式、関数、ピボットテーブルなどに慣れてきたら、さらに1つのレベル、つまりカスタム拡張機能を実現できます。製品が異なれば、拡張機能を作成するためのインターフェースも異なります。この記事では、Google AppsScriptと呼ばれるドキュメントを操作するための使いやすいインターフェースを提供するGoogleスプレッドシートに焦点を当てます。 JavaScriptのサブセットで記述されており、他のGoogleアプリやサービスとの統合が可能です。

GoogleスプレッドシートのAppsScriptがとてもクールなのはなぜですか？

スプレッドシートの柔軟性は、起業家や新興企業にとって素晴らしいツールになります。追加のAppsスクリプトを使用すると、拡張機能の可能性が無限に広がります。初めて便利だと思ったのは約7年前。私は編集者のグループでウェブサイトを運営していて、特定の目標を設定して測定したかったのです。 Analytics、AdSense、Facebookのデータを1ページに含むカスタムレポートを作成する簡単な方法はありませんでした。また、毎日更新される最新のデータに基づいてレポートを作成することも重要でした。最初のアイデアは、スプレッドシートを作成してそこにすべての数値を保持することでしたが、それを最新の状態に保つことは困難でした。調査の結果、Appsスクリプトが解決策になる可能性があることがわかりました。これにより、ドキュメントの変更、サードパーティサービスからのデータのフェッチ、およびワークフロー全体の自動化が可能になります。それ以来、さまざまなユースケースを処理するために数十のスプレッドシートを作成してきました。

実際のユースケース

以下に、独自のユースケースを作成するきっかけとなる可能性のある実際のユースケースを示します。

一般的な一般的なユースケースは、アクセス可能なRESTAPIから情報を取得することです。これはオンデマンドで実行できますが、cronのようなメカニズムを使用して設定された間隔で実行することもできます。
React Nativeアプリを作成するには、技術者以外のモデレーターが構成ファイルの小さなチャンクを編集できるようにする必要がありました。単純なJSONのようなファイルがまさに必要だったので、私の解決策は、スプレッドシートを作成し、特定のセルからデータを収集するメソッドを作成し、サーバーの構成を更新することでした。
銀行からのアカウントトランザクションのインポート-銀行APIを使用するか、Puppeteerなどのツールを使用して銀行ページをスクレイプし、データをJSONにエクスポートして、スプレッドシートに自動的にインポートできます。
アプリやダッシュボードのプロトタイプをすばやく開発します。インターフェースをすばやく作成するのは簡単で、最初に機能するプロトタイプを準備してアイデアを検証するための優れたツールになります。
Googleフォームの回答をスプレッドシートからSlackに転送します。
割り当てを自動化し、リマインダーを電子メールで送信し、受信パッケージの情報を追跡し、スプレッドシートから全員をループに保つことで、シークレットサンタグループを作成します。

可能性は無限大。詳細を見て、サンプル拡張機能を作成しましょう。

プロジェクト

この記事では、提供されたVATID番号を使用して検証する簡単な拡張機能を作成します。 VIESシステム欧州連合によって作成されました。 Googleは、小さな拡張機能に役立つWebベースのスクリプトエディタを提供していますが、多くの制限もあります。

Googleのスクリーンショット

その代わりに、Googleが作成した別のツールを使用します。クラスプ。これは、AppsScriptツールのローカル開発を支援するコマンドラインツールです。このようにして、コードをGitリポジトリに保持し、他のユーザーと共同作業することができます。 Googleドキュメントの開発者。また、展開にも役立ちます。

Claspは、Node.jsに基づくコマンドラインツールです。まず、次の方法でインストールする必要があります。

npm i @google/clasp -g

このツールを使用するには、Googleアカウントにログインする必要があります。 Claspで作成されたプロジェクトはすべて、このGoogleアカウントに関連付けられます。単に電話してください：

clasp login

そして、新しいブラウザタブが開きます。サインインすると、新しいプロジェクトを開始する準備が整います。

mkdir vat-id-validator; cd vat-id-validator; clasp create 'VAT ID Validator'

スクリプトタイプの入力を求められ、sheetsと答える必要があります。 Apps ScriptAPIを有効にするように求められる場合もあります。その場合は、画面の指示に従ってください。正常に終了すると、ドキュメントとスクリプトエディタへのリンクが提供されます。例：

Created new Google Sheet: https://drive.google.com/open?id=1Do83dksmRvsFPuGbaOS8tMPdyZEHvGuWWdDwMuEp8tA Created new Google Sheets Add-on script: https://script.google.com/d/1gfjbe875R1VPmxf30zA3DAcKy_4qpK5XHsmwbzDt6JWb24P3p17EYk2s/edit

両方のURLを書き留めてください。後で必要になります。これで、お気に入りのエディターでフォルダーを開き、ハッキングを開始する準備が整いました。

財務におけるCFOとは

こんにちは世界

簡単なことから始めましょう。新しいファイルを作成し、それをindex.jsと呼び、次のコンテンツを貼り付けます。

function validate() { console.log('hello world') }

それでは、コードをプッシュしましょう。

clasp push

そして、ブラウザでスクリプトエディタを開きます（ステップ作成のリンクを取得しました）。ここで、コードを実行してテストできます。選択する 実行>関数の実行>検証 。

ヒント： Claspを使用すると、CLIから直接関数を実行できます。プロジェクトの追加セットアップが必要です。あなたはより多くの情報を見つけるでしょうここに。

すべてが大丈夫なら…何も起こりません！ログはStackdriverLoggingツールに保存されます。あなたはそれを介してアクセスすることができます 表示> Stackdriverログ 、ただし、コンソールから実行する方が便利です。単に電話してください：

clasp logs

初めて、クラスプはプロジェクトIDを要求します。取得するには、スクリプトエディタに戻り、[ リソース> CloudPlatformプロジェクト 。ポップアップには、から始まるIDが表示されます。 プロジェクトID-xxxxxxxxx 。 ID全体をコピーしてコンソールに貼り付けます。

しばらくすると、ログに記録された「helloworld」がコンソールに表示されます。

ヒント： 両方のコマンドclasp pushおよびclasp logs --watchを受け入れるフラグ。開発中に役立つ場合があります。

VATIDバリデーター

検証のコードを作成する時が来ました。 VIESはパブリックSOAPAPIを提供します。これは、JSからの作業が最も簡単ではありませんが、私たちのニーズには問題ありません。次のコードでindex.jsを更新します。

function validate() { const ret = makeCall('PL', '8522604586') console.log(ret) return ret } function makeCall(countryCode, vatNumber) { // Create SOAP message for WDSL: http://ec.europa.eu/taxation_customs/vies/checkVatService.wsdl var message = '' + '' + ' ' + ' ' + ' ' + countryCode + '' + ' ' + vatNumber + '' + ' ' + ' ' + '' // Use UrlFetchApp (https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app) to send POST request var xml = UrlFetchApp.fetch('http://ec.europa.eu/taxation_customs/vies/services/checkVatService', { method: 'POST', contentType: 'text/xml', payload: message }).getContentText() // the response is XML, which can be parsed with XmlService (https://developers.google.com/apps-script/reference/xml-service/) var document = XmlService.parse(xml); var mainNs = XmlService.getNamespace('http://schemas.xmlsoap.org/soap/envelope/'); var checkVatResponseNs = XmlService.getNamespace('urn:ec.europa.eu:taxud:vies:services:checkVat:types'); var root = document.getRootElement().getChild('Body', mainNs).getChild('checkVatResponse', checkVatResponseNs); // Extract interesting information var isValid = root.getChild('valid', checkVatResponseNs).getText() var companyName = root.getChild('name', checkVatResponseNs).getText() var companyAddress = root.getChild('address', checkVatResponseNs).getText() return { isValid: isValid, companyName: companyName, companyAddress: companyAddress } }

関数validateは別の関数を呼び出します（ makeCall ）VIESに接続し、提供されたVATIDに基づいて会社の詳細を取得します。これで、validateの実行を再試行できます。追加の許可を求められるので、同意する必要があります。ログを確認すると、新しいエントリが含まれます。

{isValid=true, companyName=BUSHIDO GAMES SPÓŁKA Z OGRANICZONĄ ODPOWIEDZIALNOŚCIĄ, companyAddress=ANDRZEJA MAŁKOWSKIEGO 30 M1 70-304 SZCZECIN}

ヒント： Claspを使用すると、TypeScriptでコードを記述できます。このようにして、矢印関数、クラス、型指定などにアクセスできます。あなたはより多くの詳細を見つけることができますここに。

スプレッドシートとの統合

これまでのところ、外部APIを呼び出すコードがありますが、スプレッドシートとは統合されていません。それを変える時が来ました。アイデアは、ドキュメント内の複数のセルを選択してから、検証メカニズムを呼び出すことを可能にすることです。検証済みのVAT番号は、緑または赤で強調表示されます。

index.jsのvalidate関数を次のコードと交換します。

function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var menuEntries = [{name: 'Validate', functionName: 'validate'}] ss.addMenu('VAT ID Validator', menuEntries); } function validate() { // Take current selection var range = SpreadsheetApp.getActiveSpreadsheet().getSelection().getActiveRange(); // Iterate through range var numRows = range.getNumRows(); var numCols = range.getNumColumns(); for (var i = 1; i <= numRows; i++) { for (var j = 1; j <= numCols; j++) { var cell = range.getCell(i, j); var currentValue = cell.getValue(); // check if the cell value has VAT ID format var parsed = currentValue.match(/([A-Z]{2})(w+)/); // if it's not VAT ID, skip it if(!parsed){ continue } // make call to VIES var validated = makeCall(parsed[1], parsed[2]) // Add colors if(validated.isValid){ // as addition we can add a note with company name and address retrieved from VIES cell.setNote(validated.companyName + '

' + validated.companyAddress) cell.setBackgroundColor('#dfffdb'); } else { cell.setBackgroundColor('#e6b8af'); } } } }

新しい関数に気付くかもしれませんonOpen追加されました。新しいメニューエントリが作成されます。スプレッドシートドキュメントを開きます（clasp createを呼び出した後にリンクを取得しました）。完全に読み込まれると、メニューに新しい位置が表示されます。 VATIDバリデーター 単一のオプションで、検証。それをクリックすると、Googleは追加の権限を承認するように求めます。同意すると、スクリプトは実行されますが、何も起こりません。これは、検証にVATIDが必要なためです。遊ぶべきいくつかがあります：

PL8522604586
IE6388047V
NL813981487B01
IT7863930017
DE123456789

それらをスプレッドシートに貼り付け、5つのセルをすべて選択して、をクリックするだけです。検証もう一度。 Voilà！最初の3つは有効であり、緑色で強調表示されているはずです。他の2つは、有効なVAT ID番号ではないため、赤になります。

追加の演習

上記は完全な拡張である可能性がありますが、より多くの練習を探している場合は、バリデーターを追加してみてください。たとえば、選択したセルに有効な米国納税者識別番号またはクレジットカード番号が含まれているかどうかを検証できます。 2つのアプローチを取ることができます：

上記のVATIDの場合と同様に、検証を処理するAPIを見つけます。これは、データ形式をチェックするだけでなく、番号が正式に登録されているかどうかを検証する必要があるTINの場合はより正確になります。
独自のロジックをAppsスクリプトに直接記述します。クレジットカードの検証では、特定のルールに従うことができ、それらのほとんどは、 Luhnアルゴリズム。

これは紹介に過ぎず、上記の例は非常に単純ですが、Appsスクリプトのビジネスロジックははるかに高度な場合があることに注意してください。カスタムJSライブラリを使用できます。これは、より複雑なタスクを実行するのに役立ちます。私がよく使用するものの1つはMoment.jsです。VanillaJSで日付を操作するのは恐ろしい経験です。

スクリプトがより一般的な問題を解決する場合に実行できるもう1つのことは、Appsスクリプトをアドオンとしてデプロイすることです。これにより、スクリプトを他のユーザーと共有できるようになります。アドオンを組織とのみ共有するか、すべての人に公開するかを選択することもできます。アドオンライブラリに表示され、どのスプレッドシートでも使用できるようになります。

デプロイプロセスは複雑ではなく、最初のステップはテスト用のコードを準備することです。あなたはそれについてのより多くの情報を見つけることができますここに。行く準備ができていると判断した場合は、アドオンを公開する。コードを公開することを選択した場合は、アプリの公開の基準に準拠していることを確認するために、Googleチームによってコードがレビューされることに注意してください。