In this session of “what I’ve automated with AutoHotkey” I explain how I saved my client a ton of time and money by using the Google Translate API.
How I used Google Translate to save the client both time and money
Below is the code I used in the code to Google Translate to save the client both time and money. Make sure you put my Excel function library and Maestrith’s Notify function in your library.
#SingleInstance,Force
Global API_Key,ID,All,From,To,XL,EndPoint,LangObj
Global Column_to_Translate:="A", Column_To_Put_Translations:="B"
;**************************************
EndPoint:="https://www.googleapis.com/language/translate/v2" ; ?parameters
IniRead, API_Key,Auth.ini,API, Key ;read current token (You'll need to get your own)
XL:=XL_Handle(1)
if ! IsObject(Xl) { ;Joe added 9/22 to make sure Excel is loaded before running
MsgBox % "Please Start Excel then click this box"
Reload
}
LangObj:={"Chinese Trad":"zh-CN","Chinese Simp":"zh-TW",Italian:"it",Czech:"cs",French:"fr",German:"de",Hebrew:"he",Indonesian:"id",Japanese:"ja",Korean:"ko",Malay:"ms",Polish:"pl",Portuguese:"pt",Russian:"ru",Spanish:"es",Thai:"th",Turkish:"tr",Vietnamese:"vi",English:"en"}
for a in LangObj ;Build Language object for ComboBox
LangList.=a "|"
All:=[]
gui,+hwndMain
ID:="ahk_id" Main
gui,Add,ListView,w800 h300,Text
gui,Add,ComboBox,vFrom,% Trim(LangList,"|") ;~ https://cloud.google.com/translate/docs/languages
gui,Add,ComboBox,x+m vTo,en||it|es|la ;Languages to translate into
gui,Add,Text,x+60, Column to be translated
gui,Add,ComboBox,x+m w50 vColumn_to_Translate,A||B|C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R|S|T|U|V|W|X|Y|Z ;Store which column to translate
gui,Add,Text,x+60, Destination for translations
gui,Add,ComboBox,x+m w50 vColumn_To_Put_Translations,A|B||C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R|S|T|U|V|W|X|Y|Z ;Store where to put the translations
gui,Add,Button,xm gLang,Discover Language
gui,Add,Button,x+25 gTranslate,Translate
gui,Add,Button,xm gRefresh,Refresh
Refresh()
gui,Show
return
Refresh(){
Rows:=XL.ActiveSheet.UsedRange.Rows.Count,LV_Delete(),All:=[]
while(A_Index<=Rows){
if(Value:=(Obj:=XL.Range(Column_to_Translate . A_Index)).Value)
All.Push(Obj),LV_Add("",Value)
if(All.MaxIndex()=15)
Break
}LV_Modify(1,"Select Vis Focus")
}
Translate(){
Gui,Submit,NoHide
From:=LangObj[From]?LangObj[From]:From
if(!From||!To)
return m("Both Language values must be set")
XL.Columns(Column_To_Put_Translations).ColumnWidth:=100
Rows:=XL.ActiveSheet.UsedRange.Rows.Count
Blank:=0,BlankInput:=0
while(A_Index<=Rows){
if(!(Obj:=XL.Range(Column_To_Put_Translations . A_Index)).Value){
Blank:=0
t("Processing")
if(Value:=XL.Range(Column_to_Translate . A_Index).Value){
QueryString:=QueryString_Builder({"source":From,"target":To,"q":Value,"key":API_Key})
RegExMatch(Val:=API_Call(EndPoint,queryString),"OUi)\x22translatedText\x22:\s+\x22(.*)\x22",Found)
Text:=Found.1
while(RegExMatch(Text,"OU)(\&#(\d+);)",Found))
Text:=RegExReplace(Text,"\Q" Found.1 "\E",Chr(Found.2))
while(RegExMatch(Text,"OU)(\")",Found))
Text:=RegExReplace(Text,"\Q"\E",Chr(34))
Obj.Value:=Text
}
}else{
Blank++
m(Blank)
if(Blank=5)
Break
}
}
t(),Notify(60).AddWindow("Complete",{size:14,Animate:"Center",ShowDelay:1000,Icon:161,IconSize:15,Title:"Translations",TitleSize:14,Color:"0x00FF00",TitleColor:"0xFF0000",time:6000})
}
GuiEscape:
GuiClose:
ExitApp
return
;********************Language detection***********************************
Lang(){
Lang:=API_Call("https://translation.googleapis.com/language/translate/v2/detect",QueryString_Builder({q:All[LV_GetNext()].Value,key:API_Key}))
RegExMatch(Lang,"OUi)\x22language\x22:\s+\x22(.*)\x22",Found)
for a,b in LangObj
if(b=Found.1)
Language:=a
ControlSetText,ComboBox1,% Language?Language:Found.1,%ID%
}
;********************API Call***********************************
API_Call(EndPoint,queryString){
static WebRequest := ComObjCreate("WinHttp.WinHttpRequest.5.1")
WebRequest.Open("GET", EndPoint . queryString)
WebRequest.SetRequestHeader("Accept", "text/xml;charset=UTF-8")
WebRequest.Send()
return Response:=WebRequest.ResponseText
}
;********************URI Encode charachters***********************************
UriEncode(Uri, full = 0){
oSC := ComObjCreate("ScriptControl")
oSC.Language := "JScript"
Script := "var Encoded = encodeURIComponent(""" . Uri . """)"
oSC.ExecuteStatement(Script)
encoded := oSC.Eval("Encoded")
Return encoded
}