Progress!

After much more futzing about, I'm happy to report that the #GEGMtl translator bot now works even when the spreadsheet is closed. It seemed like it would be trivial, but in fact it turned out to be harder than I thought. (That's usually the case when I sit down to write some code.)

The first big hurdle was a limitation of the language library that is included in Google Apps Script. It's powerful and built off Google translate, but there's no function to detect language. Since I wanted the script to fluidly alternate between English and French text, that was a requirement.

I tried the Google translate API, but after struggling with it for a few hours, I read the not-so-fine print that it's a paid service. Fortunately, a quick search revealed detectlanguage.com which has an option to call an API to discover the language of the text you pass it. It was a matter of minutes to get the code working for that. With a limit of 5000 calls per day, I think it will scale enough to start with. If it ever grows to the point where I need to look into other options, hopefully I'll have enough in my pocket to cover the expense.

The next task was to call the language service to ask it to translate the text. Pretty straightforward, but I kept mixing up the indexes of the arrays I was using. The debugger took care of sorting out that issue. By stopping the code at certain points I was able to see which index of the relevant variable was holding the value I wanted, and where I'd messed up.

The final piece of the puzzle was to get the translation to show as a reply to the original tweet. I figured out the parameter to use to get this to work, and I now can proudly tweet using the hash tag and it will work even when the sheet is closed.

The next goal is to create a version which will work as an add on or a web app. It will take a lot of work, since I don't know much about the other languages involved - jQuery and CSS. I'm working on it, but it's certainly a challenge.