As we were getting ready to implement a set of improvements to our process that allows bulk-uploading of phrase videos and translations from our desktop app, I noticed that our language team frequently used Google docs to collaborate on the creation of phrase lists and review of translations with other language experts. Since Google docs has an API, I proposed that we formalize that as part of our workflow:

1. Record phrase videos in our desktop application
2. Copy the spreadsheet to Google Docs
3. Import from the mightyverse.com web app (including a preview step before the data is committed)

I was pleased to find a well-worn path to integrate with our Rails web app and was able to use the google-spreadsheet-ruby gem to write the import code.

Last week, the new import workflow allowed us to fix a large number of errors with the Italian recordings and their English translations. Paul and Iku copied 9 spreadsheets that included 1503 phrases to Google docs and, as expected, were able to easily review and edit the text using the fabulously efficient browser-based editing features implemented by the Google team.

While I could implement such a web app, I wouldn’t have devoted the engineering time to build such powerful editing features for a simple import form. However, the fact that collaborative editing was so easy, dramatically reduced the effort required.

An unexpected consequence of using the powerful Google spreadsheet UI for the fairly mundane import task is that we automatically had additional automated editing features for free. The first example of this was when we discovered that a lot of the Italian phrases had been entered in all caps, and Paul asked if we could automatically make them lower-case on import or if they should edit them by hand. Then, inspiration hit! “Let me show you,” I said, “and demonstrated as a created a new column and typed “=LOWER(” then selected the column with the upper case text. As if by magic, the lower case text appeared. Then, I dramatically selected the cell with the new formula and dragged the corner all the way to the bottom to repeat the action for every row in the spreadsheet.

I could have written the code to lower-case on import and deployed it in 20 minutes, but with Google docs, I could teach my “non-technical” co-founder how to do it himself in less than a minute. Even better, I then taught him how to figure out his next request with very little help from me, and he is well on his way to addressing these kinds of issues in the future without any special-case software development in our web app.

His next challenge was to set just the first character to be upper-case. He found the PROPPER formula, but sadly that was not precisely what he wanted. (PROPPER makes the first letter of every word upper case.) I showed him the formula reference and taught him how to combine formulas and he figured it out. The only technique he really struggled with was how to remove quotation marks, where the character had to be escaped. It is really hard to look for info on special characters like ” in google search. I knew the concept to search for and all the coder synonyms and was able to find it for him. With a spreadsheet he could test out each part of the formula in its own column:

In summary, here are the formulas that Paul found to be useful:

Make Lower Case:
=LOWER(B3)

Remove quotation marks:
=substitute(C3;char(34);””)

Capitalize the first letter (substitute A2 for the cell with the text):

=CONCAT(UPPER(LEFT(A2)),MID(A2,2, LEN(A2)))