スプレッドシートのデータを整形出力するとき、gasで全部書くとサラッとjsのように書けなかったり、単純に長くなったりします。
jsにデータだけ与えてフロントを別に組みたかったりしますが、そんな時にスプレッドシートを使いやすく取り込んでくれるgasです
スプレッドシートをタブ名に表記されたタイプで整形し、Jsonに吐きます
ノーマルなタブ名
テーブルで出力。1行目をカラム名にして2行目以降をレコードとして返します
※A1が空だとスキップ
| id | name | value |
|---|---|---|
| 1 | alfa | 33 |
| 2 | beta | 66 |
出力
{"タブ名":[
{id:1,name:"alfa",value:33},
{id:2,name:"beta",value:66}
]}
タブ表記「SheetName:Sheet」
A1シート形式で出力
| 1 | alfa | 33 |
|---|---|---|
| 2 | beta | 66 |
出力
{SheetName:[
{A:1,B:"alfa",C:33},
{A:2,B:"beta",C:66}
]}
タブ表記「& SheetName」
テーブルとして解釈し、左にあるタブのテーブルに内部結合します
（[左タブ] JOIN [& タブ] ON [& タブ]のA列）
※複数のタブでいくつも結合可
左タブ
| id | name | value |
|---|---|---|
| 1 | alfa | 33 |
| 2 | beta | 66 |
& タブ
| value | data | other |
|---|---|---|
| 66 | apple | 6666 |
| 22 | banana | 2222 |
出力
{"左タブ":[
{id:1,name:"alfa",value:33},
{id:2,name:"beta",value:66,data:"apple",other:6666}
]}
タブ表記「SheetName:Map」
HashMap、単体オブジェクトとして出力
| alfa | 33 |
|---|---|
| beta | 66 |
出力
{SheetName:{alfa:33,beta:66}}
タブ表記「//SheetName」
出力しません
code
page.html (js)
<!DOCTYPE html>
<meta charset="UTF-8">
<script type="module">
class Render{
constructor(data){
console.log(data)
//...
}
}
var data = JSON.parse(<?=new SheetData(SHEET_ID).json?>)
new Render(data)
</script>
<style>
html{font-size:12px;}
body{padding:1rem;line-height:1.5}
</style>
コード.gs
var SHEET_ID = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
function debug() {
console.log(new SheetData(SHEET_ID).json)
}
function doGet(){
return HtmlService.createTemplateFromFile('page').evaluate()
}
//シート変換
class SheetData{
constructor(id){
var file = SpreadsheetApp.openById(id)
var entries = this.getEntries(file.getSheets())//[[key,value]]
var index = 0
//INNER JOIN
while(index>-1){
index = entries.findIndex(a=>a[1] instanceof JoinSheet)
if(index>-1){
if(index>0) entries[index-1][1].join(entries[index][1])
entries.splice(index,1)
}
}
this.data = Object.fromEntries(entries)
}
getEntries(sheets){
var entries = []
sheets.forEach(sheet=>{
var s = sheet.getName().trim()
var v = null
switch(true){
case s.indexOf('//')==0 : ;break
case /^&/.test(s) :v = new JoinSheet(sheet) ;break
case /:(Sheet|A1)$/i.test(s) :v = new A1Sheet(sheet) ;break
case /:(Map|Hash)$/i.test(s) :v = new MapSheet(sheet) ;break
case this.isTable(sheet) :v = new TableSheet(sheet) ;break
}
if(v) entries.push([s.split(':').at(0),v])
})
return entries
}
isTable(sheet){return Boolean(sheet.getRange('A1').getValue())}
get json() {return JSON.stringify(this.data)}
}
//Table形式
class TableSheet extends Array{
constructor(...a){
if(a.length==1){
if(a[0] instanceof Object){
if(a[0].hasOwnProperty('getDataRange')) var sheet = a.shift()
}
}
super(...a)
if(sheet) this.parse(sheet.getDataRange().getValues())
}
parse(data){
this.length = 0
this.keys = data.shift()
this.push(...data.map(v=>new TableRow(this.keys,v)))
}
join(table){
var key = table.keys[0]
this.forEach(row=>Object.assign(row,table.find(v=>row[key]==v[key])))
}
}
//JOIN TABLE
class JoinSheet extends TableSheet{}
//ヘッダーなしの表
class A1Sheet extends TableSheet{
parse(data){
this.length = 0
this.keys = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('')
this.push(...data.map(v=>new TableRow(this.keys,v)))
}
}
class TableRow{
constructor(keys,values){
for(var i=0;i<values.length;i++){
if(keys[i]){
var key = keys[i]
if(/^\w+$/.test(key)) this[key] = values[i]
}else{
console.log('x',values[i])
}
}
}
}
//HashMap,Dic形式
class MapSheet{
constructor(sheet){
sheet.getDataRange().getValues().forEach(a=>{
if(a[0]) this[a[0]] = a[1]
})
}
}