Invoke web service and store resulted response in excel file

3 Likes

Thanks @Suniel, I’m going to give it try.
Regards,
burque505

1 Like

Folder.GetSpecialFolder SpecialFolder:Folder.SpecialFolder.DesktopDirectory SpecialFolderPath=> SpecialFolderPath
#Invoke web service
Web.InvokeWebService URL:“http://api.geonames.org/wikipediaSearchJSON?formatted=true&q=bangalore&maxRows=10&username=sk12te&style=full
Method:Web.Method.Get
Accept:‘application/json’
contentType:‘application/json’
CustomHeaders:’’
RequestBody:’’
ConnectionTimeout:30
FollowRedirection:True
ClearCookies:False
FailOnErrorStatus:False
EncodeRequestBody:True
UserAgent:‘Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.21) Gecko/20100312 Firefox/3.6’
Encoding:’’
ResponseHeaders=> ResponseHeaders
Response=> WebServiceResponse
#Convert Json response to custom object
Variables.ConvertJsonToCustomObject
Json: WebServiceResponse CustomObject=> JsonAsCustomObject

Excel.Launch Visible:True LoadAddInsAndMacros:False Instance=> ExcelInstance
Excel.WriteCell Instance: ExcelInstance Value:‘Summary’ Column: 1 Row: 1
Excel.WriteCell Instance: ExcelInstance Value:‘Elevation’ Column: 2 Row: 1
Excel.WriteCell Instance: ExcelInstance Value:‘Longitude’ Column: 3 Row: 1
Excel.WriteCell Instance: ExcelInstance Value:‘Rank’ Column: 4 Row: 1
Excel.WriteCell Instance: ExcelInstance Value:‘Language’ Column: 5 Row: 1
Excel.WriteCell Instance: ExcelInstance Value:‘Title’ Column: 6 Row: 1
Excel.WriteCell Instance: ExcelInstance Value:‘Latitude’ Column: 7 Row: 1
Excel.WriteCell Instance: ExcelInstance Value:‘WikipediaUrl’ Column: 8 Row: 1

#Write response output in excel file and save it to excel
loop i from 0 to 9
Excel.WriteCell Instance: ExcelInstance Value:JsonAsCustomObject.geonames[i].summary Column: 1 Row: i+2
Excel.WriteCell Instance: ExcelInstance Value:JsonAsCustomObject.geonames[i].elevation Column: 2 Row: i+2
Excel.WriteCell Instance: ExcelInstance Value:JsonAsCustomObject.geonames[i].lng Column: 3 Row: i+2
Excel.WriteCell Instance: ExcelInstance Value:JsonAsCustomObject.geonames[i].rank Column: 4 Row: i+2
Excel.WriteCell Instance: ExcelInstance Value:JsonAsCustomObject.geonames[i].lang Column: 5 Row: i+2
Excel.WriteCell Instance: ExcelInstance Value:JsonAsCustomObject.geonames[i].title Column: 6 Row: i+2
Excel.WriteCell Instance: ExcelInstance Value:JsonAsCustomObject.geonames[i].lat Column: 7 Row: i+2
Excel.WriteCell Instance: ExcelInstance Value:JsonAsCustomObject.geonames[i].wikipediaUrl Column: 8 Row: i+2
end

Excel.CloseAndSaveAs Instance: ExcelInstance DocumentFormat:Excel.ExcelFormat.FromExtension DocumentPath: ‘SpecialFolderPath\WebServiceResponseinExcel’

2 Likes

@burque505 You are welcome! and Thanks!

@Suniel, I just tried it as below, works great. I needed to change “smart quotes” to plain old quotes, and I did change the last line as shown (otherwise it would not save). I also reformatted it per my usual practices (more or less), but that of course doesn’t affect the running of it. :grinning:
Thanks very much, very instructive!

Best regards,
burque505

Folder.GetSpecialFolder SpecialFolder: Folder.SpecialFolder.DesktopDirectory SpecialFolderPath=> SpecialFolderPath
#Invoke web service
Web.InvokeWebService    URL:"http://api.geonames.org/wikipediaSearchJSON?formatted=true&q=bangalore&maxRows=10&username=sk12te&style=full 1" \
                        Method: Web.Method.Get \
                        Accept: 'application/json' \
                        contentType: 'application/json' \
                        CustomHeaders: '' \
                        RequestBody:'' \
                        ConnectionTimeout:30 \
                        FollowRedirection: True \
                        ClearCookies: False \
                        FailOnErrorStatus: False \
                        EncodeRequestBody: True \
                        UserAgent:'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.21) Gecko/20100312 Firefox/3.6' \
                        Encoding:'' \
                        ResponseHeaders=> ResponseHeaders \
                        Response=> WebServiceResponse
