Skip to content
+1-888-319-3663

EONE BLOG

Tech Thursday : Save an Excel File From a REST Service Response


Since SmartConnect 2015 we’ve been able to connect to a variety of REST services using the REST connector.  This requires the service to provide a JSON or XML payload.  I recently came across a service that doesn’t use JSON or XML.  The GoToWebinar Admin API will only give back an Excel file, so I could not use the built-in REST connector in SmartConnect.  The REST service essentially gives you the same file as if you logged into the GoToWebinar interface and manually exported the data you need.  Using the REST service to automatically go pull down the latest Excel file will eliminate the need to manually go create an excel file when you need to run a map.

Since the current REST connector in SmartConnect will not handle this response we will have to write a pre-map task script to call the web service and save the Excel file.  The following vb.net script will connect to the GoToWebinar Admin API, but you could modify it to connect to any web service that provides an Excel file.  This web service only requires the Authorization parameter, but other services may require additional parameters to take into consideration.

Dim header As New System.Net.WebHeaderCollection() ‘create header

header.Add(“Authorization: Your_Authorization_Token”) ‘add token to header

Dim sUrl As String = “https://api.getgo.com/admin/rest/v1/accounts/Your_Account_Number/users/reports/webinar/recording/registrant?fromDate=Your_epochfromdate&toDate=Your_epochtodate ‘endpoint to call

Dim request As System.Net.HttpWebRequest = TryCast(System.Net.WebRequest.Create(New Uri(sUrl)), System.Net.HttpWebRequest)

request.Headers = header

request.Accept = “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”

request.Method = “GET”

Dim response As System.Net.WebResponse = request.GetResponse()

Dim o As Stream = response.GetResponseStream()

Using fs = File.Create(“c:\Your_Save_Location\Your_Excel_Filename.xlsx”) ‘create excel output

  o.CopyTo(fs)

End Using


You will need to provide your Authorization Token, Account Number, from and to dates (in Epoch milliseconds), and export path/name for the script to work correctly with your data.  When the pre-map task script is properly setup it will look similar to this:

With the script in place, you can now continue the map setup and will be able to choose this Excel file as the source for the map.  Note: You will need to have a placeholder Excel file to choose as a source file if this will be the first time setting up the map since the file from the pre-map task will not be available until the map is actually run.

This should get you started with a web service that returns an Excel file response.  In this particular example, I would recommend using a couple global variables for the from and to dates that are calculated before this pre-map task.   This will prevent the need to change the dates in the script for future use and make it more dynamic.

Leave a Comment





RECENT POSTS


2021 Q4 Promotions
Connect with us at Directions EMEA 2021 in Milan!
eOne at Summit North America 2021
Employee Spotlight: Megan Tripp, Business Development Representative
eOne is now compatible with Microsoft Dynamics GP October 2021 Release

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships partners Popdock release Reporting SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.