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.

Tie A Recursive Google Cloud SQL Stored Procedure To A Google Apps Script / Spreadsheet Application Part 2: the MySQL stored procedures

______________________

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 explained the steps needed to configure the Cloud SQL instance and the MySQL development tool.

Part Two here will explain the MySQL stored procedures behind the application.

Part Three 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.

________________

In an earlier article, I showed how recursion works in SQL Server 2005 and beyond. The article started with a stored procedure, a function, and a common table expression that recursively calculate integer factorials - a classic way to show how recursion works. Then, the article showed how to recursively extract the multiple-of-two component integers of a given integer, again with a stored procedure, a function, and a common table expression. This second problem, and its solution, led to the example application for this article.

That earlier article explains that a base-10 integer can "unpack" into base-10 multiples-of-two. For example,

     8331 = (2 ^ 13) + (2 ^ 7) + (2 ^ 3) + (2 ^ 1) + (2 ^ 0)

              =     8192 +     128 +         8 +         2 +         1

              =     8331  

As I explained in that article, a looping algorithm will definitely work here, but a recursive solution will also work. MySQL does not have the common table expressions of modern SQL Server, and MySQL functions don't support recursion. Still, MySQL stored procedures can handle recursion, and this example application will rely on it.

In the MySQL workbench, I first built a database called 'DEMO'. Then, I ran stored procedure usp_create_example_table to create a table with base-10 multiple-of-two integers in the ROW_ID column and their string equivalents in the ROW_NAME column. The powers of two range from zero to fifteen.

Next, I built stored procedure usp_build_comma_dlm_string as the core stored procedure for the application. This stored procedure has two parameters: N, the integer it will unpack, and PARSE_STRING, a comma-delimited string of the multiple-of-two components of N. Because this SP returns a value in PARSE_STRING, technically the SP should declare it as an "OUT" parameter. However, in this application, stored procedures

   usp_return_comma_dlm_string

and

   usp_return_tbl_of_values

both call stored procedure

   usp_build_comma_dlm_string

in their own code, which has a few implications because of the recursion machinery. Stored procedure

   usp_build_comma_dlm_string

recursively builds PARSE_STRING, the comma-delimited string. For the recursion to work, the stored procedure must use @variable @PARSE_STRING. An @variable, or a user-defined "session" variable, stays alive throughout the stored procedure session. Because of the way MySQL works, a user-defined "session" @variable does not get the "OUT" keyword, with two exceptions. First, in the stored procedure declarations specifically, the @variables don't have the actual "@" character. We'll see this in usp_build_comma_dlm_string. Second, the declaration for a variable intended as a session variable does not have the "@" character in front of the variable name. We'll see this in usp_return_comma_dlm_string.

In stored procedures, MySQL will default system variable max_sp_recursion_depth to zero. This disables recursion and to enable it, I arbitrarily set max_sp_recursion_depth to 20, or 20 recursion levels. This system variable has a maximum value of 255.

If the call to usp_build_comma_dlm_string has a NULL value for @PARSE_STRING, the if-block sets @PARSE_STRING to an empty string value at line 14. This helps prevent crashes, because MySQL defines an empty string as a string with length zero.

The actual recursion happens in the next if-block. If N, the integer to unpack, equals zero at line 26, the stored procedure has hit the recursion base-case and will end as planned, with the recursively-computed value in "session" variable @PARSE_STRING. Because the final character of @PARSE_STRING is a comma at this point, the base-case strips it off at line 27. The recursion step in the else-block uses LOG2, FLOOR, and POWER functions at line 29 to compute the largest base-10 multiple-of-two component integer in parameter / variable N. It places this value in integer variable COMPONENT. Next, it casts COMPONENT as a string and concatenates this value to @PARSE_STRING, with a trailing comma at line 30 to delimit the string. I did not add a space after that comma because spaces will crash the MySQL find_in_set() function. We'll see shortly that stored procedure usp_return_tbl_of_values uses this function. Line 31 subtracts COMPONENT from N, to drive the stored procedure closer to the base-case. Finally, the stored procedure makes a recursive call to itself at line 32.

Stored procedure usp_return_comma_dlm_string has one IN parameter, input_param - the integer to parse / unpack. The stored procedure first declares variable @PARSE_STRING at line 9. Note that this declaration does not have the leading "@". Only in later uses does @PARSE_STRING get the "@" in most cases, as described above. It initializes @PARSE_STRING to NULL, calls stored procedure usp_build_comma_dlm_string, and selects @PARSE_STRING to return the value of this variable to the caller.

