• Intro to AutoHotkey HotStrings with AutoHotkey Intermediate AutoHotkey GUIs are Easy with AutoHotkey Intro to DOS & AutoHotkey AutoHotkey FAQ AutoHotkey FAQ

Example API call- Clearbit Discovery API

Rest API Robot FrameworkBlack Bottle2While our 2-hour webinar went into depth on API calls, I’m cranking example videos demonstrating how easy it is to connect to Webservice APIs.  You can get your own secure token and read-up on the webservice here.
If you’re new to API calls, be sure to get our API Syntax Writer

Here is the code I review in the below video

Video demonstrating example API Call to Clearbit Discovery API

AutoHotkey Webinar- Intro to API calls with AutoHotkey

In this AutoHotkey Webinar we cover an Intro to API calls with AutoHotkey.

Video Hour 1: High-level:

  • What is a Webservice API call
  • Web Scraping vs. WebServices / API
  • List of some “fun” WebService / APIs as well as links to resources to tens-of-thousands!
  • Differences between a Get vs. Post request
  • High-level look at oAuth1 vs. oAuth2
  • Resources
    • HTTP: Intro to HyperText Protocol, Types of Requests, oAuth, Parsing XML, XPath, JSON
    • AutoHotkey resource: Syntax writer, winHTTPrequest, Msxml2 vs. WinHTTPrequest, Parsing XML & JSON
  • Basic examples of API requests (compared using browser and winHTTP request)

Video Hour 2: Coding and Q&A

  • Played with several APIs
  • Discussed & demonstrated reverse-engineering API calls from a website
  • Passing key-value pairs (and using a function to keep it organized)
  • Reviewed additional APIs
  • Q&A

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

Or use one of our Fiddler Rippers


Script Highlight: Select text and “pretty” hyperlink text

The below script demonstrates how you can use AHK to automate highlighting text on a page and then, using the winClip library, constructing a “pretty” html link.  Here are links to the GetActiveBrowserURL and WinClipAPI / WinClip

Intro to API calls with AutoHotkey


What is a Webservice API? (Application Programming Interface?)

API calls with AutoHotkey


Examples of Webservice / API calls with AutoHotkey

  • Application / Software querying products for sale on Amazon.com
  • App on your phone getting latest Weather
  • Database pulling updated sales report
  • Using your Tablet to Select movies to watch on Netflix
  • DropBox application syncing files between your computer & cloud
  • Google places search

APIs are becoming increasingly available!

…Since 2005, we’ve seen APIs grow from a curiosity, to a trend, and now to the point where APIs are core to many businesses. APIs have provided tremendous value to countless organizations and developers, which is reflected in their continued growth.   Source: Programmable Web

API calls with AutoHotkey

 

 


Main Differences between WebService / API call & Web Browser

API calls with AutoHotkey


When to use API verse Web Scraping?

API calls with AutoHotkey


Some Webservices / APIs

Some of the Amazing APIs out there…

Programmable Web – list of thousands of APIs as well as a great, six-part, article reviewing APIs

Short-list of APIs open to public on Wikipedia,   Google APIs– Over 100 APIs @ Google; Yahoo APIs

Social Sites : Twitter, Pinterest, Google+, YouTube, Facebook, LinkedIn, reddit, StumbleUpon, WordPress, Instagram

Contact / Business Lookup: ClearBit, Pipl, FullContact, Dunn & Bradstreet , Foursquare, Google Places, Yelp, GeoNames

Weather: Weather Underground, Yahoo Weather, World Air

Finance: Google Finance, Yahoo Finance, PayPal, Stripe, XigniteRealTime, Ally Invest

Government: A bunch of Government APIs, Cloud.Gov, US Census, 18F.gsa.gov

