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|
|2. Open the Share|
|3. Click "x" to delete existing non-owner shares|
|4. Click the cloud to open the web app configuration window|
|5. The initial "Deploy as web app"|
window for the application
|6. The configured "Deploy as web app" window|
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|
Execute the web app as: -> me
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|
|9. Add the Google addresses that will receive shares to the application|
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|
|11. The Request for Permission|
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.