【freeeAPI×GAS】スプレッドシートのデータから振替伝票を一括作成する【逆仕訳一括作成ツール②】

こんにちは、もりです！

クラウド会計ソフトfreeeのAPIを使って、freeeの操作を自動化するシリーズ。合計2回の連載で「振替伝票の逆仕訳を一括作成するツール」を作ります。

1回目の記事がこちら。

【freeeAPI×GAS】指定条件の振替伝票をスプレッドシートに取得する【逆仕訳一括作成ツール①】GASを使って「クラウド会計ソフトfreee」のAPIを操作するシリーズ。振替伝票を一括取得してスプレッドシートに出力するスクリプトを紹介しています。...

今回の記事では、スプレッドシートのデータをfreeeAPIに送信して、振替伝票を一括作成するスクリプトを紹介します。

※この記事で紹介するスクリプトを動作させるには、freeeAPIの連携アプリの作成と認証が済んでいることを前提としています。

詳しくは下記記事をご覧ください。

【freee×GAS】GoogleAppsScriptでfreeeAPIと連携認証する（サンプルコードあり）GoogleAppsScriptを使用して、クラウド会計ソフトfreeeのAPI連携が可能です。freee連携アプリの登録方法、認証方法、サンプルコードを紹介しています。...

Contents

逆仕訳一括作成ツール
- 【動画】ツールの使い方（処理の流れ）
スプレッドシートのデータを元に振替伝票を作成する

逆仕訳一括作成ツール

逆仕訳（反対仕訳）とは、貸借を逆に起票する仕訳です。伝票仕訳を間違えた時の訂正や、決算整理仕訳の戻入で作りますね。

【動画】ツールの使い方（処理の流れ）

freeeの振替伝票を使用した「逆仕訳」の作成は、下記の流れで行います。

逆仕訳一括作成の流れ

freeeに登録済みの振替伝票をスプレッドシートに出力する【API使用】
スプレッドシート上で日付を書き換える【手作業】
スプレッドシートのデータを貸借入替でfreeeに登録する【API使用】

詳細は操作デモをご覧ください。※再生すると解説の音声が出るのでご注意ください！

スプレッドシートのデータを元に振替伝票を作成する

前回の記事で、freeeに登録済みの振替伝票をスプレッドシートに出力しました。

今回の記事では、このスプレッドシートのデータを使って振替伝票を作成する処理を作ります。

逆仕訳一括作成の流れ

freeeに登録済みの振替伝票をスプレッドシートに出力する【API使用】
スプレッドシート上で日付などを書き換える【手作業】
スプレッドシートのデータを貸借入替でfreeeに登録する【API使用】←今回はココを作る

【事前準備】スプレッドシートのパラメータを設定

スプレッドシートに「取得項目」と「パラメータ」を設定する枠を用意します。セルの色は下記の意味をもっています。

黄色セル → パラメータを設定（入力）するセル
水色セル → 取得結果を出力するセル

スプレッドシートの「発生日」を任意の日付に書き換えます。（期末計上→期首戻入の場合は、期首日付にする）

「振替伝票ID」の値が同じ行は、同一伝票です。この記事で紹介するスクリプトは、各伝票の「最初の行の日付」を登録します（下記例の場合、3,6,8行目）

貸借入替：「する/しない」の選択肢を設けています。

しない → D列の「貸借」debit/creditをそのまま登録
する → D列の「貸借」debit/creditを逆にして登録

【解説】POSTリクエストで送信するデータの形式

振替伝票の作成は「POSTリクエスト」で実行します。「GETリクエスト」に比べて、リクエストURLと一緒に送信する「パラメータ」が異なります。

const params = {
    method : 'POST',
    contentType : 'application/json',
    headers : {"Authorization":"Bearer " + accessToken},
    payload : {ここにJSONデータを指定}
}

const params = {

method : 'POST',

contentType : 'application/json',

headers : {"Authorization":"Bearer " + accessToken},

payload : {ここにJSONデータを指定}

}