#Convert Json response to custom object
Variables.ConvertJsonToCustomObject \
                        Json: WebServiceResponse CustomObject=> JsonAsCustomObject

Excel.Launch            Visible:True \
                        LoadAddInsAndMacros:False \
                        Instance=> ExcelInstance
Excel.WriteCell         Instance: ExcelInstance \
                        Value:'Summary' \
                        Column: 1 \
                        Row: 1
Excel.WriteCell         Instance: ExcelInstance \
                        Value:'Elevation' \
                        Column: 2 \
                        Row: 1
Excel.WriteCell         Instance: ExcelInstance \
                        Value:'Longitude' \
                        Column: 3 \
                        Row: 1
Excel.WriteCell         Instance: ExcelInstance \
                        Value:'Rank' \
                        Column: 4 \
                        Row: 1
Excel.WriteCell         Instance: ExcelInstance \
                        Value:'Language' \
                        Column: 5 \
                        Row: 1
Excel.WriteCell         Instance: ExcelInstance \
                        Value:'Title' \
                        Column: 6 \
                        Row: 1
Excel.WriteCell         Instance: ExcelInstance \
                        Value:'Latitude' \
                        Column: 7 \
                        Row: 1
Excel.WriteCell         Instance: ExcelInstance \
                        Value:'WikipediaUrl' \
                        Column: 8 \
                        Row: 1

#Write response output in excel file and save it to excel
loop i from 0 to 9
Excel.WriteCell         Instance: ExcelInstance \
                        Value:JsonAsCustomObject.geonames[i].summary \
                        Column: 1 \
                        Row: i+2
Excel.WriteCell         Instance: ExcelInstance \
                        Value:JsonAsCustomObject.geonames[i].elevation \
                        Column: 2 \
                        Row: i+2
Excel.WriteCell         Instance: ExcelInstance \
                        Value:JsonAsCustomObject.geonames[i].lng \
                        Column: 3 \
                        Row: i+2
Excel.WriteCell         Instance: ExcelInstance \
                        Value:JsonAsCustomObject.geonames[i].rank \
                        Column: 4 \
                        Row: i+2
Excel.WriteCell         Instance: ExcelInstance \
                        Value:JsonAsCustomObject.geonames[i].lang \
                        Column: 5 \
                        Row: i+2
Excel.WriteCell         Instance: ExcelInstance \
                        Value:JsonAsCustomObject.geonames[i].title \
                        Column: 6 \
                        Row: i+2
Excel.WriteCell         Instance: ExcelInstance \
                        Value:JsonAsCustomObject.geonames[i].lat \
                        Column: 7 \
                        Row: i+2
Excel.WriteCell         Instance: ExcelInstance \
                        Value:JsonAsCustomObject.geonames[i].wikipediaUrl Column: 8 Row: i+2
end

Excel.CloseAndSaveAs    Instance: ExcelInstance \
                        DocumentFormat:Excel.ExcelFormat.FromExtension \
                        DocumentPath: SpecialFolderPath + "\WebServiceResponseinExcel.xlsx"
2 Likes

I am trying to invoke web service but getting error…

key:fGPgcaeBzZrUG6Zvtkwm7H0i0139Nq4k
CURL:

curl -X GET “https://api-eu.dhl.com/track/shipments?trackingNumber=5271578126&service=express&language=en” -H “accept: application/json” -H “DHL-API-Key: dhl-try-it-out-key”

Req Url:
https://api-eu.dhl.com/track/shipments?trackingNumber=5271578126&service=express&language=en

Need Help…

1 Like

Hello Suniel,
This can happen when you are not authorized to use a particular API.
I tried to access both url and it returns
“{“status”:401,“title”:“Unauthorized”,“detail”:“Unauthorized for given resource.”}”

2 Likes

I’m getting same error.
when i try this on DHL Manually ,it response Successfully.

link:
https://developer.dhl.com/api-reference/shipment-tracking#reference-docs-section�fault

its works without key…

1 Like

@nldavila yes, we are not able to run it from url also.

@rushi.dalvi It seems it requires access, Even if you try to execute this on Postman also it is giving same error. could you please refer DHL api doccuemnt and try accordingly.

I think you need to pass originCountryCode and requestCountryCode properly.

3 Likes

@Suniel
here i’m adding only tracking no …

Response as following…


how i can implement this in robin…