Stored procedure usp_return_tbl_of_values
 works almost the same way. In the SELECT statement, the SP queries DEMO database table EXAMPLE_TABLE at line 12. The SP uses variable @PARSE_STRING inside the WHERE clause FIND_IN_SET function at line 13. This SELECT returns the result set to the caller.

This covered the MySQL stored procedures for the application. Part 3 will show how the front-end Google Apps Script / Google Spreadsheet application / engineering work.

Tie A Recursive Google Cloud SQL Stored Procedure To A Google Apps Script / Spreadsheet Application Part 1: set up the tools

______________________

This three-part article shows how to tie a recursive Google Cloud SQL stored procedure to a Google Spreadsheet application.

Part One here will describe the application and explain the steps needed to configure the Cloud SQL instance and the MySQL development tool.

Part Two will explain the MySQL stored procedures behind the application.

Part Three will show how the front-end Google Apps Script / Google Spreadsheet application and engineering work.

Download the two Google Apps Script 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.
________________

For a recent project, I saw that a web front-end solution over a Google Cloud SQL database would work the best. The customer then mentioned spreadsheets, so the solution I built awhile back, to front-end BigQuery with Google Spreadsheets, came in handy. It turned out that some recursive stored procedures would really help. Don't ask - it's a long story. Fortunately, I have some experience with this, and everything worked out. The whole solution became the core idea for this article.

Except for description of a few important details, this article will focus more on the software I built, and less on how to set up the MySQL / Google Cloud SQL / Google Apps Script machinery. A YouTube video helped me set up Cloud SQL, and I described how to set up Google Apps Script for Google Spreadsheets in my earlier articles here at the BitVectors blog.

After some research and the video, I set up a Cloud SQL project and then I installed MySQL locally to front-end that project. The Developer's Console made it quite clear that it expected me to use MySQL 5.5 - anything past that simply won't work.
1. Cloud SQL expects MySQL 5.5
I found a Windows MySQL 5.5 download and I proceeded with that installation.

Next, I opened the local MySQL workbench and built a new connection to the Cloud SQL project. I placed the IP address of the Cloud SQL instance found here
2. The Cloud SQL instance IP address
in the Developers Console
in the MySQL workbench connection configuration

3. In the local MySQL workbench, set up a new connection
and type the Google Cloud SQL project IP address
in the Hostname textbox
as shown here. Then, in the Developers Console ACCESS CONTROL tab

4. In the Developers Console, configure the authorized networks
I configured the authorized networks as suggested in the Setup for Google Cloud SQL section here. I made sure I included the IP address for the machine I used for development as one of those authorized networks.

This completed the tool configuration for the application. Part 2 will look at the MySQL stored procedures behind the application.

Front-end Google BigQuery with a Google Spreadsheet Part 5 - Configure Security / Distribute the Application

_________________________

This five part article shows how to set up a Google BigQuery project, and how to front-end that project with a sample Google Docs Spreadsheet web application. This application will work in all browsers and platforms, including the Apple iPad and iPhone. The article also explains the application engineering and configuration. Download the two application component files from the GitHub repository.


Part one explained how to set up a Google Cloud Platform account, and how to build a basic BigQuery project.

Part two described the application user experience.

Part three explained the application engineering.

Part four started with a brief introduction to the Google Docs script editor. Then, it showed how to tie the application code to both the Google Docs Spreadsheet and the BigQuery project.

Part five here will show how to configure security for the sample application, and how to distribute the sample application to users.

I want to say thank you to Google Developer Advocate / Software Engineer Felipe Hoffa. At the Google Cloud Platform Roadshow here in Los Angeles, he took the time to discuss Google Apps Scripts development with me, and answered my questions about the associated security machinery.
____________________

In parts one to four, I showed how to set up a Google BigQuery project, how to front-end that project with a Google Docs Spreadsheet application, the user experience of the application, and how to tie that application to both the Google Docs Spreadsheet and the BigQuery project. However, we need to configure application security before we distribute the application to authorized users. Some of the individual security choices described here might not make sense on their own, but I found that this combination of settings allows the authorized user the exact access to the spreadsheet, the form, and the BigQuery project that we want.

When making changes to existing Google Apps Scripts file security and / or file share configurations, I found that it's a good idea to completely rebuild any existing shares to relevant Google accounts. First, the Google accounts that receive the file share should drill down to account security and permissions

1. Revoke access to the file from inside the sharing the Google account
and revoke access to the file from inside the Google account, if that access already exists. Then, on the spreadsheet page itself

