Tie A Recursive Google Cloud SQL Stored Procedure To A Google Apps Script / Spreadsheet Application Part 3: the application engineering

______________________

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
of the application. The integer spinner ranges from 1 to 65535. I arbitrarily set these positive integer limits for the entire application, although I could easily change them. To use the application, either click on the spinner controls, or type a number in the spinner text box. Note that this application has no input / error / etc. checking. When the spinner has the chosen value - 47, for example - click SUBMIT. The page will show
2. The application with computed results
both stored procedure result sets. A user with Google account rights to the application can also see the spreadsheet
3. The Google Spreadsheet tied to
the application web page
with the correct URL. The spreadsheet data mirrors the web page data, but for this application, the work flow moves one way, from the web page to the spreadsheet.

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:

   Code.gs

and

   CloudSQLDemoApp.html

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
Project properties
for the database connection values and the spreadsheet URL value. Although these values could go in the code files, this approach helps maintain application security if developers distribute those files. The localSpreadSheet variable at line 27 uses the spreadSheetURL value and we'll see shortly that the returnFormParams function in Code.gs uses the other values.

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

   HtmlService.createTemplateFromFile("CloudSQLDemoApp.html").evaluate();

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.

3) The web page loads with the HTML / JavaScript / jQuery ready to go. If the spinner has an integer and the user hits SUBMIT, the submitData() JavaScript function of line 53 in CloudSQLDemoApp.html runs. This function grabs the value in the spinner and runs this statement

   google.script.run.withFailureHandler(alertString).withSuccessHandler(callBack).
   returnFormParams(spinnerVal);

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

         withSuccessHandler(callBack)

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

         $("#RESET").click()

in 

         CloudSQLDemoApp.html

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

 google.script.run.withFailureHandler(alertString).withSuccessHandler().clearSheet();

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.