Google Sheet is becoming popular choice of spreadsheet solution. This article demonstrates workaround of import of Tally reports directly into Google Sheet
- Tally.ERP 9 / Tally Prime
- Google Personal / Workspace account
- Windows 10
- Microsoft DotNet Framework 4.5 or higher
- Google Chrome / Microsoft Edge / Mozilla Firefox
Tally has in-built XML Server feature to push-pull data to-from Tally scripted in TDL (Tally Developer Language) in XML format. This setting must be enabled
-
Gateway of Tally > Configure (F12) > Advance Configuration
-
Change (or ensure) to set Tally.ERP 9 is acting as to Both and Port is 9000
- Restart Tally when prompted
-
Help (F1) > Settings > Connectivity
-
Select Client/Server configuration
-
Change (or ensure) TallyPrime is acting as to Both and Port is 9000
In popular browsers access to internal (or localhost) website running on that PC is blocked due to security consideration.
So, this utility unblocks browser access with Tally to exchage XML messages of fetching data.
This utility can be download from below link
Download Tally Localhost Connector utility
As part of transparency initative, we have kept source code of this utility in public domain (on GitHub) developed in C# .NET language. Users can verify code, re-compile utility, study it from security angle, or refer it for educational purpose
Visit GitHub Soure Repository
Google Apps Script project for this needs to be setup (Tools > Script Editor), add 3 files into it
- Code.gs
- Dialog.html
- TDL.html
function onOpen() {
SpreadsheetApp.getUi().createMenu('Tally').addItem('Fetch Report', 'fetchReport').addToUi();
}
function insertReport(arr) {
let headerLine = 'Name,Parent,Primary Group,Opening,Debit,Credit,Closing,Description';
let lstHdr = headerLine.split(/,/g);
let sh = SpreadsheetApp.getActiveSheet();
sh.getRange('A:H').clearContent();
let r = arr.length || 0;
let c = (arr.length && arr[0].length) ? arr[0].length : 0;
if (r && c) sh.getRange(2, 1, r, c).setValues(arr);
sh.getRange(1, 1, 1, lstHdr.length).setValues([lstHdr]);
}
function fetchReport() {
let tdl = HtmlService.createHtmlOutputFromFile('TDL').getContent();
let html = HtmlService.createTemplateFromFile('Dialog');
html.tdl = tdl;
SpreadsheetApp.getUi().showModalDialog(html.evaluate(), 'Data Connector');
}
function onOpen() {
SpreadsheetApp.getUi().createMenu('Tally').addItem('Fetch Report', 'fetchReport').addToUi();
}
function insertReport(arr) {
let headerLine = 'Name,Parent,Primary Group,Opening,Debit,Credit,Closing,Description';
let lstHdr = headerLine.split(/,/g);
let sh = SpreadsheetApp.getActiveSheet();
sh.getRange('A:H').clearContent();
let r = arr.length || 0;
let c = (arr.length && arr[0].length) ? arr[0].length : 0;
if (r && c) sh.getRange(2, 1, r, c).setValues(arr);
sh.getRange(1, 1, 1, lstHdr.length).setValues([lstHdr]);
}
function fetchReport() {
let tdl = HtmlService.createHtmlOutputFromFile('TDL').getContent();
let html = HtmlService.createTemplateFromFile('Dialog');
html.tdl = tdl;
SpreadsheetApp.getUi().showModalDialog(html.evaluate(), 'Data Connector');
}
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script>
$(document).ready(function () {
$.ajax({
url: 'http://localhost:9001',
method: 'POST',
data: `<?= tdl ?>`
}).done((resp) => {
$('#status').html('Processing report received from Tally Connector');
resp = resp.replace(/\xF5/g, '');
let retval = [];
let dRows = resp.split(/,\r\n/g);
dRows.pop();
console.log(dRows);
for (let r = 0; r < dRows.length; r++) {
let rContent = dRows[r];
let dCols = rContent.match(/(".*?"|[^",\s]+)(?=\s*,|\s*$)/g);
let items = [];
for (let c = 0; c < dCols.length; c++) {
let cContent = dCols[c];
cContent = cContent.substring(1, cContent.length - 1);
items.push(cContent);
}
retval.push(items);
}
console.log(retval);
google.script.run.withSuccessHandler(() => google.script.host.close()).insertReport(retval);
}).fail(() => $('#status').html('Unable to communicate with Tally Connector'));
});
</script>
</head>
<body>
<div id="status">Establishing connectivity with Tally Connector</div>
</body>
</html>
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>
<script>
$(document).ready(function () {
$.ajax({
url: 'http://localhost:9001',
method: 'POST',
data: `<?= tdl ?>`
}).done((resp) => {
$('#status').html('Processing report received from Tally Connector');
resp = resp.replace(/\xF5/g, '');
let retval = [];
let dRows = resp.split(/,\r\n/g);
dRows.pop();
console.log(dRows);
for (let r = 0; r < dRows.length; r++) {
let rContent = dRows[r];
let dCols = rContent.match(/(".*?"|[^",\s]+)(?=\s*,|\s*$)/g);
let items = [];
for (let c = 0; c < dCols.length; c++) {
let cContent = dCols[c];
cContent = cContent.substring(1, cContent.length - 1);
items.push(cContent);
}
retval.push(items);
}
console.log(retval);
google.script.run.withSuccessHandler(() => google.script.host.close()).insertReport(retval);
}).fail(() => $('#status').html('Unable to communicate with Tally Connector'));
});
</script>
</head>
<body>
<div id="status">Establishing connectivity with Tally Connector</div>
</body>
</html>
TDL.html file contains the TDL report (in XML format), which is sent to Tally for fetching data.
Few reports are available on open-source Power Query project maintained on GitHub.
Just navigate to folder supporting_XML which contains TDL files for fetching data. Few parameters need to be changed after copy-paste of XML into TDL.html file
Visit Excelkida Power Query project