プロパティpayloadに、振替伝票のデータをJSON形式で指定します。

「どのようなJSONデータを指定すればよいか？」は、freee公式リファレンス（/api/1/manual_journals）の「Example Value」で確認できます。

「Model」をクリックすると、各プロパティの説明も確認できます。

「振替伝票の作成」のJSONデータのポイントは details プロパティです。

detailsプロパティは「配列」で、振替伝票の各行を「オブジェクト」にして列挙します（貸借合わせて100行まで指定できる）

"details":[{1行目}, {2行目}, {3行目}, …]

1	"details":[{1行目}, {2行目}, {3行目}, …]

つまり、1件の振替伝票を作成する場合、このようなJSONデータを作ればよいことがわかります。

【スクリプト】

このスクリプトと同じプロジェクト内に、下記記事の認証スクリプト（認証用の関数）を書いておく必要があります。

【freee×GAS】GoogleAppsScriptでfreeeAPIと連携認証する（サンプルコードあり）

具体的には、「getService」関数と、その中で使われている「Client_ID」「Client_Secret」の値です。

そして、下記スクリプトの「createJournalMain」関数を実行すると、スプレッドシートのデータから振替伝票を作成できます。

//エントリポイント
function createJournalMain() {
  
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('振替伝票');
  
  //freeeAPIのアクセストークン取得
  const accessToken = getService().getAccessToken();
  
  //A列の最終行を取得
  //(参考)https://moripro.net/gas-get-specified-lastcol-lastrow/
  const lastRow = sheet.getRange(sheet.getMaxRows(), 1)
  .getNextDataCell(SpreadsheetApp.Direction.UP)
  .getRow();
  
  let startRow,endRow;
  
  let i = 3;
  
  while (i < lastRow) {
    
    startRow = i; //開始行番号
    
    let journals_id = sheet.getRange(i, 1).getValue();
    let journals_next_id = sheet.getRange(i + 1, 1).getValue();
    
    //同一伝票のチェック(同一IDは同一伝票)
    while (journals_id == journals_next_id) {
      
      i++;
      journals_id = sheet.getRange(i, 1).getValue();
      journals_next_id = sheet.getRange(i + 1, 1).getValue();
      
    } 
    
    endRow = i; //終了行番号
    
    //開始行～終了行の伝票オブジェクトを作る
    const objBody = createManualJournalBody_(sheet,startRow,endRow);
    console.log(objBody); //ここでログ出力
    
    //伝票の件数分APIにリクエスト送信
    postManualJournals_(accessToken,objBody);
    Utilities.sleep(1000); //過度のアクセスを回避するため1秒待機
    
    i++; //次の伝票行へ
    
  }
  
}

