2021-10-05 / Add-ons / ,
MoneyForward Journal, Create
MoneyForward Journal, Create
Creates the Journal-Slips (TSV or Excel) for MoneyForward Cloud Accounting. It supports not only batch sales invoices but also invoices that are prorated for each month.
Configs
  • A: Select STRING DATA for Slip Description *
  • B: Select NUMERIC DATA for Sales Amount (Single Record)
  • C: Select DATE DATA for Sales Record (Single Record)
  • D: Select NUMERIC DATA for Sales Amount (Multiple Records)
  • E: Select DATE DATA for First Sales Record (Next Month 1st Day)
  • F: Select NUMERIC DATA for Division Number
  • G: Select DATE DATA for Scheduled Due Date *
  • M: Set Record Tpl for Receivable Single (e.g. “,,AR,,,,,,Sa,*”)#{EL}
  • N: Set Record Tpl for Received Single (e.g. “,,Rvd,,,,,,Sa,*”)#{EL}
  • P: Set Record Tpl for Receivable Sales (e.g. “,,AR,,,,,,Sa,*”)#{EL}
  • Q: Set Record Tpl for Received Sales (e.g. “,,Rvd,,,,,,Sa,*”)#{EL}
  • R: Set Record Tpl for Payment of Receivable (e.g. “Bank,,na”)#{EL}
  • S: Set Record Tpl for Payment of Received (e.g. “Bank,,na”)#{EL}
  • X: Select STRING DATA for TSV (update)
  • Y: Select FILES DATA for Excel-CSV (update)
  • Z: Set End Month of Fiscal Period (eg March: “3” )#{EL}
Script (click to open)

// GraalJS Script (engine type: 2)
// 
// Notes:
// "Transaction No."
// - The slip generated on April 1 = 8 digits of "401yyyzz" (closing month "12").
// - The slip generated on April 1 = 8 digits of "101yyyzz" (closing month "3").
// - The slip generated on March 31 = 9 digits "1231yyyzz" (closing month "3").
// - The yyy part is the last three digits of {process ID}.
// - The zz part is the split ID (01,02, ...).
// - It may be renumbered when imported to MF.
// - "Transaction No." such as "manual entry" will be the increment value.
// No header row is added to the generated "journal TSV". (for Sheet Append)
// The following 21 items are posted in the header line of "Journal Excel-CSV"
// - [0] Transaction No, [1] Transaction Date,
// - [2] Debit account, [3] Debit sub-item, [4] Debit tax classification,
// - [5] Debit department, [6] Debit amount (yen), [7] Debit tax amount,
// - [8] Credit account, [9] Credit sub-item, [10] Credit tax classification,
// - [11] Credit department, [12] Credit amount (yen), [13] Credit tax amount,
// - [14] description, [15] journal memo, [16] tag, [17] MF journal type,
// - [18] Closing journal, [19] Creation date, [20] Last update date
// #Money Forward journal_sample.csv
// https://support.biz.moneyforward.com/account/guide/import-books/ib01.html
// The description of the split sales records in the format "2020-04 @ {Description}".
//
// Notes (ja):
// "取引No" は、
// - 4月1日に生成される伝票は、決算月が"12"の場合 "401yyyzz" の8桁となります。
// - 4月1日に生成される伝票は、決算月が "3"の場合 "101yyyzz" の8桁となります。
// - 3月31日に生成される伝票は、決算月が"3"の場合"1231yyyzz" の9桁となります。
// - yyy 部は {プロセスID} の下三桁になります。
// - zz 部は分割IDになります（01,02,…）。
// - ※MFにインポートした際にリナンバーされる場合があります。
// - ※その後の「手動で仕訳」等の "取引No" はインクリメント値になります。
// 生成される「仕訳TSV」にヘッダ行は付与されません。（Sheet追記が想定されるため）
// 生成される「仕訳Excel-CSV」のヘッダ行には以下の21項目がCSV形式で掲示されます
// - [0]取引No,[1]取引日,
// - [2]借方勘定科目,[3]借方補助科目,[4]借方税区分,
// - [5]借方部門,[6]借方金額(円),[7]借方税額,
// - [8]貸方勘定科目,[9]貸方補助科目,[10]貸方税区分,
// - [11]貸方部門,[12]貸方金額(円),[13]貸方税額,
// - [14]摘要,[15]仕訳メモ,[16]タグ,[17]MF仕訳タイプ,
// - [18]決算整理仕訳,[19]作成日時,[20]最終更新日時
// ＃Money Forward journal_sample.csv
// https://support.biz.moneyforward.com/account/guide/import-books/ib01.html
// 分割された売上レコードの摘要は "2020-04 @ {摘要}" といった書式となります。
//
// ＜４伝票のテンプレート設定例＞
// カンマ（20個）で区切られた文字列で振替伝票行のテンプレートを設定します。
// 変数（EL式）を挿入すると、変数部が案件に応じて変化します。
// ※なお "[0],[1],[6],[12],[14]" の各項は自動的に上書きされます
// 
// - 売掛金売上伝票を『[17]MF仕訳タイプ』を "未実現" にしたい場合
// -- ",,売掛金,,対象外,,,0,売上高,,課税売上 10%,,,0,,,,未実現,,,"
// - 前受金売上伝票の『[3]借方補助科目』を "取引先" に『[9]貸方補助科目』を "商品種" に
// -- ",,前受金,#{#q_Billto},対象外,,,0,売上高,#{#q_Product},課税売上 10%,,,0,,,,,,,"
// - 売掛金回収伝票の『[9]貸方補助科目』を "取引先" にし、タイプを "未実現" に
// -- ",,普通貯金,,対象外,,,0,売掛金,#{#q_Billto},対象外,,,0,,,,未実現,,,"
// - 前受金回収伝票の『[9]貸方補助科目』を "取引先" にし、タイプを "未実現" に
// -- ",,普通貯金,みずほ銀行,対象外,,,0,前受金,#{#q_Billto},対象外,,,0,,,,未実現,,,"


