You already know how to use Awesome Table in general. But did you know that Awesome Table can be set up to display specific rows of your Google Sheet, depending on the connected user using a Proxy?
info This article is only for Google users.
not_interested Proxy & Row-level permission will not work when used with queries.
Why use a Proxy?
In a classic use case, Awesome Table fetches the data directly from Google Sheets and people are allowed or not to view data based on the spreadsheet sharing settings.
When using a Proxy, Awesome Table will not query the spreadsheet, but the Proxy. The Google Sheets does not need to be shared with the users, only with the account that runs the Proxy.
A major use of Proxy is to only display specific rows of your Google Sheets, depending on the connected user.
It has a lot of applications:
- In conjunction with a Google Form, Awesome Table can display only the entries submitted by the user and allow them to modify those.
- A list of web links, useful applications, can be customized depending on the user, and their permissions.
- Display information accessible only to some people, and keep it all in one spreadsheet.
Moreover, if you are interested in Google Apps Script, you can completely customize the Proxy, and create a lot of different ways to collect data and to assign a user view permission on these data. You can even imagine fetching data not from Google Sheets, but from a web service.
Set up
warning As the apps script proxy needs to get the email address of the user, it can only be used by Google Workspace [Google Workspace Enterprise, Google Workspace Business (includes Google Workspace for Education) or Google Workspace Basic] users inside their own Domain.
To learn how to use a proxy, we are going to create a simple example: an Awesome Table app linked to a Google Form where users will be the only one able to see their submissions.
- Create a Google Form. Make sure that you have these optional settings selected in your form settings: Collect email address and Restrict to your domain
- Create an Awesome Table with your form's responses spreadsheet as the source
- Make a copy of this script in your drive
- Run the doGet() function once an authorize the script
- Deploy as a Web App (Publish > Deploy as a Web App).
Make sure that the script will be executed as you and that the setting Who has access to the app is set to Anyone. This does not mean anyone can have access to your data as the data is filtered based on the logged in user:
- Select Deploy
- Copy the web app url
- Paste it on your Awesome Table settings (Advanced Parameters > Apps Script Proxy Url)
- In the second row of the email column in your response spreadsheet write "Permissions"
If you want one entry to be accessible to several users, separate their email by a ',' in the email column.
How does it work?
Awesome Table will call the script and pass it the following parameters:
- parameter.url: URL of the spreadsheet
- parameter.sheet: Name of the data sheet
- parameter.range: Data range in this sheet
- parameter.callback: the function called to return the result
If a template is used, some other parameters are given:
- parameter.templateSheet: Name of the template sheet
- parameter.templateRange: Range of template
If a user is viewing your App with a browser that has third party cookies disabled, another parameter is also passed :
- parameter.access_token: The token of the user that is viewing the application
The script will detect the current user logged with his google Apps account with:
1 |
var currentUser = getCurrentUser(e); |
This will determine who is viewing the application either directly through Apps Script with `Session.getActiveUser().getEmail()` or by validating the passed access token.
It's important to note that it only works with users of Google Apps domain hence the limitation in using a proxy within Google Apps domains.
Our version of the proxy takes this information, accesses the spreadsheet, then builds a JavaScript object later used by Awesome Table to build a dataTable. The template information is returned in the same exact way as any other dataTable.
The object must be of the following form:
1 2 3 4 5 6 7 8 9 |
var dt = {
cols: [{id: 'A', label: 'Task', type: 'string'},
{id: 'B', label: 'Hours per Day', type: 'number'}],
rows: [{c:[{v: 'Work'}, {v: 11}]},
{c:[{v: 'Eat'}, {v: 2}]},
{c:[{v: 'Commute'}, {v: 2}]},
{c:[{v: 'Watch TV'}, {v:2}]},
{c:[{v: 'Sleep'}, {v:7, f:'7.000'}]}]
};
|
It's important to note that the 'id' of the 'cols' field must be column name in A1 notation (like 'A', 'B', 'AC'). Because internally, Awesome Table uses these ids for pre-set filters and direct row links (q=&c=).
Here is a little Apps Script snippet that convert a numeric index into column A1 notation (1 -> A, 26 -> Z, 27 -> AA):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
function numToA(num){
var a = '',modulo = 0;
for (var i = 0; i < 6; i++){
modulo = num % 26;
if(modulo == 0 {
a = 'Z' + a;
num = num / 26 - 1;
}else{
a = String.fromCharCode(64 + modulo) + a;
num = (num - modulo) / 26;
}
if (num <= 0) break;
}
return a;
}
|