使用Node.js从TallyPrime加载自定义TDL报表获取数据时出错的求助

各位大佬好，我最近在做一个需求：把TallyPrime里的库存交易数据整理成扁平化结构（每一行对应一条库存明细），然后同步到Google Sheets。我自己写了自定义TDL报表，还有一个Node.js脚本负责调用Tally API拉取数据，处理后同步到Sheets，但现在遇到了麻烦，想请大家帮忙排查问题。

目前的情况是：这个TDL在TallyPrime里直接打开能正常生成报表，所有字段（包括凭证信息、批次号）都能正确显示，但通过Node.js脚本调用Tally API拉取数据时，要么拿不到完整数据，要么XML解析后数据缺失/错误，完全没法同步到Sheets里。我已经核对了Tally地址、Google Sheets权限这些基础配置，都没问题，就是卡在数据拉取和解析这一步。

我的自定义TDL代码 #

这个TDL的作用是生成扁平化的库存交易报表，包含凭证日期、往来方、凭证类型、凭证号、成本中心、库存商品、数量、单价、金额、批次这些字段：

;; ============================================================
;; Inventory Walk (Flat) — one row per inventory line (all voucher types)
;; Report ID: RTS FlatVch
;; Columns:
;; DATE, PARTYNAME, VCHTYPE, VOUCHERNUMBER, COSTCENTRENAME,
;; STOCKITEMNAME, ACTUALQTY, RATE, AMOUNT, BATCHNAME
;; ============================================================
[Report: RTS FlatVch]
Form : RTS InvFlat Form
Filtered : Yes
Export : Yes

[Form: RTS InvFlat Form]
Parts : RTS Flat Part

[Part: RTS Flat Part]
Lines : RTS FlatRow
Repeat : RTS FlatRow : RTS FlatInv
Vertical : Yes
Scroll : Vertical

[Line: RTS FlatRow]
XMLTag : ROW
Fields : F_DATE, F_PARTY, F_VCHTYPE, F_VCHNO, F_COST, F_ITEM, F_QTY, F_RATE, F_AMOUNT, F_BATCH

; ---- voucher-level fields (now read from computed methods on the line) ----
[Field: F_DATE]
Use : Name Field
Set As : $V_Date
XMLTag : DATE

[Field: F_PARTY]
Use : Name Field
Set As : $V_Party
XMLTag : PARTYNAME

[Field: F_VCHTYPE]
Use : Name Field
Set As : $V_VchType
XMLTag : VCHTYPE

[Field: F_VCHNO]
Use : Name Field
Set As : $V_VchNo
XMLTag : VOUCHERNUMBER

[Field: F_COST]
Use : Name Field
Set As : $V_Cost
XMLTag : COSTCENTRENAME

; ---- item-level fields (current inventory entry) ----
[Field: F_ITEM]
Use : Name Field
Set As : $StockItemName
XMLTag : STOCKITEMNAME

[Field: F_QTY]
Use : Name Field
Set As : $ActualQty
XMLTag : ACTUALQTY

[Field: F_RATE]
Use : Name Field
Set As : $Rate
XMLTag : RATE

[Field: F_AMOUNT]
Use : Name Field
Set As : $Amount
XMLTag : AMOUNT

[Field: F_BATCH]
Use : Name Field
Set As : $BatchAllocations[1].BatchName
XMLTag : BATCHNAME

; -------- all vouchers (no filter) --------
[Collection: RTS AllVouchers]
Type : Voucher
Fetch : Date, PartyLedgerName, VoucherTypeName, VoucherNumber, CostCentreName, AllInventoryEntries.*

; -------- flat inventory-line collection --------
[Collection: RTS FlatInv]
Source Collection : RTS AllVouchers
Walk : All Inventory Entries
Belongs To : RTS AllVouchers
Fetch : StockItemName, ActualQty, Rate, Amount, BatchAllocations.*
; Compute voucher-level methods onto each inventory line
Compute : V_Date : $..Date
Compute : V_Party : $..PartyLedgerName
Compute : V_VchType : $..VoucherTypeName
Compute : V_VchNo : $..VoucherNumber
Compute : V_Cost : $..CostCentreName

我的Node.js脚本代码 #

这个脚本负责构建Tally API请求、拉取报表数据、解析XML，最后同步到Google Sheets（我补全了原本截断的请求信封构建函数，否则脚本无法正常运行）：

