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. 

Front-end Google BigQuery with a Google Spreadsheet Part 2 - The Application User Experience

_________________________

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 here will describe the application user experience.

Part three 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.
____________________

For this walk-through, we'll assume an authorized user will run the demo application through a URL. Part four will show how to open the form and run the application from the Google Apps Script editor. Part five will show how to configure user authorization and application security.

An authorized user sees a web form

1. The BigQuery application web form
and the associated Google Docs Spreadsheet if that user has the spreadsheet URL:

2. The BigQuery application Google Docs Spreadsheet
Any BigQuery result set on this spreadsheet will mirror the result set on the form. Note that the workflow moves one way, from the form to the spreadsheet. The spreadsheet does not drive changes in the form.

The form has seven dropdowns to filter the BigQuery result set. The Number of Quantiles dropdown stays hidden by default; it becomes visible only for the Quantiles Employee Count pick in the Function for Select Clause dropdown.

The Reset button does three things. It resets all dropdowns, including the hidden Number of Quantiles dropdown, to their default values. It deletes any existing query results on the form. Lastly, it hides the Number of Quantiles dropdown and its associated labels.

When the user clicks the submit button, the application sends the query to BigQuery and shows the result set on the form itself, mirroring both the result set and the user's dropdown picks on the spreadsheet.

The first three functions in the Function for Select Clause dropdown all return single-value result sets. The Quantiles for Employee Count function opens the Number of Quantiles dropdown

3. The QUANTILES EMPLOYEE COUNT function opens
the Number of Quantiles dropdown / labels
and its associated labels. The quantiles function needs a number of quantiles parameter, and for the application, this dropdown ranges from two to one-hundred.

The form does not have a CLOSE button because Google does not support window code closure. The user must close the form from the browser tab.

To use the application, the user simply picks values from the dropdown and then clicks SUBMIT. Both the form and the spreadsheet show the BigQuery result set. For single-value functions, the spreadsheet grays out the quantiles parameter value. If the quantiles function returns an empty result set, the spreadsheet grays out the quantile number cell next to the information message in the quantile value cell. These screenshot pairs show the overall application behavior:

4a. Form - parameters and BigQuery
single-value result set
4b. Spreadsheet - mirrored parameters and single-valued
result set from form. The application grayed out
the number of quantiles cell.
_________________

5a. Form - parameters and BigQuery single-value
function returning no value
5b. Spreadsheet - no-value message and
parameters mirrored from form. 
_________________

6a. Form - parameters and BigQuery
quantiles function result set
6b. Spreadsheet - parameters and BigQuery
quantiles function result set mirrored
from form
_________________

7a. Form - parameters and zero-count BigQuery
quantiles function result set
7b. Spreadsheet - parameters and zero-count BigQuery quantiles
function result set mirrored from form. The application
grayed out the quantile number cell.

This described the application user experience. Part three will explain the engineering behind the application.

Front-end Google BigQuery with a Google Spreadsheet Part 1 - Set up a Google Cloud Platform Account / 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 here will explain how to set up a Google Cloud Platform account, and how to build a basic BigQuery project.

Part two will describe the application user experience.

Part three 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 showed how to set up a BigQuery project and front-end it with an ASP.net application. The customer liked the original ASP.net solution I built but then wanted a front-end that would work on an iPad. Google does not yet have BigQuery Objective C libraries available, but does offer Google Apps Script. With this tool, I built a web app over a Google Docs Spreadsheet. This web app communicates with the Google BigQuery project. Even better, the web app writes to that spreadsheet. An iPad can certainly see a web app through a browser, so the Google Docs Spreadsheet became the solution. This article focuses on the basic technology behind that original Google Apps Script solution.
_______________

In Front-end Google BigQuery with an ASP.net 4.5 application - Part 1, I showed how to set up a Google Cloud Platform account, and how to build a basic BigQuery project. Since then, enough steps, screens, and forms have changed to require a complete rebuild.

