Connecting to Excel via Spire API

[Spire Systems]

We’ve put together this quick tutorial to show you how to connect Excel to Spire using our API.

The advantage of using the API is that it only allows the user to access data that they have permission to see in Spire. For example, if the user does not have access to the GL, then they will not be able to report on the GL data.

• This topic was modified 1 year, 3 months ago by Spire Systems.
• This topic was modified 1 year, 3 months ago by [email protected].

[Luca Del Mastro]

Apologies it is now running for me.

 

Thanks.

 

Will the video be made available? It comes up as “Video unavailable.  This video is private.”  on the embedded YouTube video.

• This reply was modified 1 year, 3 months ago by Luca Del Mastro. Reason: Video was updated

[Ben van Gogh]

This is great, is there a way to alter both the “limit” and the “start” point when pulling the data?

[Spire Systems]

Here is an example of using the limit and a filter that may work

 

https://cold-bird-8338.spirelan.com:10880/api/v2/companies/Sample/sales/invoice_items/?limit=10000&filter={%22invoiceDate%22:{%22$gt%22:%222018-12-30%22}}

[Luca Del Mastro]

Tried running this with ?limit=0 and it never worked, I kept getting bad request error. Is there a limitation inside of Excel as I was never able to get my full amount of records in but I could type in ?limit=1000 and get 1000 results. Just hoping that I would be able to see all of it like in the example.

[Spire Systems]

https://cold-bird-8338.spirelan.com:10880/api/v2/companies/Sample/sales/invoice_items/?limit=0

does return all records for me. Try it in your browser to see if there are errors.

 

• This reply was modified 1 year, 3 months ago by Spire Systems.
• This reply was modified 1 year, 3 months ago by Spire Systems.

[Luca Del Mastro]

I tried to do it through the browser and the error that is returned is the following:

 

HTTP 400 BAD REQUEST
Content-Type: application/json

{
“type”:”error”,
“message”:”Limit too high, reduce limit and retry request”
}

 

I’m curious if there is something that I need to change to allow access to all of the data in a given module.

 

Thanks for your help.

[Spire Systems]

This works for me on Chrome and MS Edge on a PC, however I tried with an IPhone and got a 400 error saying limit too high. What are you using?

https://cold-bird-8338.spirelan.com:10880/api/v2/companies/Sample/sales/invoice_items/?limit=0

Make sure there is no trailing /

This video shows that it does work on my computer. https://youtu.be/uTRb87FncdE

 

 

[Luca Del Mastro]

Thank you for the reply. I am using it on Chrome browser on a Windows 10 PC. I have done it the same way on each module and keep getting the limit too high error. The only one that does not give me that is sales orders which has 7,517 current orders.

This led me to believe that there is some sort of hard limit programmed in. Every other module is too large and it looks like there is a hard limit set up of possibly 10,000 records only. I was ok when typing in ?limit=10000 but as soon as I switched it to ?limit=10001 it returned with limit too high.

This tool is  very powerful and I’m assuming the limit is there for a reason but it does hinder our ability to utilize it as I am not even able to extract the sales order details list. Some other modules are also potentially very powerful through this API link as well and I was wondering if there was any way of removing the hard limit?

If not, is  a consistent method to filter only to see the most recent records? I noticed in the reply above you added a filter code for invoice date, but is that format specific to getting the invoice date?

Thank you for all the help.

[Jessi Bhattal]

 

Is there away to have to retrieve more the 10000 recorders. When I set the limit to 10000 or zero, I get a limit to high error.

I have 50 000 plus records and when I set the limit great than 10000 I get an error.

Has there been fix or work around for this issue?

[Author]

Posts