• Become a Power user Intro to AutoHotkey Intermediate AutoHotkey Intermediate Objects GUIs are Easy w/AutoHotkey Painlessly switch from V1 to V2

Example Webservice / API call- Extract JSON & Binary data from Smartsheets

In the webinar on APIs we discuss various types of data extraction but I don’t think I mentioned that you can also download binary data.  In this example I demonstrate how to connect to the SmartSheets API and extract both JSON (I also demoed this FormatJson function by tmplinshi which made the JSON much easier to read) and binary data (in this case a csv file).  Here is the AutoHotkey script I used during the below video:

IniRead, API_Token,Auth.ini,API, Token ;read current token

;~  EndPoint:="https://api.smartsheet.com/2.0/sheets" ;get list of sheets (needs to be GET)
Sheet_ID:="7323737868527492"  ;https://app.smartsheet.com/b/home
Endpoint:="https://api.smartsheet.com/2.0/sheets/" Sheet_ID ;extract sheet data (needs to be GET)

;***********API Call*******************
HTTP := ComObjCreate("WinHttp.WinHttpRequest.5.1")
HTTP.Open("GET", Endpoint . QueryString)
HTTP.SetRequestHeader("Authorization", "Bearer " API_Token)
HTTP.SetRequestHeader("Accept","text/csv")
HTTP.Send()
;~  MsgBox % Response:=HTTP.Status
;~  MsgBox % Response:=HTTP.responseBody
;~  Response:=HTTP.ResponseText
;~  SciTE_Output(Response) ;Text,Clear=1,LineBreak=1,Exit=0
;~  SciTE_Output(FormatJson(Response)) ;Text,Clear=1,LineBreak=1,Exit=0
;***********Save binary file- probably should adapt above to the MSXML2.XMLHTTP.6.0*******************
ADODB:=ComObjCreate("ADODB.Stream") ;
ADODB.Type:=1 ;set to 1 which is Binary  2 is text
Sleep, 1000
ADODB.Open() ;After API call completes,
ADODB.Write(HTTP.responseBody) ;Write the binary data into ADODB Stream
ADODB.SaveToFile(A_WorkingDir "\" "data.csv",2) ;Save it to a file
ADODB.Close()

;************Query String builders*********
QueryString_Builder(kvp){
for key, value in kvp
  queryString.=((A_Index="1")?(url "?"):("&")) key "=" value
return queryString
}

 

AutoHotkey Merchandise-White Stress ball

Video demonstrating example API Call

Example API Call- Top Reddit post to AutoHotkey subreddit

Top Reddit post

As we mentioned in our webservice / API webinar most APIs require Authentication.   In this example I noticed the number of records being returned by the API call was not nearly as many as when I did the same call with my browser  (I think this is because I didn’t bother to use my secret/token for oAuth2.)

I decided to use the developer tools in Chrome to see what headers were being sent to reddit.   Once I included the cookie header (data borrowed from my browser) I was returning a ton of data!  I also demonstrated the use of sXML_Pretty function by VxE to re-structure the XML to be “pretty” for humans.

If you’re new to API calls, be sure to get our API Syntax Writer



Here is the video where I document the Example API Call and show how to use the Chrome Developer tools

Example API call- phone number to email address

In the below code I continue on the with examples that we talked to during our webinar on Example API calls.  In the below code I demonstrate a service that allows you to return an email address for a mobile phone number.  This allows you to “email” a “text” to a phone!

If you’re new to API calls, be sure to get our API Syntax Writer

Example API Call: emailing a text to a phone

Example API call to get an email address associated to a phone number