Skip to content

Instantly share code, notes, and snippets.

@hira22
Last active September 17, 2020 22:39
Show Gist options
  • Save hira22/1b8d966649beda4e272d243ffed092df to your computer and use it in GitHub Desktop.
Save hira22/1b8d966649beda4e272d243ffed092df to your computer and use it in GitHub Desktop.
Google SpreadSheet を JSONファイル に変換してファイルを配置するGAS
// メニューバーにアクションを設定する
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