Google スプレッドシートの内容を自動取得するホームページを作成する

概要

身内で行ったゲームイベントの運営（システム担当）として、Google スプレッドシートで管理している情報を自動で取得し、整えて表示してくれるホームページを作成しました。
似たような仕掛けを作りたい人に向け、どんな構成で何をしたかについて備忘を残します。

ホームページの概要

以下のようなイベントに使うホームページを作成しました。

• 参加者全員VS運営という構図で、提示されたお題を順に倒していくクエスト形式
• お題のリザルトの提出があると、運営が判定の後、即時に次のお題を提示する

このお題を提示する箇所のつくりについて解説します。
リザルトの提出については、参加者が全員加入しているDiscordで行いましたので今回のスコープ外です。

要件

• デザインや演出を凝りたいので、ホームページが使いたい
• 速やかに次のお題を出してあげたいので、出先でもスマホから簡単にホームページを更新したい
• 身内の小さなイベントなので、無料で済ませたい

双方を満たす着地点を調べていたところ、スプレッドシートとWIXを連携させたという以下のブログにたどり着きました。

上記を参考に（ほぼそのまま採用し）、無料で使えるサービスを組み合わせ、ページ上の一部コンテンツを外部参照させることで、ページを開いた時点の最新の進捗情報が反映されるようにしました。

図1：実際に使用した画面

システム構成

以下の様に、Google スプレッドシートとWIXをGoogle Apps Script(GAS)製のREST APIで繋げた構成となります。どれも（若干の制約はあれど）無料で使用可能なものです。

図2：システム構成図

システムの処理フロー

1. 参加者がWIXで作成したページを開く
2. HTML埋め込みコードにて実行されるスクリプトから、APIリクエストをGASへ送信
3. GASはAPIリクエスト内のパラメーターをもとに、対応するシートの中身を取得
4. GASはシートの中身をJSON形式に組み直し、スクリプトへ返却
5. スクリプトは、受け取ったJSONをもとに表を組み表示

運営の処理フロー

1. Discordに提出のあったリザルトを確認する
2. 該当のお題の「達成者」欄に記名する
3. それだけ！！！

処理のポイント

スプレッドシートの数式作り込み

出先でも簡単に更新できるよう、運営が行う操作は最小限に留めるよう数式を作り込みしました。
運営がお題をコピペ・入力する手間を省き、ヒューマンエラーの防止にも役立ちました。

シート内容取得処理の単純化

シート内容取得APIの動作としては、シートの中身をテーブル形式でそのまま返却するだけにしました。
GAS上で絞り込みや表示の変換なんかもできるとは思うのですが、あえてそこは関数に任せGASとしては単純移送だけにしました。
こうすることで、スプシの数式を変更するだけで仕様変更に対応可能なほか、想定外の挙動があった際も、最悪の場合スプシに直接書き込めば更新可能になる、というメリットがあります。

実際、ハンデ付きお題を追加するとなった際にAPIには変更を加えず済んだので助かりました。
また、全お題達成時の表示まで考慮しておらず表示が崩れたケースもあり、急ぎ手打ちで文言を置き換えることもできました。
この設計で狙った通りの対応ができたかなと思っています。

PCとスマホ双方での表示最適化

データを表示させる箇所は、知識もなかったので単に表形式で表示させるだけにしました。
その際、横長の表はスマホでは見づらい！と感じたので、PCとスマホで表の形式を変更しました。

以下のサイトを参考に、縦積み形式の表にすることでスマホ画面でもコンテンツを見やすくするのは意識しました。

WIXはPC、スマホそれぞれで画面レイアウトを操作できるので、PC用ページにはPC用のHTML埋め込みコードを、スマホ用ページにはスマホ用ページ用のHTML埋め込みコードをセットしました。

ただ難点として、PC、スマホそれぞれの画面で埋め込みコードを作ったので、最初作るときは（ほぼコピペでは済んだものの）それなりに手間がかかりました。

デメリット

• ホームページのレスポンスがちょっと遅かった（5秒位かかる）

ホームページの全体レイアウトはWIXのGUIでの設定で行ったため、分割して表示したい「挑戦中お題」、「達成済お題」、「進捗率」でそれぞれAPIコールを行う必要があり、1回ページを開くたびに3回のAPIコールがなされます。
それ故、GASとWIXのどちらがボトルネックなのかはわかりませんが、サクッと表示されるほどではなかったです。
まあ、身内イベントですしこれくらいはお目溢しいただきたいです……。

