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 link, useful applications, can be customized depending on the user, and its rights.
- 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 to fetch data not from Google Sheets, but from a web service.
Set up
warning As the apps script proxy need 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] user 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 it will be restricted to your domain:
- 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
- parameters.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
The script will detect the current user logged with his google Apps account with:
1 |
var currentUser = Session.getActiveUser().getEmail(); |
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 these information, access the spreadsheet, then build a JavaScript object later used by Awesome Table to build a dataTable. The template information are returned in the same exact way, in another 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 columns name in A1 notation (like 'A', 'B', 'AC'). Because internally, Awesome Table use these id 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;
}
|
