GASでWebアプリを作成する際にスプレッドシートから値を読込んで表にしたい場合があると思います。
ここでは、既存のスプレッドシートを読み込んで、出力する方法を紹介しています。
この記事の対象
スプレッドシートの用意
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列目だけを範囲指定しています。
結果
上記の結果は、範囲を1行目、1列目だけを出力したものになります。