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;
}
|
Comments
8 comments
This worked great for us in the spring. It did not work late fall. I was surprised to check today to see that it had been "updated" 16 days ago. I followed the above steps to update our set up, but it still does not work. I get the attached error message.
Hello Chris Chamberlain,
Do you have some issues with your internet connection? Indeed, if your Awesome Table view has a lot of data or a lot of formula to display, you could have this problem.
Kind regards.
Thomas
Hello!
Is there any way to implement this without breaking templates already implemented on the table?
Every time I apply the script to a table that has the "People Directory" template implemented, it no longer displays properly- it only shows the "Directory" sidebar and the profile section goes missing.
Hello Adrian,
Could you create a ticket and detail your problem in it? It will be easier to discuss with you about your issue.
Thanks in advance.
Thomas
So, to verify, they must be logged in as a GSuite customer?
If our organization does not currently subscribe to GSuite, is there an alternative method to do the same thing?
Hello Nick,
It is outside our scope. So, I suggest you contact Gsuite support.
Thank you for your understanding.
Thomas
There is a workaround to use proxy row-level permissions AND Queries at the same time: is to apply the query on the sheet and not on the Awesome Table. You can do this using the QUERY formula of google sheets.
Hello Good Morning
I have used the proxy and all is working however I wish to make so that a particular use can view all content. How can get the script to read for lets say Column 5 instead of Column 2 is that possible
In column 5 for example I will pull the email address from column two (dwiz@domain) and combine it through combining to read (dwiz@domain,phs@domain) where phs@domain will be used in a formula array to add to every email coming from column 2
Please sign in to leave a comment.