概要

今回は、Google Apps Script を使って、Googleスプレッドシート上のデータをリアルタイムで処理し、特定のURLで表示する方法を紹介します。さらに、データの更新に伴う日時の表示や、複数のシートに対する処理を行い、それぞれのシートに対して異なるURLを発行する方法もカバーします。

このスクリプトを利用することで、OBSなどでリアルタイムにスプレッドシートのデータを表示できるようになります。

目的

複数のスプレッドシートからデータを取得し、リアルタイムでブラウザに表示する
スプレッドシートのデータ更新時に、更新日時を自動で挿入する
現在のスコアやハイスコア、それに対応する時刻を表示
それぞれのシートに対するURLを発行し、異なるシートのデータを個別に表示

前提

基本的なGoogle Apps Scriptの知識
Googleスプレッドシートを利用していること

スプレッドシートのセットアップ

今回の例では、4つのシート（'area', 'hoko', 'asari', 'yagura'）を用意し、それぞれのシートのA列にスコアを入力し、B列にそのスコアの入力日時を表示します。

シート構成の例:

※A列とB列のみ使います。C列以降はメモ。

Google Apps Scriptの作成

スクリプトエディタを開く
Googleスプレッドシートの「拡張機能」→「Apps Script」を選択し、スクリプトエディタを開きます。

2. コードを入力する次に、以下のコードを入力します。このスクリプトは、各シートのスコアとその差分、ハイスコア、そしてそれらの入力時刻を表示するための機能を実装しています。

Google App Script

// 4つのシートにおける
// 1. 現在のスコア(直前のスコアの差分)　を表示する処理
// 2. ハイスコアを表示する処理
// 3. 現在のスコアの記録日時を表示する処理
// 4. ハイスコアの記録日時を表示する処理
// を作成

// 使い方は
// 1. 引数に パラメータ ?sheet=area のようにシート名のみをつけることで現在のスコアを表示
// 2. 引数に パラメータ ?action=highest&sheet=area のようにaction=highestおよびシート名をつけることでハイスコアを表示
// 3. 引数に パラメータ ?action=currentTime&sheet=area のようにaction=action=currentTimeおよびシート名をつけることで現在スコアの記録日時を表示
// 4. 引数に パラメータ ?action=highestTime&sheet=area のようにaction=highestTimeおよびシート名をつけることでハイスコアの記録日時を表示

// URL: https://script.google.com/macros/s/XXX/exec

function doGet(e) {
  var action = e.parameter.action || 'sheet';  // URLパラメータからアクションを取得（デフォルトは'sheet'）

  if (action === 'highest') {
    return showHighestValueForSheet(e);
  } else if (action === 'currentTime') {
    return showCurrentTimeForSheet(e);
  } else if (action === 'highestTime') {
    return showHighestTimeForSheet(e);
  } else {
    return showSheetData(e);
  }
}

function formatDateWithoutTimezone(date) {
  // 年、月、日を取得して希望のフォーマットに変換
  var year = date.getFullYear();
  var month = date.getMonth() + 1;  // 月は0から始まるため、+1します
  var day = date.getDate();
  return year + '/' + month + '/' + day;
}

function showSheetData(e) {
  var sheetName = e.parameter.sheet || 'area'; // URLパラメータからシート名を取得、デフォルトは'area'
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  
  if (!sheet) {
    return HtmlService.createHtmlOutput('<html><body>Invalid sheet name</body></html>');
  }

  var lastRow = sheet.getLastRow();
  
  // ヘッダー行(1行目)をスキップし、2行目以降が対象
  if (lastRow > 1) { 
    var currentValue = sheet.getRange(lastRow, 1).getValue();
    var previousValue = sheet.getRange(lastRow - 1, 1).getValue();
    var difference = currentValue - previousValue;

    var arrow = difference > 0 ? "+" : (difference < 0 ? "-" : "");
    var output = currentValue + "(" + arrow + Math.abs(difference) + ")";

    var htmlOutput = HtmlService.createHtmlOutput('<html><head>' +
      '<style>@import url("https://fonts.googleapis.com/css2?family=Luckiest+Guy&display=swap"); ' +
      'body { font-family: "Luckiest Guy", cursive; font-weight: bold; color: white; ' +
      'font-size: 128px; letter-spacing: 5px; text-shadow: 2px 2px 4px rgba(0, 0, 0, 0.8); }' +
      '</style>' +
      '</head><body>' +
      '<div>' + output + '</div>' +
      '</body></html>');

    return htmlOutput;
  } else {
    return HtmlService.createHtmlOutput('<html><body>No Data</body></html>');
  }
}

// 他の関数も同様に、スタイルを適用した形に変更します

function showHighestValueForSheet(e) {
  var sheetName = e.parameter.sheet || 'area';  // URLパラメータからシート名を取得、デフォルトは'area'
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

  if (!sheet) {
    return HtmlService.createHtmlOutput('<html><body>Invalid sheet name</body></html>');
  }

  var values = sheet.getRange('A2:A' + sheet.getLastRow()).getValues();  // ヘッダー行を除く
  var max = Math.max(...values.flat().filter(Number.isFinite));  // 数値フィルター後の最大値

  var output = '<html><head>' +
    '<style>@import url("https://fonts.googleapis.com/css2?family=Luckiest+Guy&display=swap"); ' +
    'body { font-family: "Luckiest Guy", cursive; font-weight: bold; color: white; ' +
    'font-size: 128px; letter-spacing: 5px; text-shadow: 2px 2px 4px rgba(0, 0, 0, 0.8); }' +
    '</style>' +
    '</head><body>' +
    '<div>' + max + '</div>' +
    '</body></html>';
  
  return HtmlService.createHtmlOutput(output);
}

