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:
- 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".
- 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
- 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.
- 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.
- 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.