GETTING STARTED

The easiest way to get a feel for what you can do with GQLx is to head on over to the online console - be patient, the scripts can a while to load.

For the purposes of making explanations simple, imagine that you have a Google spreadsheet titled 'Lord Of The Rings' that contains a single worksheet named 'Characters':

Logging in

The first time you use the console you will need to login to authorise access to your own Google Doc's. Clicking the LOGIN button will take you through the Google Docs authorisation procedure and then return you to the console page.

Your login will remain active until such time as you actively logoff or the authentication token received from Google expires (typically after about a month of no activity).

NOTE:  the online authentication mechanism for Google Doc's can sometimes be annoyingly quirky - you may find you need to attempt to login a second time or refresh the page before everything knows that its been authenticated and authorised and that its good to go. As a last resort you can delete the browser cookies for gqlx.twyst.co.za and if necessary google.com.

Opening a spreadsheet

The online console lets you enter and execute GQL commands and view the results from inside your web browser. Start by executing the OPEN command:

OPEN LordOfTheRings;

If you want to open a spreadsheet with a name that contains spaces or anything other than numbers and letters then it must be enclosed in single quotes e.g:

  • OPEN LordOfTheRings;
  • OPEN 'Lord Of The Rings';
  • OPEN 'LordOfTheRings-2';

The OPEN statement (like all GQLx commands) is terminated with a semi-colon.

Also:

  • OPEN *; conveniently opens all spreadsheets associated with your user ID.
  • SHOW SPREADSHEETS; lists all the spreadsheets that are currently open.

Working with worksheets

Most of GQLx is devoted to interacting with the worksheets of an open spreadsheet. The reference manual describes the GQLx statements in more detail but for a first hack try statements similar to the ones below:

  • SELECT * FROM Characters;

  • SELECT * FROM Characters WHERE Rating > '7';

  • INSERT INTO Characters (Character,Actor,Rating) VALUES('Pippin','Billy Boyd','4');

  • UPDATE Characters SET Rating='5' WHERE Actor='Billy Boyd';

  • DELETE FROM Characters WHERE Rating < '3';

Some things to take note of:

  • String are enclosed in single quotes
  • GQLx is not case sensitive - you can type SELECT, select or even SeLeCt
  • All statements end with a semi-colon
  • You can submits multiple statements as a batch with a single execute

A Note on Spelling

Since it is mostly intended to be used by real human beings, GQLx takes a reasonably relaxed approach to spelling.

Other than the reserved keywords (OPEN,SELECT,INSERT, etc) it will tolerate minor spelling mistakes in the names of spreadsheets,worksheets,columns and values. In the example above you could have typed:

OPEN LrdOfThRngs;

or

SELECT * FROM Chrctrs;

 and more than likely the correct spreadsheet would have been opened and the correct data returned.

You can use the SET MATCH NORMAL or SET MATCH EXACT statements to alter this if it leads to undesirable,unexpected or even drunken and disorderly behavior.

Moving On

From here the reference manual takes over ... it describes the use of the Java GUI and console applications and also documents all the GQLx statements in more detail.