//////// START "main()" ////////////////////////////////////////////////////////////////
main();
function main(){

//// == for "MoneyForwardクラウド会計" ==
const myNumOfCols = 21; // マネーフォワードのヘッダ行は以下の21項目
const headerRow =
  "取引No,取引日,借方勘定科目,借方補助科目,借方税区分,借方部門,借方金額(円),借方税額," +
  "貸方勘定科目,貸方補助科目,貸方税区分,貸方部門,貸方金額(円),貸方税額,摘要," +
  "仕訳メモ,タグ,MF仕訳タイプ,決算整理仕訳,作成日時,最終更新日時";
const colSlipId = 0;  // "取引No" のセル位置（MoneyForwardの場合は 0）
const colDate   = 1;  // "取引日" のセル位置（MoneyForwardの場合は 1）
const ymdFormat = "YYYY/MM/DD"; // 日付フォーマット（MoneyForwardの場合は "YYYY/MM/DD"）
const colDrAmt  = 6;  // "借方金額" のセル位置（MoneyForwardの場合は 6）
const colCrAmt  = 12; // "貸方金額" のセル位置（MoneyForwardの場合は 12）
const decimalPlaces = 0; // 通貨の小数点以下有効数字（日本円の場合は 0）
const colSlipDescr = 14; // "摘要" のセル位置（MoneyForwardの場合は 14）

//// == Config Retrieving / 工程コンフィグの参照 ==
const dataIdA = configs.get( "conf_DataIdA" ) + ""; // required
const dataIdB = configs.get( "conf_DataIdB" ) + "";
const dataIdC = configs.get( "conf_DataIdC" ) + "";
const dataIdD = configs.get( "conf_DataIdD" ) + "";
const dataIdE = configs.get( "conf_DataIdE" ) + "";
const dataIdF = configs.get( "conf_DataIdF" ) + "";
const dataIdG = configs.get( "conf_DataIdG" ) + ""; // required

let   tplReceivableSingle = configs.get( "conf_TplReceivableSingle" ) + "";
  if( tplReceivableSingle === ""){
      tplReceivableSingle = ",,売掛金,,対象外,,,0,売上高,,課税売上 10%,,,0,,,,,,,";}
  const arrTplReceivableSingle = tplReceivableSingle.split(",");
let   tplReceivedSingle   = configs.get( "conf_TplReceivedSingle" ) + "";
  if( tplReceivedSingle   === ""){
      tplReceivedSingle   = ",,前受金,,対象外,,,0,売上高,,課税売上 10%,,,0,,,,,,,";}
  const arrTplReceivedSingle   = tplReceivedSingle.split(",");
let   tplReceivableSales   = configs.get( "conf_TplReceivableSales" ) + "";
  if( tplReceivableSales   === ""){
      tplReceivableSales = ",,売掛金,,対象外,,,0,売上高,,課税売上 10%,,,0,,,,,,,";}
  const arrTplReceivableSales = tplReceivableSales.split(",");
let   tplReceivedSales     = configs.get( "conf_TplReceivedSales" ) + "";
  if( tplReceivedSales     === ""){
      tplReceivedSales   = ",,前受金,,対象外,,,0,売上高,,課税売上 10%,,,0,,,,,,,";}
  const arrTplReceivedSales   = tplReceivedSales.split(",");
let   tplPaymentReceivable = configs.get( "conf_TplPaymentReceivable" ) + "";
  if( tplPaymentReceivable === ""){
      tplPaymentReceivable = ",,普通預金,,対象外,,,0,売掛金,,対象外,,,0,,,,,,,";}
  const arrTplPaymentReceivable = tplPaymentReceivable.split(",");
let   tplPaymentReceived   = configs.get( "conf_TplPaymentReceived" ) + "";
  if( tplPaymentReceived   === ""){
      tplPaymentReceived     = ",,普通預金,,対象外,,,0,前受金,,対象外,,,0,,,,,,,";}
  const arrTplPaymentReceived   = tplPaymentReceived.split(",");
if( myNumOfCols !== tplReceivableSingle.split(",").length ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {M} does not match 20-commas Header \n" );
}
if( myNumOfCols !== tplReceivedSingle.split(",").length ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {N} does not match 20-commas Header \n" );
}
if( myNumOfCols !== tplReceivableSales.split(",").length ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {P} does not match 20-commas Header \n" );
}
if( myNumOfCols !== tplReceivedSales.split(",").length ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {Q} does not match 20-commas Header \n" );
}
if( myNumOfCols !== tplPaymentReceivable.split(",").length ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {R} does not match 20-commas Header \n" );
}
if( myNumOfCols !== tplPaymentReceived.split(",").length ){
  throw new Error( "\n AutomatedTask ConfigError:" +
                   " Config {S} does not match 20-commas Header \n" );
}

const dataIdX = configs.get( "conf_DataIdX" ) + "";
const dataIdY = configs.get( "conf_DataIdY" ) + "";

let numEndMonth = 12;
const monthInt = /^([1-9]|1[0-2])$/; // RegExp: 1,2,,,11,12
const strEndMonth = configs.get( "conf_EndMonth" ) + "";
if( strEndMonth === "" ){
  engine.log( " AutomatedTask ConfigWarning:" +
              " {Z:EndMonth} is 12 (default)" );
}else{
  if( monthInt.test( strEndMonth ) ){
    numEndMonth = parseInt(strEndMonth, 10);
  }else{
    throw new Error( "\n AutomatedTask ConfigError:" +
                     " Config {Z:EndMonth} must be 1 to 12 \n" );
  }
}


//// == Data Retrieving / ワークフローデータの参照 ==
let slipDescr = ""; // config required
if( engine.findDataByNumber( dataIdA ) === null ){
  engine.log( " AutomatedTask StringWarning:" +
              " {A} is empty" );
}else{
  slipDescr = engine.findDataByNumber( dataIdA ) + "";
}

let salesAmtSingle       = engine.findDataByNumber( dataIdB ) - 0;  // null-0 -> 0
let salesAmtMultiple     = engine.findDataByNumber( dataIdD ) - 0;
let salesAmtMultipleNum  = engine.findDataByNumber( dataIdF ) - 0;
if( salesAmtSingle === 0 && salesAmtMultiple === 0){ //Case 0: Error exit
  throw new Error( "\n AutomatedTask UnexpectedError:" +
                   " Both {B:salesAmtSingle} and {D:salesAmtMultiple} are zero \n" );
}

let salesAmtSingleDate   = engine.findDataByNumber( dataIdC ) + "";
let salesAmtMultipleDate = engine.findDataByNumber( dataIdE ) + "";

let dueDate = ""; // config required
if( engine.findDataByNumber( dataIdG ) === null ){
  throw new Error( "\n AutomatedTask UnexpectedDateError:" +
                   " Date {G} is null \n" );
}else{
  dueDate = engine.findDataByNumber( dataIdG ) + "";
}
const dateDueDate = toJsDate( dueDate );


//// == Calculating / 演算 ==
//  [M] numMonthSerial (Serial Number, 1 means the first month of the fiscal year.)
//               01 02 03 04 05 06 07 08 09 10 11 12
//  EndMonth= 1: 12  1  2  3  4  5  6  7  8  9 10 11
//  EndMonth= 3: 10 11 12  1  2  3  4  5  6  7  8  9
//  EndMonth=12:  1  2  3  4  5  6  7  8  9 10 11 12
const dateToday = new Date();
const numMonthSerial = (dateToday.getMonth() + 12 - numEndMonth ) % 12 + 1;
//  [MDD] todaySerial
const todaySerial = numMonthSerial * 100 + dateToday.getDate();
/// "取引No" (tmpSlipId): 8or9桁 MDDyyyzz,
let tmpSlipId = todaySerial * 100000 +
                (processInstance.getProcessInstanceId() % 1000) * 100;

let myTsv    = "";

//Case 1: Only Single Sales / 一括売上だけの場合
if(salesAmtSingle !== 0 && salesAmtMultiple === 0){
  let dateSalesAmtSingleDate = toJsDate( salesAmtSingleDate );
  engine.log( " AutomatedTask message:" +
              " Single only" );

  if( dateSalesAmtSingleDate.getTime() <= dateDueDate.getTime() ){
    tmpSlipId++;
    let tmpArrSales        = arrTplReceivableSingle.concat();
    tmpArrSales[colSlipId] = tmpSlipId + "";
    tmpArrSales[colDate]   = funcDateStr( dateSalesAmtSingleDate, ymdFormat );
    tmpArrSales[colDrAmt]  = salesAmtSingle;
    tmpArrSales[colCrAmt]  = salesAmtSingle;
    tmpArrSales[colSlipDescr] = slipDescr;
    myTsv += tmpArrSales.join("\t");
    myTsv += "\n";

    tmpSlipId++;
    let tmpArrPay          = arrTplPaymentReceivable.concat();
    tmpArrPay[colSlipId]   = tmpSlipId + "";
    tmpArrPay[colDate]     = funcDateStr( dateDueDate, ymdFormat );
    tmpArrPay[colDrAmt]    = salesAmtSingle;
    tmpArrPay[colCrAmt]    = salesAmtSingle;
    tmpArrPay[colSlipDescr] = slipDescr;
    myTsv += tmpArrPay.join("\t");
  }else{
    tmpSlipId++;
    let tmpArrSales        = arrTplReceivedSingle.concat();
    tmpArrSales[colSlipId] = tmpSlipId + "";
    tmpArrSales[colDate]   = funcDateStr( dateSalesAmtSingleDate, ymdFormat );
    tmpArrSales[colDrAmt]  = salesAmtSingle;
    tmpArrSales[colCrAmt]  = salesAmtSingle;
    tmpArrSales[colSlipDescr] = slipDescr;
    myTsv += tmpArrSales.join("\t");
    myTsv += "\n";

    tmpSlipId++;
    let tmpArrPay          = arrTplPaymentReceived.concat();
    tmpArrPay[colSlipId]   = tmpSlipId + "";
    tmpArrPay[colDate]     = funcDateStr( dateDueDate, ymdFormat );
    tmpArrPay[colDrAmt]    = salesAmtSingle;
    tmpArrPay[colCrAmt]    = salesAmtSingle;
    tmpArrPay[colSlipDescr] = slipDescr;
    myTsv += tmpArrPay.join("\t");
  }

}

//Case 2:  Including Multiple Sales / 分割売上を含む場合
if(salesAmtMultiple !== 0){
  if(salesAmtMultipleNum === 0){
    throw new Error( "\n AutomatedTask UnexpectedConfigError:" +
                     " '0' can not be used as division number. \n" );
  }
  engine.log( " AutomatedTask message:" +
              " Multiple " + salesAmtMultipleNum );

  // Subtotal of ReceivableSales and ReceivedSales
  let myReceivableSales = 0;
  let myReceivedSales = 0;

  //// Single
  if(salesAmtSingle !== 0){
    engine.log( " AutomatedTask message:" +
                " Multiple with Single" );
    let dateSalesAmtSingleDate = toJsDate( salesAmtSingleDate );

    if( dateSalesAmtSingleDate.getTime() <= dateDueDate.getTime() ){
      tmpSlipId++;
      let tmpArrSales        = arrTplReceivableSingle.concat();
      tmpArrSales[colSlipId] = tmpSlipId + "";
      tmpArrSales[colDate]   = funcDateStr( dateSalesAmtSingleDate, ymdFormat );
      tmpArrSales[colDrAmt]  = salesAmtSingle;
      tmpArrSales[colCrAmt]  = salesAmtSingle;
      tmpArrSales[colSlipDescr] = slipDescr;
      myTsv += tmpArrSales.join("\t");
      myTsv += "\n";
      myReceivableSales += salesAmtSingle;
    }else{
      tmpSlipId++;
      let tmpArrSales        = arrTplReceivedSingle.concat();
      tmpArrSales[colSlipId] = tmpSlipId + "";
      tmpArrSales[colDate]   = funcDateStr( dateSalesAmtSingleDate, ymdFormat );
      tmpArrSales[colDrAmt]  = salesAmtSingle;
      tmpArrSales[colCrAmt]  = salesAmtSingle;
      tmpArrSales[colSlipDescr] = slipDescr;
      myTsv += tmpArrSales.join("\t");
      myTsv += "\n";
      myReceivedSales += salesAmtSingle;
    }
  }

  //// Multiple
  // Sales for each month
  // The remainder is added to the first month. / 商の剰余は初月に加算
  // "101/10" is divided into "10.10" * 10, in the case of two decimal places.
  // "101/10" is divided into "11" * 1 and "10" * 9, in the case of 0 decimal place.
  const monthlySalesFollow =
        Math.floor( salesAmtMultiple / salesAmtMultipleNum * Math.pow(10, decimalPlaces) ) /
        Math.pow(10, decimalPlaces);
  const monthlySalesFirst  =
        salesAmtMultiple - monthlySalesFollow * ( salesAmtMultipleNum - 1 );
  let arrSalesAmtMultipleDate  = new Array( salesAmtMultipleNum );

  // First Month
  arrSalesAmtMultipleDate[0]   = toJsDate( salesAmtMultipleDate );
  if( arrSalesAmtMultipleDate[0].getTime() <= dateDueDate.getTime() ){
    tmpSlipId++;
    let tmpArrSales        = arrTplReceivableSales.concat();
    tmpArrSales[colSlipId] = tmpSlipId + "";
    tmpArrSales[colDate]   = funcDateStr( arrSalesAmtMultipleDate[0], ymdFormat );
    tmpArrSales[colDrAmt]  = monthlySalesFirst;
    tmpArrSales[colCrAmt]  = monthlySalesFirst;
    tmpArrSales[colSlipDescr] =
      funcMonthStr( arrSalesAmtMultipleDate[0] ) + " @ " + slipDescr;
    myTsv += tmpArrSales.join("\t");
    myTsv += "\n";
    myReceivableSales += monthlySalesFirst;
  }else{
    tmpSlipId++;
    let tmpArrSales        = arrTplReceivedSales.concat();
    tmpArrSales[colSlipId] = tmpSlipId + "";
    tmpArrSales[colDate]   = funcDateStr( arrSalesAmtMultipleDate[0], ymdFormat );
    tmpArrSales[colDrAmt]  = monthlySalesFirst;
    tmpArrSales[colCrAmt]  = monthlySalesFirst;
    tmpArrSales[colSlipDescr] =
      funcMonthStr( arrSalesAmtMultipleDate[0] ) + " @ " + slipDescr;
    myTsv += tmpArrSales.join("\t");
    myTsv += "\n";
    myReceivedSales += monthlySalesFirst;
  }

  // Following Months
  // Record on 1st of each month / 次月以降は1日に売上計上
  for( let i=1; i < salesAmtMultipleNum; i++){
    arrSalesAmtMultipleDate[i] = new Date(
                                     arrSalesAmtMultipleDate[0].getFullYear(),
                                     arrSalesAmtMultipleDate[0].getMonth() + i,
                                     1); // 1st of each month
    if( arrSalesAmtMultipleDate[i].getTime() <= dateDueDate.getTime() ){
      tmpSlipId++;
      let tmpArrSales        = arrTplReceivableSales.concat();
      tmpArrSales[colSlipId] = tmpSlipId + "";
      tmpArrSales[colDate]   = funcDateStr( arrSalesAmtMultipleDate[i], ymdFormat );
      tmpArrSales[colDrAmt]  = monthlySalesFollow;
      tmpArrSales[colCrAmt]  = monthlySalesFollow;
      tmpArrSales[colSlipDescr] =
        funcMonthStr( arrSalesAmtMultipleDate[i] ) + " @ " + slipDescr;
      myTsv += tmpArrSales.join("\t");
      myTsv += "\n";
      myReceivableSales += monthlySalesFollow;
    }else{
      tmpSlipId++;
      let tmpArrSales        = arrTplReceivedSales.concat();
      tmpArrSales[colSlipId] = tmpSlipId + "";
      tmpArrSales[colDate]   = funcDateStr( arrSalesAmtMultipleDate[i], ymdFormat );
      tmpArrSales[colDrAmt]  = monthlySalesFollow;
      tmpArrSales[colCrAmt]  = monthlySalesFollow;
      tmpArrSales[colSlipDescr] =
        funcMonthStr( arrSalesAmtMultipleDate[i] ) + " @ " + slipDescr;
      myTsv += tmpArrSales.join("\t");
      myTsv += "\n";
      myReceivedSales += monthlySalesFollow;
    }
  }
  if(myReceivableSales !== 0 && myReceivedSales !== 0){
    tmpSlipId++;
    let tmpArrPay1         = arrTplPaymentReceivable.concat();
    tmpArrPay1[colSlipId]  = tmpSlipId + "";
    tmpArrPay1[colDate]    = funcDateStr( dateDueDate, ymdFormat );
    tmpArrPay1[colDrAmt]   = myReceivableSales + myReceivedSales;
    tmpArrPay1[colCrAmt]   = myReceivableSales;
    tmpArrPay1[colSlipDescr] = slipDescr;
    myTsv += tmpArrPay1.join("\t");
    myTsv += "\n";
    let tmpArrPay2         = arrTplPaymentReceived.concat();
    tmpArrPay2[colSlipId]  = tmpSlipId + "";
    tmpArrPay2[colDate]    = funcDateStr( dateDueDate, ymdFormat );
    tmpArrPay2[colDrAmt]   = 0;
    tmpArrPay2[colCrAmt]   = myReceivedSales;
    tmpArrPay2[colSlipDescr] = slipDescr;
    myTsv += tmpArrPay2.join("\t");
  }else if(myReceivableSales === 0){
    tmpSlipId++;
    let tmpArrPay          = arrTplPaymentReceived.concat();
    tmpArrPay[colSlipId]   = tmpSlipId + "";
    tmpArrPay[colDate]     = funcDateStr( dateDueDate, ymdFormat );
    tmpArrPay[colDrAmt]    = myReceivableSales + myReceivedSales;
    tmpArrPay[colCrAmt]    = myReceivedSales;
    tmpArrPay[colSlipDescr] = slipDescr;
    myTsv += tmpArrPay.join("\t");
  }else if(myReceivedSales === 0){
    tmpSlipId++;
    let tmpArrPay          = arrTplPaymentReceivable.concat();
    tmpArrPay[colSlipId]   = tmpSlipId + "";
    tmpArrPay[colDate]     = funcDateStr( dateDueDate, ymdFormat );
    tmpArrPay[colDrAmt]    = myReceivableSales + myReceivedSales;
    tmpArrPay[colCrAmt]    = myReceivableSales;
    tmpArrPay[colSlipDescr] = slipDescr;
    myTsv += tmpArrPay.join("\t");
  }
}


//// == Data Updating / ワークフローデータへの代入 ==
if( dataIdX !== "" ){
  engine.setDataByNumber( dataIdX, myTsv );
}
if( dataIdY !== "" ){
  let outputFiles = engine.findDataByNumber( dataIdY ); 
  // java.util.ArrayList
  // - com.questetra.bpms.core.event.scripttask.QfileView
  if( outputFiles === null ){
    outputFiles = new java.util.ArrayList();
  }
  let strSjisCsv = headerRow + "\n" +
                   myTsv.replace( /,/g, "" ).replace( /\t/g, "," );
  outputFiles.add(
    new com.questetra.bpms.core.event.scripttask.NewQfile(
      (processInstance.getProcessInstanceId() + ".csv"),  // fileName
      "text/comma-separated-values; charset=Shift_JIS",   // for sjis
      strSjisCsv
    )
  );
  engine.setDataByNumber( dataIdY, outputFiles );
}


} //////// END "main()" ////////////////////////////////////////////////////////////////


