_________________________
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"
.png) |
| 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
.png) |
| 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.