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:
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.
A 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 . . .
and in the script editor, drill down to Resources -> All your triggers
and click
to open the trigger editor here:
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...
to open the Advanced Google Services window:
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()
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 |
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.
A 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 |
 |
| 3. Drilling down to the trigger editor |
 |
| 4. Drilling down to the trigger editor |
 |
| 5. The trigger editor |
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... |
 |
| 7. Pick the BigQuery API within the application |
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.
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.