Last active
July 16, 2018 13:07
-
-
Save nna774/6ad60e7e6a662ee1c6cfe843f110c866 to your computer and use it in GitHub Desktop.
かけいぼgoogle app script
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 date2str(date, sep) { | |
function pad(number) { | |
return number < 10 ? "0" + number : number.toString(); | |
} | |
return date.getYear() + sep + pad(date.getMonth()+1) + sep + pad(date.getDate()); | |
} | |
function toQif(date, desc, debit, credit, amount) { | |
return [ | |
"!Clear:AutoSwitch", | |
"!Account", | |
"N" + debit, | |
"^", | |
"!Type:Cash", | |
"D" + date2str(date, "/"), | |
"T" + amount, | |
"M" + desc, | |
"L" + credit, | |
"^", | |
].join("\n") + "\n"; | |
} | |
function row2qif(row) { | |
var date = row[0], desc = row[1], debit = row[2], credit = row[3], amount = row[4]; | |
return toQif(date, desc, debit, credit, amount); | |
} | |
function row2qifBus(row) { | |
var date = row[0], type = row[1], number = row[2], from = row[3], to = row[4], nota = row[5]; | |
hiru = type === "昼券"; | |
nota_tatekae = nota === "yes"; | |
amount = hiru ? 165 : 200; // magic | |
credit = hiru ? "資産:流動資産:有価証券類:市バス昼間回数券" : "資産:流動資産:有価証券類:市バス回数券" | |
desc = number + ": " + from + " → " + to; | |
debit = "費用:交通機関:バス:市バス" | |
if (nota_tatekae) { | |
fee = 230; // | |
return [ | |
"!Clear:AutoSwitch", | |
"!Account", | |
"N" + "資産:貸付:nota:立替", | |
"^", | |
"!Type:Cash", | |
"D" + date2str(date, "/"), | |
"T" + fee, | |
"M" + desc, | |
"S" + credit, | |
"$" + amount, | |
"S" + "収益:その他の収入:nota", | |
"$" + (fee - amount), | |
"^", | |
].join("\n") + "\n"; | |
} | |
return toQif(date, desc, debit, credit, amount); | |
} | |
function exportQif(sheetname, row2qif) { | |
var ss = SpreadsheetApp.getActiveSpreadsheet(); | |
var range = ss.getSheetByName(sheetname).getDataRange(); | |
var qif = range.getValues().slice(1).map(row2qif).join(""); | |
var name = date2str(new Date(Date.now()), "-") + ".qif"; | |
Logger.log(qif); | |
return DriveApp.createFile(name, qif, "application/qif"); | |
} | |
function downloadQif(file) { | |
function htmlEscape(s) { | |
return s.replace(/["&'<>]/g, function(c) { return {'"':'"', '&':'&', '\'':''', '<':'<', '>':'>'}[c]; }); | |
} | |
var html = '<script>window.location=("'+htmlEscape(file.getDownloadUrl())+'");</script>'; | |
SpreadsheetApp.getUi().showModalDialog(HtmlService.createHtmlOutput(html), 'Downloading...'); | |
} | |
function download() { | |
return downloadQif(exportQif("transactions", row2qif)); | |
} | |
function downloadBus() { | |
return downloadQif(exportQif("bus_transactions", row2qifBus)); | |
} | |
function onOpen() { | |
var ui = SpreadsheetApp.getUi(); | |
var menu = ui.createMenu('🐾'); | |
menu.addItem("Export QIF", 'download').addToUi(); | |
menu.addItem("Export Bus QIF", 'downloadBus').addToUi(); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment