Payment Management Privat24 from Google-tables

Working with the Internet banking of a large number of enterprises can be quite a routine task. To create payments and control them, you need to switch between accounts each time. And if there are more than 50 such organizations, you can easily get lost and make mistakes, not to mention porting time. Let's see how to make the life of an accountant easier by the example of PrivatBank's API for business.

To work with the account, you must create an AutoClient and get the data for authorization . This data, as well as billing information will be stored in the Google-table.




')
Tools / Script Editor. Here we will write the backend on Google Apps Script , which is based on Java Script. Our task is to create an http request, specifying the necessary details.

The script will respond to changes in the last column. To do this, add a trigger when changing (onEdit) - perform the function onEditt (). 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(); } } 

Making payments

To create payments, use a POST request. The process generates a document number, which will come in handy 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 is the same, 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; } 

The check can be put on the trigger. It remains only to observe how payments are made.



PS Try how it works on a test chart . Copy it to your disk and add an onEdit trigger to execute the onEditt () function.