Additional: Wikipedia, Amazon Product, email to text, SmartSheet,SurveyGizmo, Bitly, SnapOCR, PasteBin, DropBox, Zoho, Zillow, imugr, Mailgun, MailChimp,  Microsoft: Graph, MS Office: Excel  Word  SharePoint  OneNote Outlook Yammer PowerPoint


Break-down of a REST API request via COM object

  • Create COM object
  • Open Endpoint (w/ parameters & Authentication if GET request)
  • Set RequestHeader(s)
  • Send (w/”payload & Authentication” for POST requests)
  • Get response (body or text)

Main differences of oAuth1 verse oAuth2

Unfortunately there is no “standard” implementation of oAuth1 or oAuth2 however, at a high-level, here are some of the main conceptual differences:

oAuth1:

  • Need a Key & Token from Webservice API (typically different than your username and password)
  • You use your Key & Token in your API call
  • oAuth1 is less secure and, generally, being phased out
  • While being phased out often the “developers” (us) can use oAuth1 for development of the “app”

oAuth2:

  • Need a Key and Token (same as oAuth1) however you use the Key & Token and some other parameters to perform a “handshake” which returns a secure token which typically times-out in seconds / minutes/hours
  • Your token is restricted to the level of your account (or what has been authorized)
  • The secure token is what is shared with your actual endpoint. (this allows other Social sites (like LinkedIn, Facebook, etc.) to assist your login but not have your username/password to the endpoint

HTTP and AutoHotkey Resources

HTTP Protocol & General Tutorials

AutoHotkey specific



Let’s make some API calls with AutoHotkey!

Weather Underground

  1. Dallas forecast in XML
  2. Dallas forecast in JSON
  3. Using WinHttpRequest
  4. Example with browser

Yahoo business

  1. Pizza restaurants in zip code 75019 in XML
  2. Pizza restaurants in zip code 75019 in JSON
  3. Using WinHTTPRequest

 

AutoHotKey GUI menu for 17 API calls to web service- Simplify Connecting to amazing SilverPop via API

AutoHotkey GUI menu for API web service

AutoHotkey GUI menu for API web service

For years we have relied on using a browser for interacting with our client (SilverPop) servers.  I had heard that SilverPop had an API for doing much of the same thing but was always “too busy” to take the time to investigate.  At some point I started playing with it and was amazed at how much time could be saved.  After you get passed the “hand-shake” between your computer and the server, the rest is pretty easy.  You simply pass XML to tell the remote servers what action you want to achieve.

I then created an AutoHotKey GUI menu for API web service  which streamlined submitting the XML.

 

Here is the script for AutoHotkey GUI menu for API web service

 
#SingleInstance, Force 
SplitPath,A_ahkPath,,Root
IniRead, RU,%A_MyDocuments%\fdsd.txt, Engage, RU ;gets Username
IniRead, DP,%A_MyDocuments%\fdfds.txt, Engage, DP ;Gets password

;***********************ADD Menu********************************.
Menu, Add_MU_0,Add, Add Contact to Contact List, Add_Contact_to_contact_List_TL
Menu, Add_MU_0,Add, Create Contact List, Add_Create_Contact_List_TL
Menu, Add_MU_0,Add, Import List, Add_Import_List_TL
Menu, Add_MU_0,Add, Opt-out email address, Add_Opt_Out_Recipient_TL

;***********************EXPORT MENU********************************.
Menu, Export_MU_0,Add, Get Aggregate Tracking for &Mailing, Export_Get_Aggregate_Tracking_For_Mailing_TL
Menu, Export_MU_0,Add, Get Aggregate Tracking for &User, Export_Get_Aggregate_Tracking_For_User_TL
Menu, Export_MU_0,Add, Get Aggregate Tracking for &Org, Export_Get_Aggregate_Tracking_For_Org_TL
Menu, Export_MU_0,Add, Get Aggregate Tracking for &Org with Top Domains, Export_Get_Aggregate_Tracking_For_Org_With_Top_Domains_TL
Menu, Export_MU_0,Add, 
Menu, Export_MU_0,Add, Export List, Export_List_TL

;***********************Get********************************.
Menu, Get_MU_0, Add, Get Sent Mailings for &Org, Get_Get_Sent_Mailings_For_Org_TL
Menu, Get_MU_0, Add, Get Sent Mailings for &User, Get_Get_Sent_Mailings_For_User_TL
Menu, Get_MU_0, Add, Get &Job Status, Get_Get_Job_status_TL
Menu, Get_MU_0, Add, Get &Recipient Data, Get_Select_Recipient_Data_TL

;***********************Misc********************************.
Menu, Misc_MU_0, Add, Add others here, Misc_Temp_TL

;***********************Main menu********************************.
Menu, MainMenu, Add, &Add, :Add_MU_0
Menu, MainMenu, Add, &Export, :Export_MU_0
Menu, MainMenu, Add, &Get, :Get_MU_0
Menu, MainMenu, Add
Menu, MainMenu, Add, &Misc, :Misc_MU_0
return
Browser_Forward::Reload
Browser_Back::
^t::Menu, MainMenu, Show  ; right mouse and windows

;***********************Add********************************.
Add_Contact_to_Contact_List_TL:
GoSub Grab_Contact_List_ID
GoSub Grab_Contact_ID
XMLFunc(xml:="<AddContactToContactList><CONTACT_LIST_ID>" . Contact_List_ID . "</CONTACT_LIST_ID><CONTACT_ID>" . Contact_ID . "</CONTACT_ID></AddContactToContactList>")
GoSub Evaluator
Return

Add_Create_Contact_List_TL:
GoSub Grab_Contact_List_Name
XMLFunc(xml:="<CreateContactList><DATABASE_ID>4324324</DATABASE_ID><CONTACT_LIST_NAME>" . Contact_List_Name . "</CONTACT_LIST_NAME><VISIBILITY>1</VISIBILITY></CreateContactList>")
RegExMatch(Response, "<CONTACT_LIST_ID>(\d{7,9})</CONTACT_LIST_ID>", List_ID) ;List_ID1 is Contact List
MsgBox % List_ID1 " is Contact list ID loaded", , 4
Return

Add_Import_List_TL:
GoSub Grab_Map_And_SourceFile
XMLFunc(xml:="<ImportList><MAP_FILE>" . Map_File . "</MAP_FILE><SOURCE_FILE>" . Source_File . "</SOURCE_FILE></ImportList>")
Return

Add_Opt_Out_Recipient_TL:
GoSub Grab_eMail
XMLFunc(xml:="<OptOutRecipient><LIST_ID>43243243</LIST_ID><EMAIL>" . eMail . "</EMAIL></OptOutRecipient>")
GoSub Evaluator

Return

Add_Create_Query_TL:
XMLFunc(xml:="<CreateQuery><QUERY_NAME>Has_Mobile_30_Zip</QUERY_NAME><PARENT_LIST_ID>432432</PARENT_LIST_ID><VISIBILITY>0</VISIBILITY><PARENT_FOLDER_ID>34243</PARENT_FOLDER_ID><ALLOW_FIELD_CHANGE>0</ALLOW_FIELD_CHANGE><SELECT_COLUMNS>Last_Name|First_Name</SELECT_COLUMNS><CRITERIA><TYPE>editable</TYPE><EXPRESSION><TYPE>TE</TYPE><COLUMN_NAME>Zip</COLUMN_NAME><OPERATORS><![CDATA[BETWEEN]]></OPERATORS><VALUES><![CDATA[30000|30999]]></VALUES><LEFT_PARENS>(</LEFT_PARENS></EXPRESSION><EXPRESSION><AND_OR>AND</AND_OR><TYPE>TE</TYPE><COLUMN_NAME>Mobile</COLUMN_NAME><OPERATORS><![CDATA[IS NOT null]]></OPERATORS><RIGHT_PARENS>)</RIGHT_PARENS></EXPRESSION></CRITERIA><BEHAVIOR><OPTION_OPERATOR>103</OPTION_OPERATOR><TYPE_OPERATOR>111</TYPE_OPERATOR><MAILING_ID>669614</MAILING_ID><REPORT_ID>502993</REPORT_ID></BEHAVIOR></CreateQuery>")
return

Add_Create_Table_TL:
XMLFunc(xml:="<CreateTable><TABLE_NAME>Purchases</TABLE_NAME><COLUMNS><COLUMN><NAME>Record Id</NAME><TYPE>NUMERIC</TYPE><IS_REQUIRED>true</IS_REQUIRED><KEY_COLUMN>true</KEY_COLUMN></COLUMN><COLUMN><NAME>Purchase Date</NAME><TYPE>DATE</TYPE><IS_REQUIRED>true</IS_REQUIRED></COLUMN><COLUMN><NAME>Product Id</NAME><TYPE>NUMERIC</TYPE><IS_REQUIRED>true</IS_REQUIRED></COLUMN></COLUMNS></CreateTable>")
Return

;*******************************************************.
;***********************Export********************************.
;*******************************************************.
Export_Get_Aggregate_Tracking_For_Mailing_TL:
gosub Grab_Mailing_ID
gosub Grab_Report_ID
XMLFunc(xml:="<GetAggregateTrackingForMailing><MAILING_ID>" . Mailing_ID . "</MAILING_ID><REPORT_ID>" . Report_ID . "</REPORT_ID></GetAggregateTrackingForMailing>")
return

Export_Get_Aggregate_Tracking_For_Org_TL:
Gosub Grab_Date
XMLFunc(xml:="<GetAggregateTrackingForOrg><DATE_START>" . Date_Start . "</DATE_START><DATE_END>" . Date_End . "</DATE_END><EXCLUDE_TEST_MAILINGS/></GetAggregateTrackingForOrg>")
GoSub ImportExcel
DropUnwantedColumns(Values:="NumSeeds,NumInboxMonitored,NumUniqueAttach,NumGrossAttach,NumUniqueClickstreams,NumGrossClickstreams,NumUniqueMedia,NumGrossMedia,NumGrossAbuse,NumGrossChangeAddress,NumGrossMailRestriction,NumGrossOther,NumConversions,NumConversionAmount,NumBounceHardFwd,NumBounceSoftFwd,NumConversionAmountFwd,NumAttachOpenFwd,NumClickFwd,NumUniqueForwardFwd,NumGrossForwardFwd,NumUniqueConversionsFwd,NumGrossConversionsFwd,NumUniqueClickstreamFwd,NumGrossClickstreamFwd,NumUniqueClickFwd,NumGrossClickFwd,NumUniqueAttachOpenFwd,NumGrossAttachOpenFwd,NumUniqueMediaFwd,NumGrossMediaFwd,NumUniqueOpenFwd,NumGrossOpenFwd,NumAbuseFwd,NumChangeAddressFwd,NumMailRestrictionFwd,NumMailBlockFwd,NumOtherFwd,NumSuppressedFwd")
GoSub DropBlankExcelColumns
GoSub SaveExcel
Return

Export_Get_Aggregate_Tracking_For_Org_With_Top_Domains_TL:
Gosub Grab_Date
XMLFunc(xml:="<GetAggregateTrackingForOrg><DATE_START>" . Date_Start . "</DATE_START><DATE_END>" . Date_End . "</DATE_END><EXCLUDE_TEST_MAILINGS/><TOP_DOMAIN/></GetAggregateTrackingForOrg>")
GoSub ImportExcel
DropUnwantedColumns(Values:="NumSeeds,NumInboxMonitored,NumUniqueAttach,NumGrossAttach,NumUniqueClickstreams,NumGrossClickstreams,NumUniqueMedia,NumGrossMedia,NumGrossAbuse,NumGrossChangeAddress,NumGrossMailRestriction,NumGrossOther,NumConversions,NumConversionAmount,NumBounceHardFwd,NumBounceSoftFwd,NumConversionAmountFwd,NumAttachOpenFwd,NumClickFwd,NumUniqueForwardFwd,NumGrossForwardFwd,NumUniqueConversionsFwd,NumGrossConversionsFwd,NumUniqueClickstreamFwd,NumGrossClickstreamFwd,NumUniqueClickFwd,NumGrossClickFwd,NumUniqueAttachOpenFwd,NumGrossAttachOpenFwd,NumUniqueMediaFwd,NumGrossMediaFwd,NumUniqueOpenFwd,NumGrossOpenFwd,NumAbuseFwd,NumChangeAddressFwd,NumMailRestrictionFwd,NumMailBlockFwd,NumOtherFwd,NumSuppressedFwd")

GoSub DropBlankExcelColumns
GoSub SaveExcel
Return

Export_Get_Aggregate_Tracking_For_User_TL:

XMLFunc(xml:="<GetAggregateTrackingForUser><DATE_START>" . Date_Start . "</DATE_START><DATE_END>" . Date_End . "</DATE_END></GetAggregateTrackingForUser>")
Return

Export_List_TL:

XMLFunc(xml:="<ExportList><LIST_ID>" . List_ID . "</LIST_ID><EXPORT_TYPE>ALL</EXPORT_TYPE><EXPORT_FORMAT>CSV</EXPORT_FORMAT><ADD_TO_STORED_FILES/><DATE_START>" . Date_Start . "</DATE_START><DATE_END>" . Date_End . "</DATE_END></ExportList>")
return
;*******************************************************.
;***********************GET********************************.
;*******************************************************.
Get_Get_Report_ID_By_Date_TL:
XMLFunc(xml:="<GetReportIdByDate><MAILING_ID>" . Mailing_ID . "</MAILING_ID><DATE_START>" . Date_Start . "</DATE_START><DATE_END>" . Date_End . "</DATE_END></GetReportIdByDate>")
return

Get_Get_Sent_Mailings_For_Org_TL:
Gosub Grab_Date
XMLFunc(xml:="<GetSentMailingsForOrg><DATE_START>" . Date_Start . "</DATE_START><DATE_END>" . Date_End . "</DATE_END><EXCLUDE_ZERO_SENT/><EXCLUDE_TEST_MAILINGS/></GetSentMailingsForOrg>")
GoSub ImportExcel
GoSub DropBlankExcelColumns
GoSub SaveExcel
return

Get_Get_Sent_Mailings_For_User_TL:

XMLFunc(xml:="<GetSentMailingsForUser><DATE_START>" . Date_Start . "</DATE_START><DATE_END>" . Date_End . "</DATE_END></GetSentMailingsForUser>")
GoSub ImportExcel
GoSub DropBlankExcelColumns
GoSub SaveExcel
return

GetAggregateTrackingForMailing_TL:
GoSub Grab_Mailing_ID
XMLFunc(xml:="<GetAggregateTrackingForMailing><MAILING_ID>" . Mail_ID . "</MAILING_ID><REPORT_ID>" . Report_ID . "</REPORT_ID> </GetAggregateTrackingForMailing>")
GoSub ImportExcel
GoSub DropBlankExcelColumns
GoSub SaveExcel
return

Get_Get_Job_status_TL:
GoSub Grab_Job_ID
XMLFunc(xml:="<GetJobStatus><JOB_ID>" . Job_ID . "</JOB_ID></GetJobStatus>")
Response:=WebRequest.ResponseText
MsgBox % Response
return

Get_Select_Recipient_Data_TL:
gosub Grab_Email
XMLFunc(xml:="<SelectRecipientData><LIST_ID>4324324</LIST_ID><EMAIL>" . email . "</EMAIL></SelectRecipientData>")
GoSub ImportExcel
;***********************Miscellaneous********************************.
Misc_Temp_TL:
return

Add_Update_Recipient_TL:
;add criteria
return




;*******************************************************.
;*******************************************************.
Login:
Pod:=4
URL:="http://api" . Pod . ".silverpop.com/XMLAPI"
Loginxml:="<Envelope><Body><Login><USERNAME>" . RU . "</USERNAME><PASSWORD>" . DP . "</PASSWORD></Login></Body></Envelope>"
WebRequest := ComObjCreate("WinHttp.WinHttpRequest.5.1")
WebRequest.Open("POST", URL)
WebRequest.SetRequestHeader("Content-Type", "text/xml;charset=UTF-8")
WebRequest.Send(Loginxml)
GoSub Evaluator ;turn this into function and pass user notification
RegExMatch(Response,"jsessionid=(\w+)\<",jid) ;obtain session ID
URL:="http://api" . Pod . ".silverpop.com/XMLAPI;jsessionid=" . JID1
return

SendXML:
XML:="<Envelope><Body>" . XML . "</Body></Envelope>"
Notify("Status","Sending request",4,"TS=10 TM=8 TF=Times New Roman GC_=Yellow SI_=1000")
WebRequest := ComObjCreate("WinHttp.WinHttpRequest.5.1")
WebRequest.Open("POST", URL)
WebRequest.SetRequestHeader("Content-Type", "text/xml;charset=UTF-8")
Clipboard:=xml ;added for testing 2/1/2013
MsgBox , 0, clipboard now contains, %xml% , 2 ;added for testing
WebRequest.Send(xml)

return

Evaluator:
MsgBox % Response:="<?xml version=""1.0"" encoding=""UTF-8"" ?>`n" . WebRequest.ResponseText
RegExMatch(Response,"<SUCCESS>(\w+)</SUCCESS>",Status)
If (status1!="TRUE"){
	MsgBox,,Fail, XML Send was not Successful
	Exit
	}
   else
   Notify("Status","Receive was Successful",4,"TS=10 TM=8 TF=Times New Roman GC_=Yellow SI_=1000")
Return

Append_CleanXML_Save:
StringReplace, Response, Response, <SUCCESS>TRUE</SUCCESS>,,
Notify("Status","Received info and Saving",2,"TS=10 TM=8 TF=Times New Roman GC_=Yellow SI_=1000")
Path=%A_Scriptdir%\mailing.xml
FileDelete, %Path%
FileAppend, %response%, %path%,Utf-8
Return

;***********************Import to Excel********************************.
ImportExcel:
Notify("Status","Importing file",6,"TS=10 TM=8 TF=Times New Roman GC_=Yellow SI_=1000")
Xls := ComObjCreate("Excel.Application") ;handle
Xls.Visible := True ;by default excel sheets are invisible

Path=%A_Scriptdir%\mailing.xml
Xls := Xls.Workbooks.OpenXML("B:\Progs\AutoHotkey_L\TI\Engage\API\mailing.xml", 1, 2) ;.LoadOption.2 ;import xml file
xls.activesheet.Name := "Data"
xls:=Excel_Get()
Xls.ActiveSheet.ListObjects("Table1").TableStyle := "TableStyleLight1" ; "" clears formatting
return



SaveExcel:
xls.DisplayAlerts := false ;doesn't ask if I care about overwriting the file
Today := A_MMM . " " . A_DD . ", " . A_YYYY
File := A_ScriptDir . "\Mailings.xlsx"
File := A_ScriptDir . "\Mailings " . Today . ".xlsx"
xls.ActiveWorkbook.Saveas(File, 51) ; 6=csv 51=2007 56=2003
xls.DisplayAlerts := true ;Turn back on warnings
return

DropBlankExcelColumns:
xls:=Excel_Get()
LR := xls.ActiveSheet.UsedRange.Rows.Count ;Last Row
LC := xls.ActiveSheet.UsedRange.Columns.Count ;Grabs last Column
Loop, %LC%
{
CL:=ColtoChar(LC-A_Index+1)
Max:=XLs.Max(xls.Range(CL . "2:" . CL . LR))
Text:=XLs.Range(CL . "2").Value ;
If (Max=0) AND (Text=0) OR (Text=""){
;	MsgBox % max " text " Text " in col " CL
	xls.Range(CL . ":" CL).Delete
	}
}
return

;*******************************************************.
;*******GUI menu for API web service****************GUIS********************************.
;*******************************************************.
Grab_Contact_ID:
RegExMatch(clipboard, "(\d){10}", Contact_ID) ;going to try and extract up to first line
Gui, +AlwaysOnTop -MaximizeBox -MinimizeBox
Gui, Add, text, , What is the Contact ID?
Gui, Add, edit, r1 vContact_ID, %Contact_ID%
Gui, Add, Button, Default, OK
Gui, Show
Gui +LastFound ;use this in conjunction with next line to have ahk wait for input before proceeding
WinWaitClose
return

Grab_Contact_List_ID:
RegExMatch(clipboard, "(\d){7}", Contact_List_ID) ;going to try and extract up to first line
Gui, +AlwaysOnTop -MaximizeBox -MinimizeBox
Gui, Add, text, , What is the Contact List ID?
Gui, Add, edit, r1 vContact_List_ID, %Contact_List_ID%
Gui, Add, Button, Default, OK
Gui, Show
Gui +LastFound ;use this in conjunction with next line to have ahk wait for input before proceeding
WinWaitClose
return

Grab_Contact_List_Name:
RegExMatch(clipboard, "(\d){8}", Contact_List_Name) ;going to try and extract up to first line
Gui, +AlwaysOnTop -MaximizeBox -MinimizeBox
Gui, Add, text, , What is the Contact List Name?
Gui, Add, edit, r1 vContact_List_Name, %Contact_List_Name%
Gui, Add, Button, Default, OK
Gui, Show
Gui +LastFound ;use this in conjunction with next line to have ahk wait for input before proceeding
WinWaitClose
return

Grab_Database_ID:
RegExMatch(clipboard, "(\d){8}", Database_ID) ;going to try and extract up to first line
Gui, +AlwaysOnTop -MaximizeBox -MinimizeBox
Gui, Add, text, , What is the eMail address?
Gui, Add, edit, r1 vDatabase_ID, %Database_ID%
Gui, Add, Button, Default, OK
Gui, Show
Gui +LastFound ;use this in conjunction with next line to have ahk wait for input before proceeding
WinWaitClose
Return

Grab_Date:
Gui, +AlwaysOnTop -MaximizeBox -MinimizeBox
Gui, Add, text, , Please select the start time
Gui, Add, DateTime, vDate_Start ;, LongDate
gui, add, text, , `nNow select the end time
Gui, Add, DateTime, vDate_End ;, LongDate
Gui, Add, Button, Default, OK
Gui, Show
Gui +LastFound ;use this in conjunction with next line to have ahk wait for input before proceeding
WinWaitClose
Date_Start:= RegExReplace(Date_Start,"(\d\d\d\d)(\d\d)(\d\d).*","$2/$3/$1 00:00:00")
Date_End:= RegExReplace(Date_End,"(\d\d\d\d)(\d\d)(\d\d).*","$2/$3/$1 23:59:59")
return

Grab_Email:
RegExMatch(Clipboard, "\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*", eMail)
Gui, +AlwaysOnTop -MaximizeBox -MinimizeBox
Gui, Add, text, , What is the eMail address?
Gui, Add, edit, r1 veMail, %eMail%
Gui, Add, Button, Default, OK
Gui, Show
Gui +LastFound ;use this in conjunction with next line to have ahk wait for input before proceeding
WinWaitClose
return

Grab_Job_ID:
RegExMatch(clipboard, "(\d){8}", Job_ID) ;going to try and extract up to first line
Gui, +AlwaysOnTop -MaximizeBox -MinimizeBox
Gui, Add, text, , What is the job ID?
Gui, Add, edit, r1 vJob_ID, %Job_ID% ;, MailID
Gui, Add, Button, Default, OK
Gui, Show
Gui +LastFound ;use this in conjunction with next line to have ahk wait for input before proceeding
WinWaitClose
return

Grab_List_ID:
RegExMatch(clipboard, "(\d){8}", List_ID) ;going to try and extract up to first line
Gui, +AlwaysOnTop -MaximizeBox -MinimizeBox
Gui, Add, text, , What is the List ID?
Gui, Add, edit, r1 vList_ID, %List_ID% ;
Gui, Add, Button, Default, OK
Gui, Show
Gui +LastFound ;use this in conjunction with next line to have ahk wait for input before proceeding
WinWaitClose
return

Grab_Mailing_ID:
RegExMatch(clipboard, "(\d){8}", Mail_ID) ;going to try and extract up to first line
Gui, +AlwaysOnTop -MaximizeBox -MinimizeBox
Gui, Add, text, , What is the Mail ID?
Gui, Add, edit, r1 vMail_ID, %Mail_ID% ;, MailID
Gui, Add, Button, Default, OK
Gui, Show
Gui +LastFound ;use this in conjunction with next line to have ahk wait for input before proceeding
WinWaitClose
return

Grab_Map_And_SourceFile:
RegExMatch(clipboard, "(\S+\.xml)", Map_File) ;filename ending in xml
RegExMatch(clipboard, "(\S+\.csv)", Source_File) ;filename ending in CSV or TXT
Gui, +AlwaysOnTop -MaximizeBox -MinimizeBox
Gui, Add, text, , What is the name of the Map File?
Gui, Add, edit, r1 Lowercase w150 vMap_File, %Map_File%
Gui, Add, text, , `nWhat is the name of the Source File?
Gui, Add, edit, r1 Lowercase w150 vSource_File, %Source_File%
Gui, Add, Button, Default, OK
Gui, Show, w200
Gui +LastFound ;use this in conjunction with next line to have ahk wait for input before proceeding
WinWaitClose
return


Grab_Report_ID:
RegExMatch(clipboard, "(\d){9}", Report_ID) ;going to try and extract up to first line
Gui, +AlwaysOnTop -MaximizeBox -MinimizeBox
gui, add, text, , `nWhat is the Report ID?
Gui, Add, edit, r1 vReport_ID, %Report_ID% ;, LongDate
Gui, Add, Button, Default, OK
Gui, Show
Gui +LastFound ;use this in conjunction with next line to have ahk wait for input before proceeding
WinWaitClose
return

ButtonOK:
gui, Submit
Gui, Destroy
return

;***********************Functions********************************.
XMLFunc(XML=""){
gosub Login
gosub SendXML
GoSub Evaluator
GoSub Append_CleanXML_Save
}

ColToChar(index) { ;Converting Columns to Numeric for Excel
	If(index <= 26)
	{
		return Chr(64+index)
	}
	Else If (index > 26)
	{
		return Chr((index-1)/26+64) . Chr(mod((index - 1),26)+65)
	}
}
DropUnwantedColumns(Values=""){
xls:=Excel_Get()
LC := xls.ActiveSheet.UsedRange.Columns.Count ;Grabs last Column
CL:=ColtoChar(LC-A_Index+1)

Loop, %LC%
{
	Header:=xls.Range(CL . "1").Value
	;~ MsgBox % Header
	Loop, parse, Values, `,
	{
		If (Header=A_LoopField)
			xls.Range(CL . ":" CL).Delete
	}
CL:=ColtoChar(LC-A_Index+1)
}
}