Google Cloud Platform offers a wide range of products that move computing from an ownership model to a rental model. In the Platform product line, Google BigQuery handles large data volumes and queries. Although BigQuery lacks some features of products like Oracle or SQL Server, it's perfect for many of the problems traditionally solved by these products, without the hassles. It avoids the hardware and dedicated DBA overhead, at prices competitive with more "established" RDBMS tools. Part one here explains how to set up and load data into Google BigQuery.


Relational database products traditionally group tables, stored procedures, etc. in "databases". In contrast, BigQuery groups tables in a user-defined "dataset" within a user-defined project. Note that a dataset has only tables, not the other machinery RDBMS products provide. The Google Docs Spreadsheet application we'll explore uses this dataset for its queries. I will show how to load a sample CSV file into that dataset. Note that for user-sourced data, BigQuery requires project billing activation.

I started at the Google BigQuery page and I signed in with a Google account. Just above the black up arrow toward the top of the page I clicked "Go To My Console":

1. Set up Google Cloud Platform - first page
To set up the BigQuery project for this article, I clicked "CREATE PROJECT" in the Console

2. Create Project
to open this window:

3. Set up the new BigQuery project
Cloud Platform maps billing data to the paying customer at the project level. I named the project "BigQueryDemoApp", I read / agreed to the terms, and I clicked Create. The Developers Console auto-generated "lexical-emblem-651" as the Project ID. I could have changed this value, but I left it as is. This Project ID value will become important later. The Project Dashboard opened next:


4. Project Dashboard
At the left, I expanded "APIS & AUTH" and I clicked APIs to open the list of Google APIs I could add to the BigQueryDemoApp project:

5.  Add one or more Google APIs to the Google Cloud Platform project
Finally, I toggled off all defaulted picks except BigQuery API, although I could have picked other APIs. The application software will need a similar configuration which I will explain in part three.

In Figure 5, I clicked "Billing & Settings" at the lower left to open this page:

6. Set up project billing
Here, I clicked "Enable Billing" as shown. This opened a page to set up the billing machinery for the "lexical-emblem-651" project. Google Billing sends a confirmation email to the customer contact email address entered in this step. With appropriate changes, we can use this approach to configure a project with any Google Cloud Platform product.

Next, I loaded a data file into BigQueryDemoApp. BigQuery accepts CSV or JSON files at this step. I used zip file "Complete ZIP Code Totals Filefrom the U.S. Census Bureau for the data. It unzips into CSV file "zbp11totals.txt". This 3 meg CSV file has about 39 K rows - enough to show the basics of BigQuery without hitting the structural limits or price points. At the Project Dashboard, I expanded "BIG DATA" and I clicked "BigQuery" to the left of the black arrow here:

7. To load the data, first pick BigQuery at the Developers Console
I clicked the indicated blue down arrow here:


8. Pick the BigQueryDemoApp project
and in the popup, I picked Create new dataset. This opens a popup where I typed the name of the new dataset. I called it "BigQueryDemoAppDS". The new dataset appeared to the left of the page here:


9. Drill down to the BigQueryDemoAppDS dataset
When I hovered the mouse over the dataset, a blue plus appeared; I clicked this plus to open a new popup to begin the data load process.


10. Create and Import data
First, Dataset ID defaulted to


BigQueryDemoAppDS

and for the Table ID, I typed


zbp11totals

Next, I picked source file "zbp11totals.txt", a CSV file. After that, the popup wanted the source data schema, like this


name:string, age:integer

and fortunately, the Census Bureau provided the source ZIP Code Totals data file layout here. This layout maps to a format compatible with BigQuery:

ZIP:string, NAME:string, EMPFLAG:string, EMP_NF:string, EMP:integer, QP1_NF:string, QP1:integer, AP_NF:string, AP:integer, EST:integer, CITY:string, STABBR:string, CTY_NAME:string

I proceeded through the Create and Import steps, and at Advanced Options as shown here I set the "Header rows to skip" value to 1:


11. Configure the BigQuery data import
I accepted the other default values and I clicked Submit.

This completed the Google Cloud account and BigQuery project set-up. Part two will describe the application user experience.