#!/usr/bin/env node
"use strict";

/**
 * AllVoucher.js — Smart Append Tally -> Google Sheets
 * Report: RTS InvFlat
 * Sheet: AllVoucher (adds header if missing, appends data only, formats new rows)
 *
 * npm i axios fast-xml-parser googleapis
 * credentials.json must have Editor access on the sheet
 */

const axios = require("axios");
const { XMLParser } = require("fast-xml-parser");
const { google } = require("googleapis");
const fs = require("fs");

/* ---------- CONFIG (edit if needed) ---------- */
const TALLY_URL = "myIP";
const COMPANY = "Company";
const REPORT_ID = "RTS FlatVch";
const DEFAULT_FROM_ISO = "2025-04-01"; // used only if sheet empty & --from not provided
const DEFAULT_TO_ISO = new Intl.DateTimeFormat('en-CA', { timeZone: 'Asia/Kolkata', year: 'numeric', month: '2-digit', day: '2-digit' }).format(new Date());
const SPREADSHEET_ID = "SheetID";
const TAB_NAME = "AllVoucher";
const CREDENTIALS = "credentials.json";
const BATCH_ROWS = 20000; // rows per append call
/* -------------------------------------------- */

const HEADERS = [
 "DATE","PARTYNAME","VCHTYPE","VOUCHERNUMBER","COSTCENTRENAME",
 "STOCKITEMNAME","ACTUALQTY","RATE","AMOUNT","BATCHNAME"
];

// ---------- small helpers ----------
function getArg(name, def = "") {
 const hit = process.argv.find(a => a.startsWith(`--${name}=`));
 return hit ? hit.split("=").slice(1).join("=") : def;
}

function ymdNoDashes(iso) {
 const [y, m, d] = iso.split("-");
 return `${y}${m}${d}`;
}

function addDaysISO(iso, days) {
 const dt = new Date(iso + "T00:00:00Z");
 dt.setUTCDate(dt.getUTCDate() + days);
 const y = dt.getUTCFullYear();
 const m = String(dt.getUTCMonth() + 1).padStart(2, "0");
 const d = String(dt.getUTCDate()).padStart(2, "0");
 return `${y}-${m}-${d}`;
}

function gsSerialToISO(n) {
 // Google serial dates are days since 1899-12-30
 const epoch = Date.UTC(1899, 11, 30);
 const dt = new Date(epoch + n * 86400 * 1000);
 const y = dt.getUTCFullYear();
 const m = String(dt.getUTCMonth()+1).padStart(2,"0");
 const d = String(dt.getUTCDate()).padStart(2,"0");
 return `${y}-${m}-${d}`;
}

// ---------- XML & parsing ----------
function buildEnvelope(company, fromIso, toIso, reportId) {
 return `
<ENVELOPE>
 <HEADER>
  <TALLYREQUEST>Export Data</TALLYREQUEST>
 </HEADER>
 <BODY>
  <EXPORTDATA>
   <REQUESTDESC>
    <REPORTNAME>${reportId}</REPORTNAME>
    <STATICVARIABLES>
     <SVCURRENTCOMPANY>${company}</SVCURRENTCOMPANY>
     <SVEXPORTFORMAT>XML</SVEXPORTFORMAT>
     <SVFROMDATE>${ymdNoDashes(fromIso)}</SVFROMDATE>
     <SVTODATE>${ymdNoDashes(toIso)}</SVTODATE>
    </STATICVARIABLES>
   </REQUESTDESC>
  </EXPORTDATA>
 </BODY>
</ENVELOPE>
 `.trim();
}

async function fetchTallyData(url, envelope) {
 try {
  const response = await axios.post(url, envelope, {
   headers: {
    "Content-Type": "text/xml",
   },
  });
  return response.data;
 } catch (err) {
  console.error("Error fetching from Tally:", err.message);
  throw err;
 }
}

function parseTallyXML(xmlData) {
 const parser = new XMLParser({
  ignoreAttributes: false,
  parseAttributeValue: true,
 });
 const parsed = parser.parse(xmlData);
 // Extract rows from the exported report
 const rows = parsed?.ENVELOPE?.BODY?.EXPORTRESPONSE?.REPORT?.ROW || [];
 // Convert each row to a flat object matching our headers
 return rows.map(row => {
  const obj = {};
  HEADERS.forEach(header => {
   obj[header] = row[header] || "";
  });
  return obj;
 });
}

