This post demonstrates a simple Excel function to retrieve a customer’s balance using the API. You can easily use it in any spreadsheet where you have a customer number. While convenient, it is not ideal for very large lists of customers, as an individual API call is made for each customer. For very large lists of customers, there are other more efficient solutions such as ODBC or a bulk API call to fill a worksheet, then reference that.
First step is to configure the spreadsheet to connect to your Spire database. This is done in the CompanyInfo worksheet.
To retrieve the balance, enter a simple formula “GetCustomerBalance” that references the cell that contains the customer number.
As with all Excel formulas, you can copy it down to adjacent cells. Grab the dot in the bottom corner of the previous formula and drag it down.
Release the mouse button and the cells are filled.
You can download the Excel file here.
NOTE: The formula will not automatically refresh or refresh with F9. You must use Ctrl-Shift-Alt-F9.