function funcDateStr( dateObj, formatStr ){
  formatStr = formatStr.replace(/YYYY/g, dateObj.getFullYear());
  formatStr = formatStr.replace(/MM/g,   ('00' + (dateObj.getMonth() + 1)).slice(-2));
  formatStr = formatStr.replace(/DD/g,   ('00' +  dateObj.getDate()).slice(-2));
  return formatStr;
}

function funcMonthStr( dateObj ){
  let myStr = dateObj.getFullYear() + "-" + ("00" + (dateObj.getMonth()+1)).slice(-2);
  return myStr;
}

function toJsDate( bpmsDateOrDatetimeStr ){
  // BPMS Date:     "2020-04-01"
  // BPMS Datetime: "2020-04-01 23:59"

  let year       = 0;
  let monthIndex = 0;
  let day        = 0;
  let hours      = 0;
  let minutes    = 0;

  //  The Date object has a large number of methods.
  // "Date.parse" is danger (strongly discouraged)
  // - new Date("2014-11-10") // Mon Nov 10 2014 09:00:00 GMT+0900 (JST)
  // - new Date(2014, 10, 10) // Mon Nov 10 2014 00:00:00 GMT+0900 (JST)
  let arrDatetime = bpmsDateOrDatetimeStr.split(" ");
  if( arrDatetime.length === 1 ){
    let arrDateParts = arrDatetime[0].split("-");
    year       = parseInt(arrDateParts[0], 10);
    monthIndex = parseInt(arrDateParts[1], 10) - 1;
    day        = parseInt(arrDateParts[2], 10);
  }
  if( arrDatetime.length === 2 ){
    let arrDateParts = arrDatetime[0].split("-");
    let arrTimeParts = arrDatetime[1].split(":");
    year       = parseInt(arrDateParts[0], 10);
    monthIndex = parseInt(arrDateParts[1], 10) - 1;
    day        = parseInt(arrDateParts[2], 10);
    hours      = parseInt(arrTimeParts[0], 10);
    minutes    = parseInt(arrTimeParts[1], 10);
  }

  return new Date( year, monthIndex, day, hours, minutes );
}

