【GAS】Webページにスプレッドシートの値を埋め込む方法

GASでWebアプリを作成する際にスプレッドシートから値を読込んで表にしたい場合があると思います。

ここでは、既存のスプレッドシートを読み込んで、出力する方法を紹介しています。

この記事の対象

GASでスプレッドシートを出力する方法を知りたい方
スプレッドシートの任意の行列だけを埋め込みたい方
スプレッドシートの全ての値を取得して埋め込みたい方

INDEX

スプレッドシートの用意
全行列の取得
HTMLの作成
1. 結果
任意の範囲だけ出力したい場合
1. 結果

スプレッドシートの用意

GASで作成したWebアプリで出力したいスプレッドシートを用意します。ここでは、2行3列の内容となります。

また、IDを控えておきます。IDが何の事か分からない方は以下をご参照下さい。

>>>Webスクレイピングしてスプレッドシートに出力する方法

全行列の取得

ここでは「コード.gs」にスプレッドシートの全データを読み込むコードを記述します。

var spreadsheetId = '**********';
function GetSpreadsheet(){
  var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName('シート1');

  var last_col = sheet.getLastColumn();  
  var last_row = sheet.getLastRow();     
  
  return sheet.getRange(1, 1, last_row, last_col).getValues();
}

以下は解説になります。

var Id = '**********';
function GetSpreadsheet(){
  var sheet = SpreadsheetApp.openById(Id).getSheetByName('シート1');

はじめに「’**********’」にはスプレッドシートのIDを指定します。次にGetSpreadsheet関数内でシートを取得します。ここでは、Idで取得したスプレッドシートの「シート1」を取得しています。

  var last_col = sheet.getLastColumn();  
  var last_row = sheet.getLastRow();

「getLastColumn」で最終列、「getLastRow」で最終行をそれぞれ取得しています。

  return sheet.getRange(1, 1, last_row, last_col).getValues();

返り値にgetRangeで1行目、1列目から最終行、最終行までのそれぞれの値を取得して返しています。

HTMLの作成

ここでは、「TestPage.html」に表を作成するプログラムを記述します。

<html>
 <script>
  function loadsheet(){
    google.script.run.withSuccessHandler(outSpreadsheet).GetSpreadsheet();
  }
  window.addEventListener('load', loadsheet);

  function outSpreadsheet(spreadsheet_data){
    var htmlTable = '<table id="tableData" border=1>';
    for(var row = 0; row < spreadsheet_data.length; row++){
      htmlTable += '<tr>';
      for(var col = 0; col < spreadsheet_data[row].length; col++){
        htmlTable += '<td>' + spreadsheet_data[row][col] + '</td>';
      }
      htmlTable += '</tr>';
    }
    htmlTable += '</table>';
    document.getElementById("inserthyo").innerHTML = htmlTable;
  }
</script>

<head>
  <title>Test</title>
  <base target="_top">
  <?!= HtmlService.createHtmlOutputFromFile('style').getContent(); ?>
</head>
<body>
  <h1>スプレッドシート読込み</h1>
  <?!= HtmlService.createHtmlOutputFromFile("jsstyle").getContent(); ?>
  <div id='inserthyo'>
    </div>
</body>
</html>

上記では「.html」に読み込んだスプレッドシートを出力しています。

以下は「script」内の解説になります。

  function loadsheet(){
    google.script.run.withSuccessHandler(dispSpreadsheet).GetSpreadsheet();
  }
  window.addEventListener('load', loadsheet);

loadsheet関数をページのロード時に実行しています。「withSuccessHandler()」はサーバー側の関数が正常に返された場合に実行される関数になります。サーバー側の関数である「GetSpreadsheet」関数を呼び出して、その結果を引数として outSpreadsheet 関数に渡しています。

次に「window.addEventListener()」が実行され、ページがロードされた際に引数のloadsheet関数が実行されるようにしています。

  function outSpreadsheet(spreadsheet_data){
    var htmlTable = '<table id="tableData" border=1>';
    for(var row = 0; row < spreadsheet_data.length; row++){
      htmlTable += '<tr>';
      for(var col = 0; col < spreadsheet_data[row].length; col++){
        htmlTable += '<td>' + spreadsheet_data[row][col] + '</td>';
      }
      htmlTable += '</tr>';
    }
    htmlTable += '</table>';
    document.getElementById("inserthyo").innerHTML = htmlTable;
  }

「outSpreadsheet」関数はサーバーサイドから受け取ったスプレッドシートのデータをHTMLテーブルに変換し特定の要素に挿入しています。

「htmlTable」変数にテーブルの開始タグを代入しています。下のfor構文で行ごとに「tr」タグを、列ごとに「td」タグを使用してデータをテーブルセルに変換して「htmlTable」に追加しています。

document.getElementById("inserthyo").innerHTML = htmlTable;

最後に上記のinnerHTMLで指定した要素(ここではinserthyo)に生成したテーブルを挿入しています。

結果

任意の範囲だけ出力したい場合

function GetSpreadsheet(){
  var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName('シート1');
  
  return sheet.getRange(1, 1, 1, 1).getValues();
}

「コード.gs」のgetRange関数の引数を固定値にするだけで任意の範囲にすることが可能です。上記の例だと1行目、1列目だけを範囲指定しています。