かけいぼgoogle app script

qif.gs

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 {'"':'&quot;', '&':'&amp;', '\'':'&#39;', '<':'&lt;', '>':'&gt;'}[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();
}