2. Open the Share 
the Google Docs Spreadsheet file owner should click Share at the upper right to open the Share settings window

3. Click "x" to delete existing non-owner shares
and click "x" to delete existing non-owner shares. The first time into the script editor for the application, click the cloud

4. Click the cloud to open the web app configuration window
to open the initial "Deploy as web app" window

5. The initial "Deploy as web app"
window for the application
and type "1" in the Project version textbox. Set the "Who has access to the app dropdown" to 'Anyone' and click Save New Version. The "Deploy as web app" window will change

6. The configured "Deploy as web app" window
a little; click  Deploy .

After the initial Deploy as web app window set-up, future changes will work a little differently. Click on the cloud to open the Deploy as web app window:

7. The web app configuration window
From there, pick

     Execute the web app as: -> me

and

     Who has access to the app: -> Anyone

Then click   Update   to return focus to the script editor.

Back at the script editor, click the cloud again to reopen the Deploy as web app window

8. Right-click latest code to get the form URL 
and right-click latest code to get the form URL. The Google account that will share the form will see and use the form through this URL.

Back at the Share settings window from Figure 3

9. Add the Google addresses that will receive shares to the application
type the Google account addresses that will receive shares to the application. Pick Can edit in the permissions dropdown to the right of the Invite People box. The Notify people via email checkbox will send each of those Google addresses an email with a URL pointing to the Google Docs Spreadsheet that "owns" the form through the script editor. We can optionally add a cover message, or extra text, to that email with the Share settings Add Message feature, but we can't delete the Google Docs Spreadsheet URL from the sent message. The Share settings Add Message feature will only add text to the default message. This matters because if the business rules only allow the sharing Google addresses access to the form, and not the "owning" Google Docs Spreadsheet, don't use the Share settings window email notifications. Instead, manually send an email through Gmail with the form URL found in figure 8 above. Note also that in figure 3 above, we could set the user's rights to "Can View". However, if that user tried to see the form, a permission request page would open. Additionally, the Google Docs Spreadsheet would open as read-only.

Part three mentioned authorization. If a script can see private data, the Google App script environment will eventually open a popup asking for that authorization. I saw this request when I built the trigger in part three, but I found that it could happen at other steps. No matter when it happens, the explanation would work better here, in part five. When the popup opened, click

10. The Authorization required
dialog box
 Continue  and the Request for Permission window

11. The Request for Permission
dialog box
opens. Click  Accept  to formally authorize the application.

The Share settings window will send an email to each sharing Google address; these emails will have the URL's to the Google Apps Scripts form and possibly the Google Docs Spreadsheet. At that point, the users owning those addresses can use the form and possibly see and edit the spreadsheet. This security model works, because any Google address, without authorization through the Share settings window, can certainly try to open the Google Docs Spreadsheet URL or the Google Apps Scripts form "owned" by the form. However, that Google account will get a message explaining that the file owner must give permission (rights) first.

BigQuery Demo Project shows how to easily front-end a BigQuery dataset with a Google Apps Script project. With this development environment, we can build an essentially universal application front-end that will run on any device that can see the Internet through a browser. This includes the Apple iPad and iPhone. This approach has almost infinite flexibility.

Front-end Google BigQuery with a Google Spreadsheet Part 4 - The Script Editor / Tie the App Code to the Spreadsheet and BigQuery

_________________________

This five part article shows how to set up a Google BigQuery project, and how to front-end that project with a sample Google Docs Spreadsheet web application. This application will work in all browsers and platforms, including the Apple iPad and iPhone. The article also explains the application engineering and configuration. Download the two application component files from the GitHub repository.

Part one explained how to set up a Google Cloud Platform account, and how to build a basic BigQuery project.

Part two described the application user experience.

Part three explained the application engineering.

Part four here will start with a brief introduction to the Google Docs script editor. Then, it will show how to tie the application code to both the Google Docs Spreadsheet and the BigQuery project.

Part five will show how to configure security for the sample application, and how to distribute the sample application to users.

I want to say thank you to Google Developer Advocate / Software Engineer Felipe Hoffa. At the Google Cloud Platform Roadshow here in Los Angeles, he took the time to discuss Google Apps Script development with me, and answered my questions about the associated security machinery.
____________________

I built, edited, and tested the application script files in the Google Docs Script editor. The application needs one script file for the spreadsheet and one HTML file to build the front-end form. To create a new spreadsheet script file, start at the Google Docs Spreadsheet and drill down


1.  Open the Script editor
to open the Google Apps Script window:


