Connecting to simPRO data from Excel

[op2352]

Hello. Apologies for this pretty basic question - I'm new to this ...
Could somebody please tell me how I can pull simPRO data into Excel? I have set up an API, which gives me a "redirect URI" and an access token, but I don't know where in Excel to use these pieces of information. I'm assuming I start off with the Get Data option and below that perhaps the From Web option (which requires a URL). But that doesn't seem to work for me - I could be using the wrong URL or the wrong option altogether. Any advice would be much appreciated. simPRO Support suggested I tried this forum.
Thanks,
Gary

[Sindre]

Hi op2352.

We can't help you with importing data into excel. If you want to test data retrieval through our API i usually recommend using Postman as an easy platform to test your credentials and get some examples of your data through the API.

If you are successful getting data there you know your credentials are working and you can set it up on other platforms.

Regards

[op2352]

Hello Sindre,

Many thanks for your response. Apologies for not replying sooner - I gave up checking after a week of silence, but simPRO Support kindly alerted me to your response. I've just noticed there's a "Notify me when I reply is posted" checkbox that I failed to tick on my original message.

I've not heard of Postman, but that might be just what I need. I will check it out.

Thanks again,
Gary

[dmpchch]

Hello op2352,

Using excel can be a bit of a pain to be fair. Not knowing where in excel is going to make it even harder.
You need to activate the developer tab and enter your code via VBA (visual basic for applications).

It is not however as simple as adding the request as seen in the examples.

Below is for excel....it is a very simple piece of code to get quotes details for multiple quotes where you have an existing list of quotes

Code: Select all

Sub GetDetail()
Application.ScreenUpdating = False


Dim httpObject As Object
Set httpObject = CreateObject("MSXML2.XMLHTTP")
Dim QT
Dim CMP
Dim RW
Dim valueOne As String
Dim positionofSubstring As Integer
Dim JSON As Object, i As Integer

RW = 2
Do Until ActiveSheet.Cells(RW, 2).Value = ""
QT = ActiveSheet.Cells(RW, 2).Value
CMP = ActiveSheet.Cells(RW, 5).Value

sURL = "https://xxxxxx.simprosuite.com/api/v1.0/companies/" & CMP & "/quotes/" & QT
sRequest = sURL
httpObject.Open "GET", sRequest, False
httpObject.setRequestHeader "Authorization", "Bearer **********goes here*****************"
httpObject.setRequestHeader "Content-Type", "application/json"
httpObject.Send
sgetresult = httpObject.responseText

Set JSON = ParseJson(sgetresult)
i = 2
'For Each Item In JSON
Sheets(1).Cells(RW, 6).Value = JSON("Customer")("CompanyName")
Sheets(1).Cells(RW, 7).Value = JSON("Site")("Name")
Sheets(1).Cells(RW, 8).Value = JSON("Salesperson")("Name")
Sheets(1).Cells(RW, 9).Value = JSON("DueDate")
Sheets(1).Cells(RW, 10).Value = JSON("ProjectManager")("Name")
Sheets(1).Cells(RW, 11).Value = JSON("Total")("ExTax")
Sheets(1).Cells(RW, 12).Value = JSON("Totals")("GrossProfitLoss")("Estimate")
Sheets(1).Cells(RW, 13).Value = JSON("Totals")("NettMargin")("Estimate")
Sheets(1).Cells(RW, 14).Value = JSON("Totals")("ResourcesCost")("LaborHours")("Estimate")
Sheets(1).Cells(RW, 15).Value = JSON("Status")("Name")

'i = i + 1
'Next
RW = RW + 1
Loop
Set sgetresult = Nothing
Set httpObject = Nothing
Application.ScreenUpdating = True

End Sub

I would 100% recommend trying to get what you need from the current reporting suite or BI reporting.

You may find you have someone on staff with excel VBA experience or better yet Python.

[op2352]

Hi dmpchch,

Many thanks for taking the trouble to pen such a detailed and useful reply. (I stumbled across it by chance, as the "Notify me when I reply is posted" checkbox didn't work for me.)

Unfortunately, I have very little experience of VBA and your code snippet was complex enough for me to decide it's not something I want to try to learn! And I have no experience whatsoever of Python.

Your recommendation sounds sensible. I've been asked to write a fairly tricky report. I can't write it fully in Metabase BI Reporting because I need to query a table that isn't present in the set of tables that are available to the tool; and the simPRO reporting suite provides only part of the picture too. But by combining a Metabase report I wrote and an existing simPRO report in Excel and cross-referencing via XLOOKUPs, I was able to achieve the required report. It's a rather inelegant solution, but it was quicker than learning VBA or Python!

Cheers,
Gary

[Nadya]

Hi Gary,
Have you had any success with your excel-Simpro exercise? I have the same problem and would appreciate if you could share what helped you

[op2352]

Hi Gary,
Have you had any success with your excel-Simpro exercise? I have the same problem and would appreciate if you could share what helped you

Hi Nadya,

So sorry for the slow response. I don't seem to be set up to receive notifications (something I'll need to look into), so I had no idea you had tried to contact me until I went back into the Forum today. As it happens, my response to you isn't very helpful anyway: Unfortunately I have had no success getting simPRO data into Excel (although I must admit I haven't tried massively hard). The above conversation with "dmpchch" sums up as far as I got with Excel. (In short, I gave up!)

I'm currently looking into trying to get at the data from Power BI instead. I'm finding it rather frustrating that simPRO provides an API allegedly to provide an interface to the database, but there doesn't seem to be much documentation on how to use it. The simPRO Support desk consultants seem to know nothing about it and they just direct their customers to this user forum instead. So I'll be posting a question on that shortly!

Regards,
Gary