Tuesday, 24 February 2015

Multi-lingual Job Board

The job board that I created using google docs has  been a very useful and powerful planning and organizational tool for me. In fact, I consider the ability to plan 2 weeks in advance the best part of my job board. In a recent disicussion on twitter with Scott Dickson (@scottAllD) he asked if there was a way to translate the contents of the job board as some of his staff didn't speak very good English. I looked into it and it turns out that Google has included a translate function in Sheets! A few mintutes later I had added the functionality to my job board to translate between any 2 languages or more!

This discussion reminded  me that not only is my job board a planning and organizational  tool, but it is also a powerful communication tool! The ability to translate adds so much to golf maintenance crews that are more diverse than my crew (we all speak english).
Spanish anyone? how about Chinese?

For those of you who use my job board I will share the formula that gives the job board the ability to translate for you. Just paste this formula into cells C4:F8. You will also need to add a column to the right of the employee names in the "lists" sheet. This will be where you put the language codes for each employee into. "en" for English, "es"for Spanish. A full list of the language codes can be found here. Just use the 2 letter code in column 2. Now all you have to do is add tasks to the planner and voila, they appear in the desired language on the job board!

=iferror(googletranslate(iferror(hlookup($A$1,Planner!$C$1:$P$27,C$3+$B4,false())),"auto",vlookup($A4,Lists!$B$1:$C$24,2,false())))

I have set the input language to "Auto." What this does is use the system language settings as the input language. I highly encourage people to check out my formulas and learn how they work. Google has a great function help section that you can use to reverse engineer the formulas I use in my sheets.
add a colum to the right of the employee names
The really cool thing here is that you can assign different languages for each employee. One issue that I do see arising is how google translates the different tasks I have outlined. As I only speak one language I cannot check to make sure any of the translate correctly. I guess some trail and error would  be required to ensure that each taks put into the job board is translated in a meaninful way for your staff.

If you have a multi-lingual crew I highly recommend checking out the =googletranslate function in Google Sheets. This adds a powerful tool that most of the paid software job boards can't offer and best of all it's free!