Spotlight on Awesome Table Proxy
Post date: Apr 09, 2015 12:10:24 PM
You did 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 ?
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 needs not 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 application :
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.
As Awesome Table had reached the Million display per month (1 028 248 for march 2015) and around 1,5% of these are by Proxy users, it becomes important to communicate about this feature.
Set up
As the apps script proxy need to get the email address of the user, it can only be used by Google Apps user inside their own Domain.
The original documentation is accessible here :
Starting today templates, pre-set filters and row selection (q=&c=) are also available while using a Proxy !
However, (and it's important !), if you already use a proxy and you want to start using templates, you must install the new proxy ! You will found it at the same URL.
If you want to ask questions or just exchange ideas about the use of a Proxy, please do it on this post in the G+ Awesome Table Community !
How does it works ? (Technical stuff)
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; }