When I presented various options for using spreadsheets available in the free Google Office tools suite on one's
Google Drive, I was asked if there wasn't a "simpler" way to do all this.
The answer is yes. However, there will be some restrictions to accept regarding your data formats and what you can do with them. If that is acceptable, keep reading and you'll find some useful "cookie cutter" options.
When I wrote the aforementioned articles, I assumed that readers unable to cope with creating their own queries would simply ask a nerdy friend for five minutes of help. But another option would be to used canned queries which match defined structures of the spreadsheet.
Let's consider the simplest cases. For anything more complicated, post questions in the comments. One can build very complex queries for a very complex glossary spreadsheet, but if that's where your at, this and other guns are for hire, no checks accepted.
You have bilingual data in Language A and Language B. These can be any two languages, even the same "language" with some twist (like a glossary of a modern standard English with 19th century thieves' cant from London). The data can be a glossary of terms, a translation memory or other bitext corpus, or even a monolingual lexicon (of special terms and their definitions or other relevant information. The fundamental requirement is that these data are placed in an online spreadsheet, which can be created online or uploaded from your local computer and that Language A be found in Column A of the spreadsheet and Language B (or the definition in a monolingual lexicon) in Column B of the spreadsheet. And to make things a little more interesting we'll designate Column C as the place for additional information.
Now let's make a list of basic queries:
- Search for the text you want in Column A, return matches for A as well as information in Column B and possibly C too in a table in that order
- Search for the text you want in Column B, return matches for B as well as information in Column A and possibly C too in a table in that order
- Search for the text you want in Column A or Column B, return matches for A/B and possibly C too in a table in that order
Query 1: searching in Column A
The basic query could be: SELECT A, B WHERE A CONTAINS '<some text>'
Of course <some text> is substituted by the actual text to look for enclosed in the single straight quote marks. If you are configuring a web search program like IntelliWebSearch or the memoQ Web Search tool or equivalents in SDL Trados Studio, OmegaT or other tools, the placeholder goes here.
If you want the information in the supplemental (Comment) Column C, add it to the SELECT statement: SELECT A, B, C WHERE A CONTAINS '<some text>'
The results table is returned in the order than the columns are named in the SELECT statement; to change the display order, change the sequence of the column labels A, B and C in the SELECT, for example: SELECT B, A, C WHERE A CONTAINS '<some text>'
Query 2: searching in Column B
Yes, you guessed it: just change the column named after WHERE. So
SELECT B, A, C WHERE B CONTAINS '<some text>'
for example.
Query 3: searching in Column A or Column B (bidirectional search)
For this, each comparison after the WHERE should be grouped in parentheses:
SELECT A, B, C WHERE (A CONTAINS '<some text>') OR (B CONTAINS '<some text>')
The statement above will return results where the expression is found in either Column A or Column B. Other logic is possible: substituting AND for the logical OR in the WHERE clause returns a results table in which the expression must be present in both columns of a given record.
And yes, in memoQ Web Search or a similar tool you would use the placeholder for the expression twice. Really.
Putting it all together
To make the search URL for your Google spreadsheet three parts are needed:
- The base URL of the spreadsheet (look in your browser's address bar; in the address https://docs.google.com/spreadsheets/d/1Bm_ssaeF2zkUJR-mG1SaaodNSatGdvYernsE7IJcEDA/edit#gid=1106428424 for example, the base URL is everything before /edit#gid=1106428424.
- The string /gviz/tq?tqx=out:html&tq= and
- Your query statement created as described above
Just concatenate all three elements:
{base URL of the spreadsheet} + /gviz/tq?tqx=out:html&tq= + {query}
An example of this in a memoQ Web Search configuration might be:
https://docs.google.com/spreadsheets/d/1Bm_ssaeF2zkUJR-mG1SaaodNSatGdvYernsE7IJcEDA/gviz/tq?tqx=out:html&tq=SELECT B, A WHERE (A CONTAINS '{}') OR (B CONTAINS '{}')
and here you can see a search with that configuration and the characters 'muni' : https://goo.gl/D5cQmh
Adding custom labels to the results table
If you clicked the short URL given as an example above, you'll notice that the columns are unlabeled. Try this short URL to see the same search with labels:
https://goo.gl/3zJQqK
This is accomplished simply by adding
LABEL A 'Portuguese', B 'English' to the end of the query string.
If you look at the URL in the address bar for any of the live web examples you'll notice that space characters, quote marks and other stuff are substituted by codes. No matter. You can type in clear text and use what you type; modern browsers can deal with stuff that is ungeeked too.
To do more formatting tricks, RTFM! It's
here.
