指定したシートのデータをHTMLで表現したくて書いたコードです
設定する変数
var ssUrl = "SPREADSHEET URL";
コード.gs
function doGet(e) {
return HtmlService.createHtmlOutputFromFile("index");
}
function returnTargetSheet() {
var ssUrl = "https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit#gid=0";
var ss = SpreadsheetApp.openByUrl(ssUrl);
var sheetId = ssUrl.split("gid=")[1];
var sheets = ss.getSheets();
for (var i = 0; i < sheets.length; i++) {
if (sheets[i].getSheetId() == sheetId) {
break;
}
}
var sheet = ss.getSheets()[i];
return sheet;
}
function returnDataFromSheet(){
var sheet = returnTargetSheet();
var dataRangeValues = returnValues(sheet);
var valuesObj = returnObj(dataRangeValues);
var json = JSON.stringify(valuesObj);
Logger.log(json);
return json;
}
function returnValues(sheet) {
var lastRow = sheet.getLastRow();
var lastCol = sheet.getLastColumn();
var range = sheet.getRange(1, 1, lastRow, lastCol)
var values = range.getDisplayValues();
return values;
}
function returnObj(dataRangeValues) {
var obj = {};
for(var i = 0; i < dataRangeValues.length; i++) {
var rowValues = dataRangeValues[i];
var row = i + 1;
obj[row] = rowValues
}
return obj;
}
|
index.html
<!DOCTYPE html>
<html>
<head>
<style>
table {
border-collapse: collapse;
}
.cell {
font-size: 12px;
white-space: nowrap;
padding: 5px;
border: solid 1px lightgray;
}
</style>
</head>
<body>
<div id="main_div"></div>
<table id="main_table">
<tbody id="main_tbody"></tbody>
</table>
<script>
var tbody = elem("main_tbody");
getDataFromSheet();
function getDataFromSheet() {
google.script.run
.withFailureHandler(onFailure)
.withSuccessHandler(gotDataFromSheet)
.returnDataFromSheet();
}
/************************************
gotDataFromSheet
************************************/
function gotDataFromSheet(dataRangeValuesJson) {
var obj = JSON.parse(dataRangeValuesJson);
createTableCells(obj);
}
/************************************
createTableCells
************************************/
function createTableCells(obj) {
createTableHeader(obj);
createTableDatas(obj);
}
/************************************
createTableHeader
************************************/
function createTableHeader(obj) {
var tr = document.createElement("tr");
var captions = captions = obj["1"];
for(var i = 0; i < captions.length; i++) {
var cellValue = captions[i];
var th = document.createElement("th");
th.innerHTML = cellValue.replace("\n", "<br>");
th.setAttribute("class", "cell");
tr.appendChild(th);
}
tbody.appendChild(tr);
}
/************************************
createTableDatas
************************************/
function createTableDatas(obj) {
var rows = Object.keys(obj);
for(var i = 1; i < rows.length; i++) {
var tr = document.createElement("tr");
var tableRow = rows[i];
var rowValues = obj[tableRow];
createTableData(tr, rowValues);
tbody.appendChild(tr);
}
}
/************************************
createTableData
************************************/
function createTableData(tr, rowValues) {
for(var i = 0; i < rowValues.length; i++) {
var cellValue = rowValues[i];
var td = document.createElement("td");
td.innerHTML = cellValue.replace("\n", "<br>");
td.setAttribute("class", "cell");
tr.appendChild(td);
}
}
/************************************
onFailure(e)
************************************/
function onFailure(e) {
alert([e.message, e.stack]);
}
/************************************
elem(id)
************************************/
function elem(id) {
return document.getElementById(id);
}
</script>
</body>
</html>
|