Download

2021-08-03 (C) Questetra, Inc. (MIT License)
https://support.questetra.com/addons/moneyforward-journal-create-2021/
The Add-on import feature is available with Professional edition.

Notes

  1. “Transaction No.”
    • The slip generated on April 1 = 8 digits of “401yyyzz” (if the closing month is 12).
    • The slip generated on April 1 = 8 digits of “101yyyzz” (if the closing month is 3).
    • The slip generated on March 31 = 9 digits “1231yyyzz” (if the closing month is 3).
    • The yyy part is the last three digits of {process ID}.
    • The zz part is the split ID (01,02, …).
    • It may be renumbered when imported to MF.
    • The Transaction No. in the manual entry will be an incremental value.
  2. No header row is added to the generated journal TSV (because it is expected to be added to the Sheet).
  3. The following 21 items are posted in the header line of the Journal Excel-CSV
    • [0] Transaction No, [1] Transaction Date,
    • [2] Debit account, [3] Debit sub-item, [4] Debit tax classification,
    • [5] Debit department, [6] Debit amount (yen), [7] Debit tax amount,
    • [8] Credit account, [9] Credit sub-item, [10] Credit tax classification,
    • [11] Credit department, [12] Credit amount (yen), [13] Credit tax amount,
    • [14] Description, [15] Journal memo, [16] Tag, [17] MF journal type,
    • [18] Closing journal, [19] Creation date, [20] Last update date
  4. #Money Forward journal_sample.csv
  5. The summary of the split sales records will be formatted as “2020-04 @ {Description}”.

Capture

Creates the Journal-Slips (TSV or Excel) for MoneyForward Cloud Accounting. It supports not only invoices that are booked as single sales record, but also invoices that are booked as monthly sales records.
Journal Slips, Generate Automatically

See also

Fiscal Month-Day String, Create
Google Sheets: Sheet, Append TSV

1 thought on “MoneyForward Journal, Create”

  1. Pingback: MoneyForward Journal, Create – Questetra Support

Comments are closed.

%d bloggers like this: