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?
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 GSuite [G Suite Enterprise, G Suite Business (includes G Suite for Education) or G Suite Basic] user inside their own Domain.
To learn how to use a proxy, we are going to create a simple example : an Awesome Table view 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 options 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
3 comments
Thanks for your information.Sometime we need verious website visit but we can't visit many of website because resricted this website proxy related issues,that time main needable proxy service.My company provide proxy service.
If need proxy service for your laptop or desktop you can try it.Typically confused with VPNs, proxy servers additionally present a tunnelled connection to the Web.
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
Please sign in to leave a comment.