ま、いろいろとやってこんな感じ。
対象となるSpreadSheetのデータ
| 都道府県別の小学校数の推移 | ||||
|---|---|---|---|---|
| 年 | 北海道 | 青森県 | 岩手県 | 宮城県 |
| 1975年 | 1820 | 545 | 610 | 458 |
| 1976年 | 1898 | 542 | 602 | 456 |
| 1977年 | 1881 | 531 | 598 | 460 |
| 1978年 | 1866 | 531 | 591 | 462 |
| 1979年 | 1854 | 531 | 579 | 468 |
そのSpreadSheetのApps Script
操作トリガーはこのメニューに置く。
更新対象となるGoogle DriveのJSONファイルは、ファイルIDで指定する。ファイルIDってなんや？ってことは「Google Drive上のファイルに直接アクセスする #Slack - Qiita」で分かる。
// 更新対象のGoogle DriveのファイルID const targetFileId = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx" function publishData() { if (!confirm('データを書き出しします。よろしいですか？')){ return ; } //ローディングダイヤログ表示 const update_with_loader_html = "update_with_loader" const ui = SpreadsheetApp.getUi() const loader = HtmlService.createTemplateFromFile(update_with_loader_html).evaluate(); ui.showModalDialog(loader, "ファイルを書き出し処理中です。"); } // ここはポップアップのHTML側から呼ばれる function updateGoogleDriveFile(contents){ const fileID = targetFileId const content = "execRemoteJson("+getData()+");"; const blob = Utilities.newBlob(content, MimeType.PLAIN_TEXT); Drive.Files.update({}, fileID, blob) } //データ取得 function getData() { //データ取得するシート（現在開いているシートを指定） var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('シート1') //行（横軸）と列（縦軸）の最大数を取得 var maxRow = sheet.getLastRow(); var maxColumn = sheet.getLastColumn(); //JSON用のkey var keys = []; //データ格納配列 var data = []; //2行目のkeyの名前取得 //1行目は管理しやすいよう日本語で記述し、 //JSON用のラベルは2行目で指定しているため //【getRange】の第1引数は【2】 for (var x = 1; x <= maxColumn; x++) { keys.push(sheet.getRange(2, x).getValue()); } //データの取得 //実際のデータが3行目からなので【y = 3】から開始 //getRange()を使って、3行目1セル ～ 最終行目の最終セルを範囲指定 var _values = sheet.getRange(3, 1, maxRow, maxColumn).getValues(); for(var i = 0; i < _values.length; i++){ let year = _values[i][0]; if (year) { var json = {}; json.year = year; json.data = {}; for (var k = 1; k < _values[i].length; k++) { json.data[keys[k]] = _values[i][k]; } data.push(json); } } //整形してテキストに return JSON.stringify(data , null, ''); } //スプレッドシート読み込み時に実行して function onOpen() { //メニューバーにJSON出力用メニューを追加 var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); var entries = [{ name : "ホームページ用に書き出し", functionName : "publishData" }]; spreadsheet.addMenu("HP出力", entries); }; const alert = (message) => { const ui = SpreadsheetApp.getUi() ui.alert(message, ui.ButtonSet.OK) } const confirm = (message) => { const ui = SpreadsheetApp.getUi() return ui.alert(message, ui.ButtonSet.YES_NO) === ui.Button.YES }
ポップアップのHTMLも作る
Apps Scriptの「ファイル」から＋マークで作れる。
<!DOCTYPE html> <html> <head> <base target="_top"> <style> .loader_wrapper { max-width: 100%; overflow: hidden; } .loader, .loader:after { border-radius: 50%; width: 10em; height: 10em; } .loader { margin: 60px auto; font-size: 10px; position: relative; text-indent: -9999em; border-top: 1.1em solid rgba(0, 0, 0, 0.2); border-right: 1.1em solid rgba(0, 0, 0, 0.2); border-bottom: 1.1em solid rgba(0, 0, 0, 0.2); border-left: 1.1em solid #000; -webkit-transform: translateZ(0); -ms-transform: translateZ(0); transform: translateZ(0); -webkit-animation: load8 1.1s infinite linear; animation: load8 1.1s infinite linear; } @-webkit-keyframes load8 { 0% { -webkit-transform: rotate(0deg); transform: rotate(0deg); } 100% { -webkit-transform: rotate(360deg); transform: rotate(360deg); } } @keyframes load8 { 0% { -webkit-transform: rotate(0deg); transform: rotate(0deg); } 100% { -webkit-transform: rotate(360deg); transform: rotate(360deg); } } </style> </head> <body> <script> google.script.run.withSuccessHandler(function(data){ google.script.run.withSuccessHandler(function(){ google.script.run.alert("終了しました。") google.script.host.close() }).updateGoogleDriveFile(data) }).getData() </script> <div class="loader_wrapper"> <div class="loader">Loading...</div> </div> 完了までこのままお待ちください。<br> ※データ数によっては1～3分ほどかかる場合ございます。 </body> </html>
google.script.run.withSuccessHandlerを使いまくってCallback地獄。google.script.runはHTML側でしか使えない。コード.gsで使いたかった。
表示する側のHTML
このJSONを何とか取ってこようとしてAjaxとJSONP試したけど普通にCORSでダメだった。そりゃそーですよね。
ってことで、PHP使う。
<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <script src="https://code.jquery.com/jquery-3.7.1.min.js" integrity="sha256-/JqT3SQfawRcv/BIHPThkBvs0OEvtFFmqPF/lYI/Cxo=" crossorigin="anonymous"></script> <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-9ndCyUaIbzAi2FUVXJi0CjmCapSmO7SnpJef0486qhLnuZ2cdeRhO02iuK6FUUVM" crossorigin="anonymous"> <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/js/bootstrap.bundle.min.js" integrity="sha384-geWF76RCwLtnZ8qwWowPQNguL3RmwHVBC9FhGdlKrxdiJJigb/j/68SIy3Te4Bkz" crossorigin="anonymous"></script> <title>Google Apps Script DEMO</title> <script> function execRemoteJson(json){ $(function(){ for (let i=0,j=json.length; i<j; i++){ let option = new Option(json[i].year, json[i].year); $('#y').append(option); } for (let i in json[0].data){ let option = new Option(i, i); $('#p').append(option); } $('#y,#p').on('change', calcNum); }) function calcNum(){ let y =$('#y').val(), p = $('#p').val(); if (y && p) { for (let i=0,j=json.length; i<j; i++){ if (y == json[i].year){ for (let j in json[i].data){ if (j == p){ $('#num').html(json[i].data[j]); } } } } } } } <?php echo file_get_contents("https://drive.google.com/uc?id=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx") ?> </script> <body> <div class="row text-center m-5"> <h2 class="mb-5 text-right">小学校の数</h2> <div class="col-5 text-end"> 年：<select id="y"><option value="">----------</option></select> </div> <div class="col-2 text-center"> 都道府県：<select id="p"><option value="">----------</option></select> </div> <div class="col-1 text-end"> <span id="num">---</span>件 </div> </div> </body> </html>
感想
- JavaScriptのノリでalert()とかconfirm()とか使えるだろとか思ったら全然ダメだった。
- Blobにするのと、ファイルを上書きしていくのが意外とはまる。Apps Scriptの公式ドキュメントが微妙に見づらい。WEB APIとごっちゃになってる？
- SpreadSheetにポップアップのHTML出すのが意外と面倒。HTML側でデータゴニョニョとかなんか見通し悪そうになってしまった。
