Category: Excel

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)(\&quot;)",Found))
          Text:=RegExReplace(Text,"\Q&quot;\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
}