Last active
September 17, 2020 22:39
-
-
Save hira22/1b8d966649beda4e272d243ffed092df to your computer and use it in GitHub Desktop.
Google SpreadSheet を JSONファイル に変換してファイルを配置するGAS
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// メニューバーにアクションを設定する | |
function onOpen() { | |
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); | |
const entries = [ | |
{ | |
name: "このシートをJSONファイルに変換&出力", | |
functionName: "exportJSON" | |
} | |
]; | |
spreadsheet.addMenu("JSON変換", entries); | |
}; | |
// メインロジック | |
function exportJSON() { | |
const activeSheet = SpreadsheetApp.getActiveSheet(); | |
const sheetName = activeSheet.getName(); | |
const sheetData = activeSheet.getDataRange().getValues(); | |
const header = sheetData[0]; | |
const body = sheetData.slice(1); | |
const json = JSON.stringify(toJSON(header, body)); | |
const fileName = `${sheetName}_${Date.now()}.json`; | |
const folderId = ''; // TODO: 出力先のGoogleDriveフォルダIDを設定 | |
makeFile(json, fileName, folderId); | |
const html = `<p><a href=\"${DriveApp.getFolderById(folderId).getUrl()}\" target="blank">ここにJSONファイルを出力したよ👏</a></p>`; | |
const htmlOutput = HtmlService | |
.createHtmlOutput(html) | |
.setSandboxMode(HtmlService.SandboxMode.IFRAME) | |
.setWidth(300).setHeight(100); | |
SpreadsheetApp.getUi().showModelessDialog(htmlOutput, '完了💁♀️'); | |
}; | |
// bodyのデータを、対応するheaderの値をキーとしたJSONデータに加工し、そのJSON文字列を返す | |
function toJSON(header, body) { | |
return body.map(function (row) { | |
return row.reduce(function (accum, v, i) { | |
accum[header[i]] = isEmpty(v) ? null : v; | |
return accum; | |
}, {}); | |
}); | |
}; | |
// ファイルを作成する | |
function makeFile(jsonData, fileName, folderId) { | |
const contentType = "text/plain"; | |
const charSet = "UTF-8"; | |
const blob = Utilities.newBlob("", contentType, fileName).setDataFromString(jsonData, charSet); | |
DriveApp.getFolderById(folderId).createFile(blob); | |
}; | |
// nullもしくは空の場合にtrue | |
function isEmpty(_var) { | |
if (_var == null) { | |
// typeof null -> object : for hack a bug of ECMAScript | |
return true; | |
} | |
switch (typeof _var) { | |
case 'object': | |
if (Array.isArray(_var)) { | |
// When object is array: | |
return (_var.length === 0); | |
} else { | |
// When object is not array: | |
if (Object.keys(_var).length > 0) { | |
return false; | |
} else | |
if (_var.valueOf().length !== undefined) { | |
return (_var.valueOf().length === 0); | |
} else | |
if (typeof _var.valueOf() !== 'object') { | |
return is_empty(_var.valueOf()); | |
} else { | |
return true; | |
} | |
} | |
case 'string': | |
return (_var === ''); | |
case 'number': | |
return (_var == 0); | |
case 'boolean': | |
return !_var; | |
case 'undefined': | |
case 'null': | |
return true; | |
case 'symbol': | |
case 'function': | |
default: | |
return false; | |
} | |
}; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment