GASでAppSuiteレコードの自動生成 3(fin)
insert_dataのPayloadで求められているもの
前回の最後でinsert_dataを使った時にエラーが出た。その解決策を考えるためにAPIの仕様と、部品の設定について確認してみよう。
『返済額』部品は数値で、APIの仕様では『値をそのまま指定します。』とあり、payloadを見てもそのまま指定しているように見える。
これのどこが間違いなのかというと、スプレッドシートから読み取った数値は内部的にいわゆる浮動小数点型の10000.0として扱われていることにある。
実は2か月ほど前まではデバッガの変数で見た時は10000であるにもかかわらず、console.logで出力した時だけ10000.0と結果が返ってきてpayloadの指定ミスが分かったが、執筆時現在はconsole.logで出力しても10000になり指定ミスがわからなくなっている。
GASは変数の型を指定しないため、数値を格納した際に変数の型が決まる。ただしGASには整数型というものは無く数値型(number)であるため、明確に整数値として指定しないスプレッドシートから読み取る場合は、いわゆる浮動小数点型として格納され、「10000」→「10000.0」と変換されてしまう様である。(詳細は私も分からないです)
GASの仕様の問題なので、GASを使う場合に頭の片隅に置いておくといいのかもしれない。
解決策
読み取った数値が整数型であったとしても、送信時には浮動小数点型となってしまう状況で解決策としては以下の様なものが思い浮かぶ。
①AppSuite側で部品の設定の小数点桁数の0を消す
簡単だが、あまりお勧めしない。手入力の際にエラー発生の原因になる。
②GASで整数を返す関数(round等)に入れる
GASで小数点以下を四捨五入等して整数で返ってくる関数を使い明示的に
整数にする。何らかの理由(ミス)で小数が入ってきた場合にエラーが出なくなるので、今回の対応策にはなるが、別の懸念が発生する。
③文字にする
APIの仕様に反するが文字としての数字を指定する。受信側API設計の柔軟性が必要だが、一般的に使われているHTTP通信の仕様にAPIが対応するので、大体問題無い模様。
なおこれでエラーが出る場合は、その前段階のHTTP通信でエラーが出るので、APIが通信が受け取れずエラーメッセージは異なる。
どれも一長一短だが、自分はいつも③の文字として数値を送っている。
実は上のpayloadでも既に"app_id"と"relookup_key_field"はそれぞれ”324”と”101”の文字で指定しているが、AppSuiteAPIの仕様上数値が指定されているにもかかわらず、エラーは出ていない。
スクリプトの修正と送信結果
for(let i = 0;i < writeData.length;i++){
const writeDataRow = writeData[i];
let payload = {
'action': 'insert_data',
'app_id': UL_APP_ID,
'relookup_key_field': '101',
'{{loan_code}}': String(writeDataRow[0]),
'{{repayment_day}}': endMonth,
'{{repayment_amount}}': String(writeDataRow[1])//文字に変換
};
let options = {
'headers': headers,
'payload': payload
};
let response = UrlFetchApp.fetch(APPSUITE_API_UPL, options);
let responseText = response.getContentText();
let parsedResponseText = JSON.parse(responseText);
console.log(parsedResponseText.status);//表示用
if(parsedResponseText.status == "ng"){
console.log(parsedResponseText.errormessage);
}
}
スクリプトで文字への変換関数をpayloadの設定に加えることで、APIのレスポンスはokになり、AppSuiteを確認するときちんと追加できている。
これでようやく大きな一連の流れの一つ一つを作ることができたので、これをつなげるとともに、自動化の仕組みを加えていく。
自動化へ
これまで作ってきたスクリプトは手で動かしていたので、これを自動化していく。
GASではトリガー機能により一定の時刻が来るとスクリプトを実行させることができる。このトリガーは細かい時間指定ができないのでダウンロードから多少時間を空けてアップロードを行うことで対応する。
(厳密にいえば時間指定できないわけではないが、今回の仕組みについては広い意味での夜中に処理されていれば時間指定は不要だろう)
忘れてはいけないAppSuiteの自動化
今回の仕組みではGASでデータを送信したとしても、借入金データを編集するわけではないため自動計算部品について更新されない。
下の図を見てもらえばわかるように、返済履歴にはデータが登録されているものの、返済累計額は0のままであるため貸付残高も10万円のままになっている。
すなわち借入金データの更新が必要になるが、ここでAPIを使うことは良くない。APIはあくまで最終手段と考えるべきで標準機能で対応できる。
APIを使えるからAPIで対応させようというのは保守の高度化により属人化する可能性が大きくなる。
終わりに
AppSuite内でのAPIを使った連携の解説はここでおしまいになる。
外部への連携も実際にやってみると大きな差は無く各種APIの仕様書を読み解くことが増えるだけである。
それより重要なことはデータの流れをどうやって仕組化するのかが難しくなると思うので、GASをさわることよりもいわゆるシステムデザインに大きな時間がかかるようになることは覚悟しておいてほしい。
年末にインフルエンザに感染してしまい多大な影響を受けてしまった。
この記事も完成が年明けになってしまい申し訳ないです。
(参考)HTTP通信の仕様
数値が求められているAPIに対し文字を送信するのは仕様に反するのではないか？
確かに事実としては正しいが、文字としての数値を指定してもHTTP通信の仕様により問題は起こらない。
GASのHTTP通信は指定しない限り”application/x-www-form-urlencoded”形式で行われる。(なお記載しているスクリプトでは明示的に形式指定している)
この形式はHTTP通信では一般的に使われ、送受信の際にデータはすべて文字として扱われる。これはGASに限らず多くの言語で同じ処理をしており、言語のルールではなく通信のルールである。
AppSuiteAPIでも部品によってはpayloadとして指定されているJSON形式では、仕様上それぞれの値が型を持つが、この通信時にはそれもすべてご破算ですべて文字に変換されてしまう。
他の通信形式の例としては
①application/json形式
AppSuiteAPIでは一部の部品をJSON形式で記述するが、この送信内容を全てJSONで指定するもので、freeeAPIでは標準通信形式の様である。ただし数値は文字で送信してもfreeeAPI側で数値として読み取ってくれる。
なおfreeeAPIも”application/x-www-form-urlencoded”も選択可能だが、使ったことは無いため、よくわからない。
②multipart/form-data形式
AppSuiteAPIでも添付ファイルのアップロード時に利用される形式。”application/x-www-form-urlencoded”ではファイルが文字に変換されてしまいファイルを復元できないので、変換されないようにバイナリとして扱うことができる形式である。
現在のところファイル連携はAppSuiteを起点にしているため、APIを利用せず手作業でアップロードしておりAppsuiteAPIで使うことは無いが、GASの場合googleDriveを経由しないとファイルが扱いづらいので、googleDriveへのアップロードにはよく使う。
(参考)全体スクリプト
/******************************************************************
summary |毎月月初に
|借入金データをダウンロードし、返済額累計へアップロードする
******************************************************************/
const SS = SpreadsheetApp.getActiveSpreadsheet();
const API_KEY = SS.getSheetByName("Setting").getRange("B1").getValue();// APIキー
const APPSUITE_API_UPL = SS.getSheetByName("Setting").getRange("B2").getValue(); //APIエンドポイント
const Sheet = SS.getSheetByName("シート1");
const DL_APP_ID = "323"; //借入金データ
const UL_APP_ID = "324"; //返済額累計
/******************************************************************
function name |SpreadSheetSetting()
summary |スプレッドシートの初期化後
|DownloadPayment()でダウンロード処理を行う
******************************************************************/
function SpreadSheetSetting() {
//--------------------既存データのクリア----------------------
let lastRow,lastCol
lastRow = Sheet.getLastRow()
lastCol = Sheet.getLastColumn()
if(lastRow > 1){
Sheet.getRange(2, 1, lastRow-1, lastCol).clearContent()
}
//--------------------データのダウンロード----------------------
DownloadPayment();
}
/******************************************************************
function name |DownloadPayment()
summary |借入金データのダウンロード
******************************************************************/
function DownloadPayment() {
const payload = {
action: 'list_data',
app_id: DL_APP_ID,
fields: JSON.stringify(
[
{ field_alias: 'loan_code'},
{ field_alias: 'repayment'},
]
),
filter: JSON.stringify({ item: [
{ field_id: '107', operator: '>', value: '0',type: "number"},//貸付残高が0より大
]
}),
};
const options = {
headers: {
'X-Desknets-Auth': API_KEY
},
payload: payload
};
const response = UrlFetchApp.fetch(APPSUITE_API_UPL, options).getContentText();
let parsedJsonData = JSON.parse(response);
let repaymentData = parsedJsonData.list.item;
let writeData = [];
for(let i = 0;i < repaymentData.length;i++){
let writeDataRow = [
repaymentData[i]['loan_code'].val,
repaymentData[i]['repayment'].val
];
writeData.push(writeDataRow);
}
Sheet.getRange(2,1,repaymentData.length,writeData[0].length).setValues(writeData);
}
/******************************************************************
function name |SendRepaymentData()
summary |ダウンロードした借入金データを返済額累計へPOST
argument |writeDataRow 借入金データの1次元配列
|[0]: 借入金コード
|[1]: 返済金額
******************************************************************/
function SendRepaymentData() {
// --------------当月末日のデータ作成------------------------
const TODAY = new Date();
const monthStartDate = new Date(TODAY.getFullYear(), TODAY.getMonth()+1, 0);
const endMonth = Utilities.formatDate(monthStartDate, 'JST', 'yyyy-MM-dd');
let lastRow = Sheet.getLastRow();
let lastCol = Sheet.getLastColumn();
let writeData = Sheet.getRange(2,1,lastRow-1,lastCol).getValues();
const headers = {
'X-Desknets-Auth': API_KEY,
'Content-Type': 'application/x-www-form-urlencoded'
};
for(let i = 0;i < writeData.length;i++){
const writeDataRow = writeData[i];
let payload = {
'action': 'insert_data',
'app_id': UL_APP_ID,
'relookup_key_field': '101',
'{{loan_code}}': String(writeDataRow[0]),
'{{repayment_day}}': endMonth,
'{{repayment_amount}}': String(writeDataRow[1])//文字に変換
};
let options = {
'headers': headers,
'payload': payload
};
let response = UrlFetchApp.fetch(APPSUITE_API_UPL, options);
//-----------------デバッグ用--------------------
// let responseText = response.getContentText();
// let parsedResponseText = JSON.parse(responseText);
// console.log(parsedResponseText.status);//表示用
// if(parsedResponseText.status == "ng"){
// console.log(parsedResponseText.errormessage);
// }
}
}