google app scriptで背景色の置換
google app scriptで背景色の置換スクリプトを書いたのでメモします。以下の内容が含まれています。
- 独自(「拡張ツール」)メニューの追加
- モーダル(背景色置換モーダル)の表示
- 初期値の挿入(テンプレートhtmlの使用)方法
概要
主にgoogleのガイドを参考にして作成しました。とりあえずできることと、コードを記載します。
できること；
- スプレッドシート内での背景色の置換、置換実行モーダルの表示
- 選択したセルの背景色を置換元としてデフォルト値として挿入
そんなこといいから、という方は、スプレッドシートを開いて、「ツール」＞「スクリプトエディタ」を選択し、スクリプトエディタのgsファイルにjavascriptコード、新規作成でhtmlファイルを作成しテンプレートファイル(index.html)を貼り付けてください。貼り付けたあとリロードするとメニューに「拡張ツール」が現れるかと思います。現れない場合はコメント頂けると幸いです。。。
コード；
main.gs
function onOpen() { SpreadsheetApp.getUi() .createMenu('拡張ツール') .addItem('背景色置換', 'showModal') .addToUi(); } function showModal() { var background = SpreadsheetApp.getActiveSheet().getActiveCell().getBackground(); var template = HtmlService .createTemplateFromFile('index.html'); template.from = background; var html = template .evaluate() .setWidth(300) .setHeight(150) .setSandboxMode(HtmlService.SandboxMode.IFRAME); SpreadsheetApp.getUi().showModalDialog(html, 'Replace background color'); } function replaceBackgroundColor(from, to) { var sheet = SpreadsheetApp.getActiveSheet(); var data = sheet.getDataRange(); var backgrounds = data.getBackgrounds(); var replacedCount = 0; for(var row = 0; row < backgrounds.length; row++) { for(var col = 0; col < backgrounds[row].length; col++) { if (backgrounds[row][col] != from) continue; sheet.getRange(row + 1, col + 1).setBackground(to); replacedCount++; } } ui = SpreadsheetApp.getUi(); ui.alert('Successfully replaced!', replacedCount + ' cell\'s background color replaced to ' + to + ' from ' + from, ui.ButtonSet.OK); }
index.html
<!DOCTYPE html> <html> <head> <base target="_top"> <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons.css"> <script> function handleClick() { document.getElementById('replace').setAttribute("disabled","disabled"); var form = document.getElementById('form'); var from = form.querySelector('input[name="from"]').value; var to = form.querySelector('input[name="to"]').value; if (!from || !to) return showError('Input #from and #to'); google.script.run .withSuccessHandler(closeModal) .withFailureHandler(showError) .replaceBackgroundColor(from, to); } function closeModal() { google.script.host.close(); } function showError(message) { document.getElementById('error').innerHTML = 'Error: ' + message; document.getElementById('replace').removeAttribute("disabled"); } </script> <style type="text/css"> html, body, #form { height: 100%; } </style> </head> <body> <div id="form" style="display: flex; justify-content: space-between; flex-direction: column;"> <div style="display: inline-flex; justify-content: flex-end"> <label for="from" style="flex: 1">From: </label> <input id="from" type="text" name="from" placeholder="from" value="<?= from ?>" style="flex: 3"> </div> <div style="display: inline-flex; justify-content: flex-end"> <label for="to" style="flex: 1">To: </label> <input id="to" type="text" name="to" placeholder="to" style="flex: 3"> </div> <button id="replace" onclick="handleClick()">Replace</button> <p id="error" style="color: red"></p> </div> </body> </html>
解説
下準備
onOpenでスプレッドシートファイルを開いたときに実行する関数を指定できます。ここではスプレドシートのUIのAPIを呼び出して、メニューの追加と、メニュー項目と選択時の実行関数の登録を行なっています。
メニュー項目が選択された際に実行される関数は、選択中のセルの背景色取得、テンプレートへの値挿入、モーダルの表示などです。対応するコードを以下に記載します；
まず現在選択中のセル(光っているセル)の背景色を取得するためにgetActiveCellを使ってセルを取得します。そのあとにgetBackgroudで#?????? 形式のカラーコードを取得し格納します。
var background = SpreadsheetApp.getActiveSheet().getActiveCell().getBackground();
次に、作成済みのhtmlテンプレートを読み込み、プレースホルダーfromに対し背景色を割り当てます。プレースホルダーの指定は、
<input id="from" type="text" name="from" placeholder="from" value="<?= from ?>" style="flex: 3">
のようにvalue=<?= form ?>となっていて、この仕組みを使って置換前の背景色のデフォルト値、として現在選択中のセルの背景色を割り当てます。参考
var template = HtmlService .createTemplateFromFile('index.html'); template.from = background;
最後にモーダルの表示処理を行います。
var html = template .evaluate() .setWidth(300) .setHeight(150) .setSandboxMode(HtmlService.SandboxMode.IFRAME); SpreadsheetApp.getUi().showModalDialog(html, 'Replace background color');
evaluateメソッドを呼び出すことで、テンプレートからhtmlオブジェクトを生成します、モーダルとして表示するためにサイズを設定し、実行方式をiframeに指定しました。実行方法に関しては他に選択肢があるようで、調べきれていないです。なので特に深い意味はありません、チュートリアルに沿って、iframeを指定しただけです。
モーダル
モーダルの記載方法に関しては1ページのhtmlを書いて行く感じです。heightとwidthが決まっている以外はwebと同じ感覚だと言えます。ただし、少し違うのは、スクリプトエディタで定義した関数を呼び出す方法があるということです。方法としては提供されているAPIを使用します；
function handleClick() { document.getElementById('replace').setAttribute("disabled","disabled"); var form = document.getElementById('form'); var from = form.querySelector('input[name="from"]').value; var to = form.querySelector('input[name="to"]').value; if (!from || !to) return showError('Input #from and #to'); google.script.run .withSuccessHandler(closeModal) .withFailureHandler(showError) .replaceBackgroundColor(from, to); }
handleChlick関数はユーザーが置換実行する際に押下するボタンに紐づいている関数です。概要としては、表示されたモーダルに対象の背景色、変換後の背景色を入力し、「置換」を押下したら、シート内の全てのセルに対して、背景色の置換を行うためのものです。処理の流れとしては二回連続のクリック禁止、入力した値の取得、軽いバリデーション、google.script.runAPIを使用した背景色置換スクリプトの実行です。
詳細が必要なのはgoogle.script.runかと思います。このAPIはgoogle.script.run.hogehoge()と記載することで、スクリプトエディタで定義したhogehoge関数を呼び出すことができます。またここでは、実行の成功時(withSuccessHandler)、失敗時(withFailureHandler)にコールバックを指定しています。コールバック関数は index.htmlで定義したものが指定可能です。
以下はモーダルに値を入力して実行した際に呼び出されるスクリプトです。
function replaceBackgroundColor(from, to) { var sheet = SpreadsheetApp.getActiveSheet(); var data = sheet.getDataRange(); var backgrounds = data.getBackgrounds(); var replacedCount = 0; for(var row = 0; row < backgrounds.length; row++) { for(var col = 0; col < backgrounds[row].length; col++) { if (backgrounds[row][col] != from) continue; sheet.getRange(row + 1, col + 1).setBackground(to); replacedCount++; } } ui = SpreadsheetApp.getUi(); ui.alert('Successfully replaced!', replacedCount + ' cell\'s background color replaced to ' + to + ' from ' + from, ui.ButtonSet.OK); }
特に目新しいことはないかと思います。alertはgetUiでUIから使用可能です。
感想
まず不安に思ったこととしては、「それ標準機能であるよ」と言われることです。その辺どうなのでしょうか、、、たまにコード書きたい欲がまさってしまい、ちゃんとした検索を行わないという傾向があります。
すごく感心したのが、google app scriptのモーダルでhtmlを表示できること、テンプレートを使用できることです。テンプレートを使用できるということは、webアプリケーションに近い何かを作れることだと思います。例えばSQLドライバーを作ってみたりすると楽しいかもです、スプレッドシートをデータベース、シートをテーブルと見立てて、とか思ったりします。
javascriptでユーザーインプットを受け取る方法は、alertとかpromptとか柔軟性がなくて困ることがありました。webだと画面側をリッチにすればいいのですが、spreadsheetだとそうもいかないので、それを解消するためにガイドが役に立つのだと思います。