/*
* 伝票1件分のオブジェクトを作成する
*
* @param {Number} 伝票開始行
* @param {Number} 伝票終了行 
*/
function createManualJournalBody_(sheet,startRow,endRow){
  
  /********** 列情報の設定 **********/
  const col = {
    issue_date: 2,
    entry_side: 4,
    account_item_id: 5,
    tax_code: 7,
    amount: 8,
    description: 10,
    end: 10,
    param: 13
  }
  
  /********** パラメータの設定 **********/
  const parameters = sheet.getRange(1, col.param, 11, 1).getValues();
  const company_id = parameters[1][0]; //事業所ID
  const type = parameters[9][0]; //決算整理
  const reverse = parameters[10][0]; //貸借入替
  
  let adjustment = true; //決算整理仕訳
  if (type === '日常仕訳') adjustment = false;  //日常仕訳
  
  /********** 伝票ボディの作成 **********/
  const header = sheet.getRange(1,1,1,col.end).getValues();
  const entry_side = header[0][col.entry_side-1];
  const tax_code = header[0][col.tax_code-1];
  const amount = header[0][col.amount-1];
  const description = header[0][col.description-1];
  const account_item_id = header[0][col.account_item_id-1];
  
  const rowCnt = endRow - startRow + 1;
  const values = sheet.getRange(startRow,1,rowCnt,col.end).getValues(); //伝票1件
  
  let details = []; //伝票の行数分のデータを格納
  
  for (let i = 0; i < values.length; i++) {
    
    let obj = {}; //1行分のデータを格納
    
    obj[entry_side] = values[i][col.entry_side-1];
    obj[tax_code] = values[i][col.tax_code-1];
    obj[amount] = values[i][col.amount-1];
    obj[description] = values[i][col.description-1];
    obj[account_item_id] = values[i][col.account_item_id-1];
    
    if (reverse === 'する') { //貸借入替
      if (values[i][col.entry_side-1] === 'debit') {
        obj[entry_side] = 'credit';
      } else if (values[i][col.entry_side-1] === 'credit'){
        obj[entry_side] = 'debit';
      }
    } 
    
    details.push(obj);
    
  }
  
  let issue_date = values[0][col.issue_date-1]; //対象伝票の先頭行の日付
  issue_date = Utilities.formatDate(issue_date, 'JST', 'yyyy-MM-dd');
  
  const objBody = {
    company_id: company_id, //事業所ID
    issue_date: issue_date, //発生日
    adjustment: adjustment,//決算整理仕訳
    details: details
  }
  
  return objBody;
  
}


