April 24, 2018 at 20:28Payment management in Privat24 from Google spreadsheets
- Tutorial
Working with the Internet banking of a large number of enterprises can be quite a routine task. To create payments and control them, it is necessary to switch between offices each time. And if there are more than 50 such organizations, then you can easily go astray and make mistakes, not to mention spoiled time. Let's see how to make life easier for an accountant using the PrivatBank API for Business example.
To work with an account, you need to create an Auto Client and obtain data for authorization . We will store these data, as well as payment information in a Google spreadsheet.
Tools / Script Editor. Here we will write a backend for Google Apps Script , which is based on Java Script. Our task is to create an http-request, indicating the necessary details.
The script will respond to changes in the last column. To do this, add a trigger when changing (onEdit) - execute the onEditt () function. And the function itself:
var ss = SpreadsheetApp.getActive();
var sheet = ss.getActiveSheet();
var lastRow = sheet.getLastRow();
var lastCol = sheet.getLastColumn();
function onEditt(e) {
var range = e.range;
var col = range.getColumn();
var row = range.getRow();
if (row > 1) {
if (col == lastCol) {
var value = range.getValue();
if (value == 'Отправить') {
createPayment(range.getRow());
}
else if (value == 'Проверить') {
checkPayment(range.getRow(), sheet);
}
range.clearContent();
}
}
Creating Payments
To create a payment system, a POST request is used. In the process, a document number is generated, which will come in handy a little later. As a result, execution statuses for each request will be visible - either success or error.
var url = "https://acp.privatbank.ua/api/proxy/payment/create_pred";
var day = getDay();
//передать номер строки в таблице
function createPayment(i) {
var iRange = sheet.getRange(i, 1, 1, lastCol);
var values = iRange.getDisplayValues();
var docNumRange = sheet.getRange(i, 3);
var stateRange = sheet.getRange(i, 10);
var state = stateRange.getValue();
if (state == 'Создано') {
iRange.setBackground('#66ff66');
return;
} else if (state == 'Оплачено') {
iRange.setBackground('#ebebe0');
return;
}
docNumRange.clearContent();
var name = values[0][0];
var userData = getUserData(name);
if (!userData) {
iRange.setBackground('#ffff80');
stateRange.setValue('Нет данных пользователя');
return;
}
var inn = userData[1];
var id = userData[2];
var token = userData[3];
var payer_account = userData[4];
var doc_type = values[0][1];
var document_number = "" + day + (i + 10) + Math.floor(Math.random() * 90 + 10);
var payment_amount = values[0][3];
var payment_naming = values[0][4];
var recipient_account = values[0][5];
var recipient_nceo = values[0][6];
var recipient_ifi = values[0][7];
var payment_destination = values[0][8];
if (!inn || !id || !token || !payer_account)
{
iRange.setBackground('#ffff80');
stateRange.setValue('Нет данных пользователя');
return;
}
if (!document_number || ! recipient_account || !recipient_nceo ||
!payment_naming || !recipient_ifi || !payment_amount || !payment_destination)
{
iRange.setBackground('#ffff80');
stateRange.setValue('Ошибка заполнения');
return;
}
var headers = {
'User-Agent' : 'GooApps',
'id' : id,
'token' : token,
'Connection' : 'close'
}
var payload = {
'document_number' : document_number,
'payer_account' : payer_account,
'recipient_account' : recipient_account,
'recipient_nceo' : recipient_nceo,
'payment_naming' : payment_naming,
'recipient_ifi' : recipient_ifi,
'payment_amount' : payment_amount,
'payment_destination' : payment_destination
};
var options = {
'method' : 'post',
'contentType' : 'application/json;charset=utf8',
'headers' : headers,
'payload' : JSON.stringify(payload),
'muteHttpExceptions' : true
};
var response = UrlFetchApp.fetch(url, options);
var respCode = response.getResponseCode();
if (respCode == 201) {
docNumRange.setValue(document_number);
iRange.setBackground('#66ff66');
stateRange.setValue('Создано');
} else {
iRange.setBackground('#ff9980');
var resp = JSON.parse(response.getContentText());
var error = resp.code;
if (!error) {
error = resp.error_code;
}
stateRange.setValue(error);
}
}
//создать все платежки
function createPayments() {
var range = sheet.getRange(2, 1, lastRow, lastCol);
range.clearFormat();
for (var i = 2; i <= lastRow; i++) {
createPayment(i);
}
}
function getUserData(name) {
var uSheet = ss.getSheetByName('Users');
var uRange = usersSheet.getRange(2, 1, uSheet.getLastRow(), uSheet.getLastColumn());
var values = uRange.getDisplayValues();
for (var i = 0; i < values.length; i++) {
if (name == values[i][0]) {
return values[i];
}
}
}
function getDay() {
var now = new Date();
var start = new Date('January 1, 2018 00:00:00 +0200');
var number = Math.round((now.getTime() - start.getTime()) / (1000 * 3600 * 24));
return number;
}
Payment control
To control payments, we will receive all statements for the last 30 days and look for the document number, status and payment amount in them. If everything matches - the payment is paid. In this case, a GET request is used.
var urlGet = 'https://acp.privatbank.ua/api/proxy/transactions';
var stDate = Utilities.formatDate(substMonth() , '+0200', 'dd-MM-yyyy');
var endDate = Utilities.formatDate(new Date(), '+0200', 'dd-MM-yyyy');
//передать номер строки в таблице
function checkPayment(i) {
var iRange = sheet.getRange(i, 1, 1, lastCol);
var values = iRange.getDisplayValues();
var stateRange = sheet.getRange(i, 10);
var state = stateRange.getValue();
if (state != 'Создано') {
return;
}
var name = values[0][0];
var userData = getUserData(name);
if (!userData) {
iRange.setBackground('#ffe066');
stateRange.setValue('Нет данных пользователя');
return;
}
var id = userData[2];
var token = userData[3];
var payer_account = userData[4];
var document_number = values[0][2];
var payment_amount = values[0][3];
if (!id || !token || !payer_account) {
iRange.setBackground('#ffe066');
stateRange.setValue('Нет данных пользователя');
return;
}
if (!document_number || !payment_amount) {
iRange.setBackground('#ffe066');
stateRange.setValue('Ошибка заполнения');
return;
}
var headers = {
'User-Agent' : 'GooApps',
'id' : id,
'token' : token,
'Connection' : 'close'
}
var options = {
'method' : 'get',
'contentType' : 'application/json;charset=utf8',
'headers' : headers,
'muteHttpExceptions' : true
};
var url = Utilities.formatString('%s?acc=%s&startDate=%s&endDate=%s', urlGet, payer_account, stDate, endDate);
var response = UrlFetchApp.fetch(url, options);
var resp = JSON.parse(response);
var statements = resp.StatementsResponse.statements;
for (var j = 0; j < statements.length; j++) {
for (var name in statements[j]) {
var bill = statements[j][name];
if (document_number == bill.BPL_NUM_DOC) {
var pay = payment_amount.toString().replace(',','.');
if (pay == bill.BPL_SUM) {
if ('r' == bill.BPL_PR_PR) {
iRange.setBackground('#ebebe0');
stateRange.setValue('Оплачено');
} else {
iRange.setBackground('#df80ff');
stateRange.setValue('Не оплачено');
}
} else {
Logger.log(pay + ' ' + bill.BPL_SUM)
iRange.setBackground('#df80ff');
stateRange.setValue('Сумма не совпадает');
}
}
}
}
}
function substMonth() {
var now = new Date();
var start = new Date(now.getTime() - 30 * (1000 * 3600 * 24));
return start;
}
Check can be put on the trigger. It remains only to observe how payments are made. PS Try how it works on the test table (link updated to an improved version). Copy it to your disk. To automate checks, you need to add a time trigger to the checkPayments function.