function showCurrentTimeForSheet(e) {
  var sheetName = e.parameter.sheet || 'area';  // URLパラメータからシート名を取得
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

  if (!sheet) {
    return HtmlService.createHtmlOutput('<html><body>Invalid sheet name</body></html>');
  }

  var lastRow = sheet.getLastRow();
  if (lastRow > 1) {
    var currentTime = sheet.getRange(lastRow, 2).getValue();  // B列の時刻取得
    var formattedTime = formatDateWithoutTimezone(currentTime);  // 日付形式にフォーマット

    var output = '<html><head>' +
      '<style>@import url("https://fonts.googleapis.com/css2?family=Luckiest+Guy&display=swap"); ' +
      'body { font-family: "Luckiest Guy", cursive; font-weight: bold; color: white; ' +
      'font-size: 32px; letter-spacing: 5px; text-shadow: 2px 2px 4px rgba(0, 0, 0, 0.8); }' +
      '</style>' +
      '</head><body>' +
      '<div>' + formattedTime + '</div>' +
      '</body></html>';
    
    return HtmlService.createHtmlOutput(output);
  } else {
    return HtmlService.createHtmlOutput('<html><body>No Data</body></html>');
  }
}

function showHighestTimeForSheet(e) {
  var sheetName = e.parameter.sheet || 'area';  // URLパラメータからシート名を取得
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

  if (!sheet) {
    return HtmlService.createHtmlOutput('<html><body>Invalid sheet name</body></html>');
  }

  var values = sheet.getRange('A2:A' + sheet.getLastRow()).getValues();  // ヘッダー行を除く
  var maxIndex = values.flat().indexOf(Math.max(...values.flat().filter(Number.isFinite))) + 2;  // 最大値のインデックス取得
  var highestTime = sheet.getRange(maxIndex, 2).getValue();  // B列の時刻取得
  var formattedTime = formatDateWithoutTimezone(highestTime);  // 日付形式にフォーマット

  var output = '<html><head>' +
    '<style>@import url("https://fonts.googleapis.com/css2?family=Luckiest+Guy&display=swap"); ' +
    'body { font-family: "Luckiest Guy", cursive; font-weight: bold; color: white; ' +
    'font-size: 32px; letter-spacing: 5px; text-shadow: 2px 2px 4px rgba(0, 0, 0, 0.8); }' +
    '</style>' +
    '</head><body>' +
    '<div>' + formattedTime + '</div>' +
    '</body></html>';
  
  return HtmlService.createHtmlOutput(output);
}

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  
  // 1行目はスキップし、2行目以降が対象
  if (range.getRow() > 1 && range.getColumn() == 1) {  // A列が編集されたかどうかを確認
    var row = range.getRow();
    var timestamp = new Date();
    
    sheet.getRange(row, 2).setValue(timestamp);  // B列に日時を設定
  }
}

現在のスコアとハイスコアの時刻表示

上記のスクリプトでは、現在のスコアとハイスコアに対応する時刻を表示する機能も追加されています。それぞれの時刻を確認したい場合には、URLパラメータで指定することで、簡単に確認することができます。

例えば、?action=currentTime&sheet=area のようにURLにパラメータを追加することで、指定したシートの現在のスコアの時刻を表示することが可能です。

以下、それぞれの関数の説明と使い方を紹介します。

現在のスコアの時刻を表示する関数

showCurrentTimeForSheet(e) 関数は、スプレッドシートのA列の最後の行に入力されているスコアに対応する時刻（B列の値）を表示します。

使用例:
- https://script.google.com/macros/s/XXX/exec?action=currentTime&sheet=area
- このURLで area シートの現在のスコアの入力時刻を取得できます

ハイスコアの時刻を表示する関数

showHighestTimeForSheet(e) 関数は、スプレッドシートのA列のハイスコアに対応する時刻（B列の値）を表示します。

使用例:
- https://script.google.com/macros/s/XXX/exec?action=highestTime&sheet=area
- このURLで area シートのハイスコアの入力時刻を取得できます

デプロイ方法

作成したスクリプトをWebアプリケーションとして公開するためには、デプロイする必要があります。以下にその手順を示します。

1. デプロイ:

スクリプトエディタで「デプロイ」→「新しいデプロイ」を選択します
「タイプの選択」から「ウェブアプリ」を選びます
「次のユーザーとして実行」を「自分」に設定し、「アクセスできるユーザー」を「全員（匿名ユーザーを含む）」に設定します
デプロイを実行し、URLが発行されます

2. URLの使用:

発行されたURLの末尾にパラメータを追加して、特定のシートやアクションにアクセスできます
例:
- https://script.google.com/macros/s/XXX/exec?action=sheet&sheet=area
- https://script.google.com/macros/s/XXX/exec?action=highestTime&sheet=hoko

最後に

今回紹介したスクリプトを使えば、Googleスプレッドシート上のデータをリアルタイムでブラウザに表示し、OBSなどの配信ツールで利用することが可能です。また、複数のシートを扱うことで、様々な用途に対応できる柔軟な仕組みを実現しました。

ニーズがあればOBSへの設定方法も作成いたします!

カスタマイズ:

スクリプトはさらにカスタマイズ可能で、デザインやフォントの変更、データの処理方法の変更なども行えます
例えば、スプレッドシートの追加シートに対応したり、さらに複雑な計算処理を追加することも可能です