/*
* freeeAPIにPOSTリクエストして振替伝票を登録する
*
* @param {string} アクセストークン
* @param {Object} 登録データ
*/
function postManualJournals_(accessToken,obj) {
  
  //オブジェクトをJSON文字列に変換する
  const json = JSON.stringify(obj);
  
  //リクエストに付与するパラメータ
  const params = {
    method : 'POST',
    contentType : 'application/json',
    headers : {"Authorization":"Bearer " + accessToken},
    payload : json,
    muteHttpExceptions : true //エラーが発生したらレスポンスに出力
  }
  
  const requestUrl = 'https://api.freee.co.jp/api/1/manual_journals';
  
  const response = UrlFetchApp.fetch(requestUrl,params);
  Logger.log(response);
  
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

//エントリポイント

function createJournalMain() {

const ss = SpreadsheetApp.getActiveSpreadsheet();

const sheet = ss.getSheetByName('振替伝票');

//freeeAPIのアクセストークン取得

const accessToken = getService().getAccessToken();

//A列の最終行を取得

//(参考)https://moripro.net/gas-get-specified-lastcol-lastrow/

const lastRow = sheet.getRange(sheet.getMaxRows(), 1)

.getNextDataCell(SpreadsheetApp.Direction.UP)

.getRow();

let startRow,endRow;

let i = 3;

while (i < lastRow) {

startRow = i; //開始行番号

let journals_id = sheet.getRange(i, 1).getValue();

let journals_next_id = sheet.getRange(i + 1, 1).getValue();

//同一伝票のチェック(同一IDは同一伝票)

while (journals_id == journals_next_id) {

i++;

journals_id = sheet.getRange(i, 1).getValue();

journals_next_id = sheet.getRange(i + 1, 1).getValue();

}

endRow = i; //終了行番号

//開始行～終了行の伝票オブジェクトを作る

const objBody = createManualJournalBody_(sheet,startRow,endRow);

console.log(objBody); //ここでログ出力

//伝票の件数分APIにリクエスト送信

postManualJournals_(accessToken,objBody);

Utilities.sleep(1000); //過度のアクセスを回避するため1秒待機

i++; //次の伝票行へ

}

* 伝票1件分のオブジェクトを作成する

* @param {Number} 伝票開始行

* @param {Number} 伝票終了行

function createManualJournalBody_(sheet,startRow,endRow){

/********** 列情報の設定 **********/

const col = {

issue_date: 2,

entry_side: 4,

account_item_id: 5,

tax_code: 7,

amount: 8,

description: 10,

end: 10,

param: 13

}

/********** パラメータの設定 **********/

const parameters = sheet.getRange(1, col.param, 11, 1).getValues();

const company_id = parameters[1][0]; //事業所ID

const type = parameters[9][0]; //決算整理

const reverse = parameters[10][0]; //貸借入替

let adjustment = true; //決算整理仕訳

if (type === '日常仕訳') adjustment = false; //日常仕訳

/********** 伝票ボディの作成 **********/

const header = sheet.getRange(1,1,1,col.end).getValues();

const entry_side = header[0][col.entry_side-1];

const tax_code = header[0][col.tax_code-1];

const amount = header[0][col.amount-1];

const description = header[0][col.description-1];

const account_item_id = header[0][col.account_item_id-1];

const rowCnt = endRow - startRow + 1;

const values = sheet.getRange(startRow,1,rowCnt,col.end).getValues(); //伝票1件

let details = []; //伝票の行数分のデータを格納

for (let i = 0; i < values.length; i++) {

let obj = {}; //1行分のデータを格納

obj[entry_side] = values[i][col.entry_side-1];

obj[tax_code] = values[i][col.tax_code-1];

obj[amount] = values[i][col.amount-1];

obj[description] = values[i][col.description-1];

obj[account_item_id] = values[i][col.account_item_id-1];

if (reverse === 'する') { //貸借入替

if (values[i][col.entry_side-1] === 'debit') {

obj[entry_side] = 'credit';

} else if (values[i][col.entry_side-1] === 'credit'){

obj[entry_side] = 'debit';

}

details.push(obj);

}

let issue_date = values[0][col.issue_date-1]; //対象伝票の先頭行の日付

issue_date = Utilities.formatDate(issue_date, 'JST', 'yyyy-MM-dd');

const objBody = {

company_id: company_id, //事業所ID

issue_date: issue_date, //発生日

adjustment: adjustment,//決算整理仕訳

details: details

}

return objBody;

}

* freeeAPIにPOSTリクエストして振替伝票を登録する

* @param {string} アクセストークン

* @param {Object} 登録データ

function postManualJournals_(accessToken,obj) {

//オブジェクトをJSON文字列に変換する

const json = JSON.stringify(obj);

//リクエストに付与するパラメータ

const params = {

method : 'POST',

contentType : 'application/json',

headers : {"Authorization":"Bearer " + accessToken},

payload : json,

muteHttpExceptions : true //エラーが発生したらレスポンスに出力

}

const requestUrl = 'https://api.freee.co.jp/api/1/manual_journals';

const response = UrlFetchApp.fetch(requestUrl,params);

Logger.log(response);

}

【参考】オブジェクトをログ出力してみる

スプレッドシートのデータからオブジェクト（キー:値のペア）を作る方法はこちらの記事で詳しく紹介しています。

【GAS】スプレッドシートのデータからオブジェクトを作る方法スプレッドシートのデータからオブジェクトを作成する方法を紹介しています。ポイントは「オブジェクトのキーに変数を使う」「配列とオブジェクトをネストする」です...

上記スクリプトの40行目で、1件分の伝票データ(objBody)をログ出力しています。

このようなデータができあがります。1件目の伝票は3行あるので、detailsプロパティの中身に3つのオブジェクトがあります。

{ company_id: xxxxxxx,
  issue_date: '2019-08-01',
  adjustment: true,
  details: 
   [ { entry_side: 'credit',
       tax_code: 2,
       amount: 150000,
       description: '消費税勘定相殺',
       account_item_id: 296536431 },
     { entry_side: 'debit',
       tax_code: 2,
       amount: 100000,
       description: '消費税勘定相殺',
       account_item_id: 296536377 },
     { entry_side: 'debit',
       tax_code: 2,
       amount: 50000,
       description: '消費税勘定相殺',
       account_item_id: 296536422 } 
   ]
}