This three-part article shows how to tie a recursive Google Cloud SQL stored procedure to a Google Spreadsheet application.
Part One described the application and explain the steps needed to configure the Cloud SQL instance and the MySQL development tool.
Part Two explained the MySQL stored procedures behind the application.
Part Three here will show how the front-end Google Apps Script / Google Spreadsheet application and engineering work.
Download the two application component files and scripts for the four MySQL stored procedures from the GitHub repository.
At the March 19, 2015 GDGLA Meetup, I made a presentation covering this article. See the YouTube vid here.
I'd like to say thank you to Mr. Matt Kaufman, the Google Developer Group Los Angeles organizer. At the December 10, 2014 Chromecast Tech Talk, he suggested use of the getScriptProperties object to hold spreadsheet URL and database connection values.
We'll now look at the Google Apps Spreadsheet application. A user with Google account rights to the application will receive at least a URL to open the web page
|1. The application web page|
|2. The application with computed results|
|3. The Google Spreadsheet tied to|
the application web page
On the web page, RESET will delete all result set data on the web page and the spreadsheet, and reset the web page spinner to 1. We'll explore this code shortly.
Two files have the application code:
In the Google Apps Script script editor, drill down to
File -> Project properties -> Script properties
to place key / value pairs
|4. Enter key / value pairs in|
We first set the "MySQL values" in the MySQL Workbench tool we used to create the database in Google Cloud. As seen in Part 1, Developers Console -> Access Control shows where to find the IP address value which will then go into the Cloud SQL instance IP address string.
The component files have plenty of comments to explain everything at a close-in level. Because the overall application engineering gets a little complicated, I will now explain how it works. I'll break out everything in numbered steps.
1) When the spreadsheet first opens, an "On open" trigger calls the onOpen() function of line 29 in the Code.gs file. This function writes text in cells, formats the spreadsheet columns / fonts, etc. If no one opens / looks at the spreadsheet, this trigger never fires and this function never runs. In the script editor, drill down to
Resources -> Current Project's Triggers
to set up triggers based on pre-defined application events.
2) When the application web page actually runs, execution starts in the doGet() function of line 80 in the Code.gs file. This function "activates" the web page, in this case CloudSQLDemoApp.html. Without this function and the
call at line 86 inside the Code.gs doGet() function, the web page URL will not load the HTML for the CloudSQLDemoApp.html web page.
at line 81 as one single line, with no spaces or hard returns between
"(callback)." and "returnFormParams".
4) The statement in 3) calls the returnFormParams() function of line 89 over in the Code.gs file, passing the picked spinner value as parameter spinnerVal.
5) The returnFormParams() function in Code.gs at line 89 uses values from the scriptProperties object to set up the database connection and get the query result sets. Specifically, this function
a) sets up a connection out to the Cloud SQL database at line 109
b) calls the stored procedures it needs at lines 125 and 126
c) catches the result sets at lines 125 and 126
d) calls a function to write the result sets on the spreadsheet at
lines 144 and 145
e) places the result sets in array resultSetArray at line 166
f) returns the resultSetArray array back to the function call inside
the CloudSQLDemoApp.html function submitData(), which
made the original call to returnFormParams(). This
submitData() function call originally happened in 3) above
6) Back in the last line of the submitData() function in the
CloudSQLDemoApp.html file at line 81, the
part calls the callBack() function in HTML file CloudSQLDemoApp.html. That
callBack() function catches the resultSetArray array. The
callBack() function then draws the data in that array on the web page.
The RESET button calls jQuery function
at line 88 to reset the spinner to 1 and delete all result set data on the web page. To keep the spreadsheet consistent, the last line of this jQuery function at line 103 calls the clearSheet() function of line 66 in Code.gs with a
statement. This statement works almost exactly like the statement of step 3) above. The statement has no parameters and no callbacks.
Cloud SQL Demo shows how to easily front-end a Google Cloud MySQL database instance with a Google Apps Script project. It also shows how to weave recursion into that database. This example shows that developers can focus on the solutions they want to build - their tools can handle the product requirements.