Friday, July 5, 2013

Manipulating Documents From Spreadsheets

Google Spreadsheets can run scripts to interact with and manipulate Google applications and services other than the host spreadsheet application itself.  For example, earlier blog postings showed how spreadsheet-hosted scripts could be used to interact with gmail and the MySQL database.  Much of the power of Google Apps Scripting derives from just such interactions with other services and applications. This posting shows how a Google Apps script hosted by a spreadsheet can be used to create and manipulate a Google document. An earlier blog entry mentioned that it has recently become possible to write Google Apps scripts hosted by the Documents application. This means it is now possible to write Google Apps Script code hosted by the Document application that can be used across Google applications and services.

To demonstrate the cross-application nature of Google Apps Script, I have written some code to copy the cell values of a named spreadsheet range to a table in a newly created document. The code also makes the header row of the table bold.

The source code can be viewed here on GitHub. To facilitate the subsequent discussion, here is the same code viewed from Pastebin (to see the code, note that the Pastebin iframe has its own vertical scrollbar):

Spreadsheet Test Data
Here is a screenshot of sample data that I wish to copy into a Document table:
Add  some data like this to your own spreadsheet and give the data range a name (Menu Action: Data->Named and protected ranges...). For the code below the name given was "ContactDetails". See an earlier posting on how to define and pro-grammatically use named spreadsheet ranges.

  • Once the data has been entered, paste the above code into the spreadsheet's script editor.
  • There are three functions, the one to call is unimaginatively named main().
  • The first time main() is called, you will be prompted to "authorize" it because it needs to write a new file to your Google Drive.
  • Once authorized and run, a messagebox dialog should appear in the spreadsheet view to indicate success or failure (remember to switch from the Script Editor view to the Spreadsheet view to see messagebox!).
  • If the code has run successfully, a new document should appear in your Google Drive called "ContactDetails".
Using the input above, my document output looks like the following:
The code shown earlier has successfully created and saved a new document and copied a range of spreadsheet values into this new document. It has also manipulated the document table so that the header row appears in bold type.

Code Notes
  • The comments in the code shown in the Pastebin iframe above are worth reading to understand how it performs its function.
  • The source code has also been passed though JSLint to check for poor formatting and general sloppiness! I would like to re-emphasize once again how important it is to subject code to JSLint or an equivalent tool. I wrote a piece before on JSLint that might be worth reviewing
  • The "use strict"; line is inserted at the top of each of the three functions to disallow problematic JavaScript usage. This is an ECMAScript 5 enhancement.
  • Apart from function writeTableToDocument(), there is nothing in the other two functions that is new or that has not been covered in earlier blog entries.
  • Function writeTableToDocument() (lines 22-49) is the one to concentrate on. It takes as parameters a string name and a nested JavaScript array as returned by the Range getValues() function. It creates a document with the name given in the parameter using the DocumentApp method create() (line 40). DocumentApp is analogous to SpreadsheetApp and has many analogous methods for doing things like getting the active document.
  • It then adds an instance of the type Table to the document  body and copies the input array to the table (line 41).
  • The Body method call appendTable() on line 41 manages to both copy the input array data into the new Table instance while also returning that same table instance. This is very convenient as it allows the Table instance to be further manipulated in the code lines that follow. 
  • The code then uses the newly created instance to extract the first row as an object of type TableRow and uses its setAttributes() method to set the font type to bold (lines 42-44).
  • Note how a styles object is created and used in this function to apply document styles. This same pattern is used to format document text and can be used for headings and paragraphs.
  • The two functions used by main() in the code example each check their input arguments and they each throw errors if the input arguments are not of the correct number and type. These checks might seem excessive but because JavaScript functions are so "relaxed" about the number and types of arguments, these types of checks are required to make the code as robust as possible.
  • Perhaps surprisingly, when the code is re-executed, a new file with the same name appears in Google Drive. The old file is not overwritten nor is any error/exception thrown. This type of behaviour differs from that seen in standard Unix and Windows environments.

This entry and the code presented here is only a taster for what can be achieved by using the scripts written in one application to manipulate another. Since Google Documents now supports Google Apps Script, we could have performed the same task using a script hosted by Documents where it would create a SpreadsheetApp instance and use it to access and copy a named range.  Later postings will again explore this type of cross-application scripting. Meanwhile keep an eye on my Leanpub book which will have a chapter or two on this topic.

No comments:

Post a Comment