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
and revoke access to the file from inside the Google account, if that access already exists. Then, on the spreadsheet page itself
the Google Docs Spreadsheet file owner should click Share at the upper right to open the Share settings window
and click "x" to delete existing non-owner shares. The first time into the script editor for the application, click the cloud
to open the initial "Deploy as web app" window
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
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:
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
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.
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
Continue and the Request for Permission window
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.
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
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 |
 |
| 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 dialog box |
 |
| 11. The Request for Permission dialog box |
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.