2.  The Google Apps Script window
Pick Spreadsheet and a new file


3.  The new Code.gs file
named "Code.gs" will open in the editor. For the sample application, I kept this filename, I deleted the default code, and I proceeded with development. I used similar steps to create the HTML file


4. The new HTML file
for the application. I renamed this file "BigQueryDemoApp.html" and I proceeded.

The first time I saved either of the two files, a dialog box wanted the project name, so I typed "BigQueryDemoApp"

5. The Project Name dialog box
and I clicked "OK".
_________________

The sample application in the GitHub repository needs customization in three places in order to run. These changes will happen in the Code.gs file.

First, function doGet() in Code.gs must point to the HTML file that builds the form in the browser - for this application, BigQueryDemoApp.html. I typed the string "BigQueryDemoApp.html" 

6. Function doGet() points to the HTML file
at line 109. Back at the spreadsheet, I copied the entire URL address from the URL bar

7.  Copy the Google Docs Spreadsheet URL
and I pasted it at line 19 in Code.gs:


8.  Paste the Spreadsheet URL value in Code.gs
Note that I blanked out most of the URL in these figures. This value will become the spreadSheetURL variable value. Three functions in Code.gs use this value in variable localSpreadSheet and it became easier to structure it as a global variable. I wanted this part of the configuration to happen automatically; originally, I tried to set this variable value with code that would "auto-sense" the Google spreadsheet URL. Unfortunately, I never found a way to make this work. If someone has a solution, please feel free to leave a comment below.

Now, the application needs to see the BigQuery project. At the Developers Console

9. Get the BigQuery Project ID value
from the Developers Console
I copied the BigQuery Project ID value "lexical-emblem-651" (blanked out in Figure 11 above) and I pasted it in the Code.gs runQuery() function

10.  Paste the Project ID value in Code.gs
at line 170 to complete the application configuration.

To run the application from the Google Docs script editor, first click the "deployment" cloud icon

11. Click the "deployment" cloud icon
to open the Deploy as web app window:

12. Click "latest code" to open the front-end
application form in a new browser tab
Click latest code and the front-end application will open in a new browser tab.


This gave a brief introduction to the Google Docs script editor, and showed how to configure the sample application within Google Docs. Part five will show how to configure security for the sample application, and how to distribute the sample application to users.

Front-end Google BigQuery with a Google Spreadsheet Part 3 - The Application Engineering

_________________________

This five part article shows how to set up a Google BigQuery project, and how to front-end that project with a sample Google Docs Spreadsheet web application. This application will work in all browsers and platforms, including the Apple iPad and iPhone. The article also explains the application engineering and configuration. Download the two application component files from the GitHub repository.

Part one explained how to set up a Google Cloud Platform account, and how to build a basic BigQuery project.

Part two described the application user experience.

Part three here will explain the application engineering.

Part four will start with a brief introduction to the Google Docs script editor. Then, it will show how to tie the application code to both the Google Docs Spreadsheet and the BigQuery project.

Part five will show how to configure security for the sample application, and how to distribute the sample application to users.

I want to say thank you to Google Developer Advocate / Software Engineer Felipe Hoffa. At the Google Cloud Platform Roadshow here in Los Angeles, he took the time to discuss Google Apps Script development with me, and answered my questions about the associated security machinery.
____________________

In an earlier series of articles, I explained the engineering behind a sample C# / ASP.net application that front-ends a BigQuery project. That sample application certainly works, and works well. Still, the solution has a large number of files scattered across a large number of directories. It requires Visual Studio as a development tool, and those who want to take this approach must directly deal with server resources. In contrast, a Google Docs Spreadsheet solution to do almost the same thing involves two JavaScript files. We can see the free development tool in a browser, and because a Google Apps Scripts solution will deal with server resources strictly as a supplied service, we can focus on building the engineering.

Google Drive, a tool available to each Google account, stores the Google Docs files owned by that account:

1. The Google Drive page
To create a new Google document, click  CREATE  at the left and follow the prompts. I will use existing file "BigQuery Demo App" for this article.

When I open the indicated file

     BigQuery Demo App

from Google Drive, a trigger calls function onOpen() in file Code.gs. I'll discuss this trigger shortly. The function operates on global variable

     localSpreadSheet

This variable points to the BigQuery Demo App spreadsheet stored on Google Drive. We'll look at the declaration of this variable in part 4.

Inside onOpen(), array headerArray holds the text strings that will fill spreadsheet cells A2 to G2. I used "\n" to force line breaks in those strings. Next, twelve JavaScript statements handle cell merges, spreadsheet cell formats, text formats, etc. The last formatting line

     localSpreadSheet.getRange("A2:G2").setValues([headerArray]);

places the headerArray values in cells A2 to G2. I could have used a loop to place the array values in these cells, but the setValues() function in this line will handle this faster and more efficiently. The function setValues will get its values from headerArray, a one-dimensional array, but this function expects a multi-dimensional array. To solve this, I encased headerArray inside an array within the setValues argument. The final set of statements of onOpen() sets the spreadsheet column width values.

trigger runs onOpen() when the file opens. No other actions or behaviors on any forms / etc. associated with this file will invoke the trigger. To set up the trigger, drill down to Tools -> Script Editor . . .

2. Open the Google Docs script editor
and in the script editor, drill down to Resources -> All your triggers

3. Drilling down to the trigger editor
and click

4. Drilling down to the trigger editor
to open the trigger editor here:

5. The trigger editor
The Run dropdown has some default events; I picked onOpen, I configured it as required, and I saved it. If I built this trigger before I gave the app "authorization to run", the script editor will probably open a popup asking for this. I'll discuss authorization in part five.

In part one, I showed how to add the BigQuery API to the server-side BigQueryDemoApp project. I mentioned that the application software will need a similar step and I will explain that now. In the script editor, drill down to Advanced Google Services...

6. Drill down to Advanced Google Services...
to open the Advanced Google Services window:

7. Pick the BigQuery API
within the application
I looked and saw that the left dropdown only had v2 available. If the dropdown provides more versions in the future, developers should probably research which version to pick, because aside from all the other considerations, the choice would likely need to match the choice in the Google Cloud Console. I switched BigQuery  on  and I clicked "OK".

Part five will show how to configure application security, but for now, we'll assume an authorized Google account owner has logged in, has a browser window open, and wants to use the Google BigQuery Demo App. We'll also assume that user has full access and security rights to that application. To run BigQuery Demo App, the user simply drops the URL pointing to BigQuery Demo App into the browser URL bar and hits Enter. The form does not need to have the spreadsheet open to run. Therefore, when the form opens, it does not invoke the onOpen() trigger. Instead, the form first runs the doGet() function in Code.gs

function doGet(){

  //  A request made to the script URL runs the doGet() function.
  //  This function loads the HTML file BigQueryDemoApp.html in
  //  the browser . . .

  return HtmlService.createTemplateFromFile("BigQueryDemoApp.html").evaluate();

}

and the evaluate() statement in this line loads the BigQueryDemoApp.html file in the browser. This HTML file draws the form in the browser using its HTML markup starting at line 381. I used Tables Generator to build the basic form HTML, which I then customized.

To explain the engineering, I heavily commented both application component files. Although readers would find it much faster and easier to study those embedded comments, we'll look at the major highlights here.

First, in BigQueryDemoApp.html, lines 13 and 14 declare pointers to external jQuery libraries. The reset button calls a jQuery script to reset all dropdowns, hide the appropriate form controls, and clean out any existing result sets from the form. The submit button at line 490 calls JavaScript function submitData(), which first builds the query string the application will send to the BigQuery project. The last line of submitData()
google.script.run.withFailureHandler(alertString).withSuccessHandler(callBack).returnFormParams(queryString, arrayParam);
at line 256 matters. The last method of the statement calls function returnFormParams() in Code.gs, which returns a result set as a parameter. The withSuccessHandler method of line 256 then calls function callBack() in BigQueryDemoApp.html, passing to callBack() the result set returnFormParams() itself returned. The callBack() function then writes the result set data to the form. At line 31, the <SCRIPT> block declares array DDLARRAY globally, to make it visible to all JavaScript functions within the file.

The call to function submitData() in BigQueryDemoApp.html not only starts the process that places the BigQuery result set on the form, it starts the process that places that result set on the Google spreadsheet. The call to submitData() calls function returnFormParams() of code.js. Function returnFormParams() calls function runQuery(), passing to it the finished BigQuery query string assembled all the way back in submitData() on BigQueryDemoApp.html. Function runQuery() sends the query string to BigQuery, receives the result set, and places that result set on the spreadsheet. Lastly, runQuery() returns the result set to returnFormParams() which then returns it to function submitData() in BigQueryDemoApp.html.

This explains the engineering behind BigQuery Demo App. Before use, application BigQuery Demo App needs configuration and customization in file code.js in three places. Part 4 will describe this customization. Part 5 will show how to configure application security and how to distribute the application to the users.