GASテスト

Google Apps Script を利用して、スプレッドシートから情報を取得してWEBページに表示させる。

html

<div id="output"><div id="loading"><div class="txt">loading...</div></div></div>
<div><script src="/ss2p.js"></script></div>

js

let style = `<style>
    body {
        background: lightblue;
    }
</style>`;

document.querySelector(`head`).insertAdjacentHTML('beforeend', style);

let callback = function(json) {
    let html = '';
    html += '<dl>';
    for(let i in json.sheet1){
        console.log(json.sheet1[i]);
        html += '<dt>' + json.sheet1[i]['title'] + '</dt>';
        html += '<dd>' + json.sheet1[i]['content'] + '</dd>';
        html += '<dd><time>' + json.sheet1[i]['date'] + '</time></dd>';
    }
    html += '</dl>';
    $('#output').html(html);
}
const extJs = document.createElement('script');
extJs.src ='https://script.google.com/macros/s/GASWEBアプリ/exec';
document.body.appendChild(extJs);

GAS

function getData(id, sheetName) {
    let sheet = SpreadsheetApp.openById(id).getSheetByName(sheetName);
    let rows = sheet.getDataRange().getValues();
//console.log(rows);
    let keys = rows.splice(0, 1)[0];
console.log(keys);
    return rows.map(function(row) {
      let obj = {}
      row.map(function(item, index) {
        obj[keys[index]] = item;
      });
      return obj;
    });
  }
    
  function doGet(){
    let sheetId = 'スプレッドシートのID';
    let resluts = {
      sheet1:getData(sheetId, 'post'),
      sheet2:getData(sheetId, 'category'),
      sheet3:getData(sheetId, 'tag')
    }
    /*json
    return ContentService.createTextOutput(JSON.stringify(resluts, null, 2))
    .setMimeType(ContentService.MimeType.JSON);
    */
    /*jsonp*/
    let jsonp = "callback(" + JSON.stringify(resluts) + ")";
  console.log(jsonp);
    return ContentService.createTextOutput(jsonp)
    .setMimeType(ContentService.MimeType.JAVASCRIPT);
}

TEST

Loading…