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
}
