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
- “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.
- No header row is added to the generated journal TSV (because it is expected to be added to the Sheet).
- 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
- #Money Forward journal_sample.csv
- The summary of the split sales records will be formatted as “2020-04 @ {Description}”.
Capture
Pingback: MoneyForward Journal, Create – Questetra Support