この構成ではできないこと

• 個人毎の進捗管理
• リザルトをホームページ経由で提出

今回は参加者VS運営というイベントの性質上、全体として1つの進捗管理で十分だったので、上記構成で十分でした。

個人毎進捗管理をするのであれば、まずページのつくりとして動的ページにしなければならないほか、リザルトや個人毎の進捗の受け皿となるデータベースを作らなければいけないので、若干ゴージャスな構成になると予想されます。ホームページにこだわらないほうが結果的にシンプルになったりするかもしれません。
ただ、世の中にはそのようなサービスも多々あると思うので、（若干コストはかかるかもしれませんが）手間なく運営はできると思います。

ソースコード

ほぼそのまま使えるソースコードをつけておきます。
ちなみに、chatGPTに98%書かせています。

シート取得API

シート取得API.gs

function doGet(e) {
  /* 
  引数1つ param
    param = 0のとき　挑戦中お題
    param = 1のとき　達成済みお題
    param = 2のとき　進捗
    param それ以外のとき　パラメータエラー

  実行結果イメージ
    [
      {"No.":"1", "お題":"お題1", "曲":"曲名1", "譜面":"DSP", "レベル":"13", "難易度":"★"},
      {"No.":"2", "お題":"お題2", "曲":"曲名2", "譜面":"ESP", "レベル":"14", "難易度":"★★"},
      {"No.":"3", "お題":"お題3", "曲":"曲名3", "譜面":"CSP", "レベル":"15", "難易度":"★★★"}
    ]
   */ 

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  try {
    // パラメータが存在するか確認
    if (!e.parameter.param) {
      throw new Error("Missing parameter: 'param'");
    }

    //パラメータにより、指定するシートを選択する
    const param = e.parameter.param; // URLのパラメータ "param" を取得

    if (param === "0") {
      var sheet = ss.getSheetByName('挑戦中お題');
    } else if (param === "1") {
      var sheet = ss.getSheetByName('達成済みお題');
    } else if (param === "2")  {
      var sheet = ss.getSheetByName('達成率');
    } else{
      throw new Error(`Invalid parameter value: '${param}'`);
    }
    var data = getFromSheet(sheet);

    //値が格納されていることをログで確認
    Logger.log(data);
  
    //outputとして、JSON形式で返す
    var output = ContentService.createTextOutput();
    output.setMimeType(ContentService.MimeType.JSON);
    output.setContent(JSON.stringify(data, null, '\t'));

    return output;

  } catch (error) {
    // エラーレスポンス
    const errorResponse = {
      status: "error",
      message: error.message
    };
    return ContentService.createTextOutput(JSON.stringify(errorResponse))
                         .setMimeType(ContentService.MimeType.JSON);
  }

}


function getFromSheet(sheet){
  //JSON用のkey
  var keys = [];

  //データ格納配列
  var data = [];

  //行（横軸）と列（縦軸）の最大数を取得
  var maxRow = sheet.getLastRow();
  var maxColumn = sheet.getLastColumn();

  //1行目のkeyの名前取得 keyの行を変更したい場合はxと引数を変更
  //JSON用のラベルは1行目で指定しているため【getRange】の第1引数は【1】
  for (var x = 1; x <= maxColumn; x++) {
    keys.push(sheet.getRange(1, x).getValue());
  }

  //データの取得
  //実際のデータが2行目からなので【y = 2】から開始
  for (var y = 2; y <= maxRow; y++) {
    // var json_shop_id = {};
    var json = {};
    
    for (var x = 1; x <= maxColumn; x++) {
      json[keys[x-1]] = sheet.getRange(y, x).getValue();
    }
    //データ格納
    data.push(json);
    
  }
  return data;
}

埋め込みコード（PC）

埋め込みコード（PC）.html

<div id="htmlActiveMissionStream"></div>

    <style>
        table {
            background-color: #F3A68A;
            border-collapse: collapse;
            width: 100%;
        }
        th, td {
            border: 1px solid #ddd;
            padding: 8px;
        }
        th {
            background-color: #E16344;
            text-align: left;
        }
    </style>

<table id="data-table">
    <thead>
        <tr id="table-header"></tr>
    </thead>
    <tbody id="table-body"></tbody>
</table>

<script>

  const apiUrl = 'APIのURI';
  //0:挑戦中お題、1:達成済みお題、2:進捗状況
  const param = 0;

    async function fetchData() {
        try {
            const response = await fetch(`${apiUrl}?param=${param}`);
            if (!response.ok) {
                throw new Error(`HTTP error! status: ${response.status}`);
            }
            const data = await response.json();
          
            populateTable(data);
          
        } catch (error) {
            console.error('Error fetching data:', error);
        }
    }

      function populateTable(data) {
        const tableHeader = document.getElementById('table-header');
        const tableBody = document.getElementById('table-body');

        // Clear existing table content
        tableHeader.innerHTML = '';
        tableBody.innerHTML = '';

        if (Array.isArray(data) && data.length > 0) {
            // Create table headers
            const headers = Object.keys(data[0]);
            headers.forEach(header => {
                const th = document.createElement('th');
                th.textContent = header;
                tableHeader.appendChild(th);
            });

            // Populate table rows
            data.forEach(row => {
                const tr = document.createElement('tr');
                headers.forEach(header => {
                    const td = document.createElement('td');
                    td.textContent = row[header] || '';
                    tr.appendChild(td);
                });
                tableBody.appendChild(tr);
            });
        } else {
            const tr = document.createElement('tr');
            const td = document.createElement('td');
            td.textContent = 'No data available';
            td.colSpan = 1;
            tr.appendChild(td);
            tableBody.appendChild(tr);
        }
    }
  
  
  // サイトが読み込まれた時点で一度実行

  fetchData();

</script>

埋め込みコード（スマホ）

埋め込みコード（スマホ）.html

<div id="mobileHtmlActiveMissionStream"></div>
     <style>
        body {
            font-family: Arial, sans-serif;
            margin: 20px;
        }
        .record {
            border: 1px solid #ddd;
            border-radius: 5px;
            padding: 10px;
            margin-bottom: 10px;
            background-color: #F3A68A;
        }
        .record div {
            display: flex;
            justify-content: space-between;
            padding: 5px 0;
            border-bottom: 1px solid #eee;
        }
        .record div:last-child {
            border-bottom: none;
        }
        .key {
            font-weight: bold;
        }
    </style>

<script>
  const apiUrl = 'APIのURI';
  //0:挑戦中お題、1:達成済みお題、2:進捗状況
  const param = 0;
  
    async function fetchData() {
        try {
            const response = await fetch(`${apiUrl}?param=${param}`);
            if (!response.ok) {
                throw new Error(`HTTP error! status: ${response.status}`);
            }
            const data = await response.json();
          
            populateTable(data);
          
        } catch (error) {
            console.error('Error fetching data:', error);
        }
    }

      function populateTable(data) {
        const container = document.getElementById('mobileHtmlActiveMissionStream');

        // Clear existing content
        container.innerHTML = '';

        if (Array.isArray(data) && data.length > 0) {
            data.forEach(record => {
                const recordDiv = document.createElement('div');
                recordDiv.className = 'record';

                Object.keys(record).forEach(key => {
                    const rowDiv = document.createElement('div');

                    const keyDiv = document.createElement('div');
                    keyDiv.className = 'key';
                    keyDiv.textContent = key;

                    const valueDiv = document.createElement('div');
                    valueDiv.className = 'value';
                    valueDiv.textContent = record[key];

                    rowDiv.appendChild(keyDiv);
                    rowDiv.appendChild(valueDiv);
                    recordDiv.appendChild(rowDiv);
                });

                container.appendChild(recordDiv);
            });
        } else {
            const noDataDiv = document.createElement('div');
            noDataDiv.textContent = 'No data available';
            container.appendChild(noDataDiv);
        }
    }
  
  
  // サイトが読み込まれた時点で一度実行

  fetchData();

</script>

感想

• 無料でもここまでできるんだとびっくり（特にGASが優秀すぎて……今ではこれを使っていろいろしているのでそれについてもどこかで解説したい）
• 簡単に操作できるよう工夫したのは運営としてかなり負荷軽減になって非常に嬉しい
• ロジックは単純な方が後々楽だと改めて痛感

というわけで、皆さんも気軽にイベントを企画してみましょう！！！