2020年1月12日日曜日

スプレッドシートからデータを取得してtable要素で表現したい


指定したシートのデータを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>