// ---------- Google Sheets integration ----------
async function getSheetsAuth(credentialsPath) {
 const credentials = JSON.parse(fs.readFileSync(credentialsPath));
 const { client_email, private_key } = credentials;
 const auth = new google.auth.JWT(
  client_email,
  null,
  private_key,
  ["https://www.googleapis.com/auth/spreadsheets"]
 );
 await auth.authorize();
 return auth;
}

async function getLastSyncDate(auth, spreadsheetId, tabName) {
 const sheets = google.sheets({ version: "v4", auth });
 const res = await sheets.spreadsheets.values.get({
  spreadsheetId,
  range: `${tabName}!A:A`,
 });
 const values = res.data.values || [];
 if (values.length === 0) return null;
 // Skip header row
 const dateCells = values.slice(1).filter(cell => cell[0]);
 if (dateCells.length === 0) return null;
 // Get last date (Google Sheets stores dates as serial numbers)
 const lastSerial = dateCells[dateCells.length - 1][0];
 return typeof lastSerial === "number" ? gsSerialToISO(lastSerial) : lastSerial;
}

async function appendToSheets(auth, spreadsheetId, tabName, rows) {
 const sheets = google.sheets({ version: "v4", auth });
 // Split into batches to avoid size limits
 for (let i = 0; i < rows.length; i += BATCH_ROWS) {
  const batch = rows.slice(i, i + BATCH_ROWS);
  // Convert rows to array of arrays
  const values = batch.map(row => HEADERS.map(header => row[header]));
  await sheets.spreadsheets.values.append({
   spreadsheetId,
   range: `${tabName}!A1`,
   valueInputOption: "USER_ENTERED",
   resource: { values },
  });
  console.log(`Appended ${batch.length} rows to sheet`);
 }
}

async function ensureHeaders(auth, spreadsheetId, tabName) {
 const sheets = google.sheets({ version: "v4", auth });
 const res = await sheets.spreadsheets.values.get({
  spreadsheetId,
  range: `${tabName}!A1:J1`,
 });
 const hasHeaders = res.data.values && res.data.values.length > 0;
 if (!hasHeaders) {
  await sheets.spreadsheets.values.update({
   spreadsheetId,
   range: `${tabName}!A1`,
   valueInputOption: "USER_ENTERED",
   resource: { values: [HEADERS] },
  });
  console.log("Added missing headers to sheet");
 }
}

// ---------- Main workflow ----------
async function main() {
 try {
  // Get date range (use last sync date if available)
  const auth = await getSheetsAuth(CREDENTIALS);
  const lastSyncDate = await getLastSyncDate(auth, SPREADSHEET_ID, TAB_NAME);
  const fromIso = getArg("from", lastSyncDate || DEFAULT_FROM_ISO);
  const toIso = getArg("to", DEFAULT_TO_ISO);

  console.log(`Fetching data from Tally: ${fromIso} to ${toIso}`);

  // Build request envelope and fetch data
  const envelope = buildEnvelope(COMPANY, fromIso, toIso, REPORT_ID);
  const xmlData = await fetchTallyData(TALLY_URL, envelope);
  const dataRows = parseTallyXML(xmlData);

  if (dataRows.length === 0) {
   console.log("No new data found to sync");
   return;
  }

  console.log(`Parsed ${dataRows.length} rows from Tally`);

  // Prepare and sync to Sheets
  await ensureHeaders(auth, SPREADSHEET_ID, TAB_NAME);
  await appendToSheets(auth, SPREADSHEET_ID, TAB_NAME, dataRows);

  console.log("Sync completed successfully!");
 } catch (err) {
  console.error("Sync failed:", err);
  process.exit(1);
 }
}

main();

我遇到的具体问题 #

1. 脚本执行时，有时Tally返回的XML里没有预期的ROW节点，解析后得到空数据集
2. 有时能拉取到数据，但部分字段缺失/错误：比如日期字段为空，批次号无法正确获取
3. 已经确认TDL在Tally客户端显示完全正常，但API拉取就出问题

有没有大佬能帮我看看TDL或者脚本里哪里写漏了/写错了？

内容来源于stack exchange