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
28 comments
Hi,
I've set up Awesome Table on my Google Site with the apps script proxy.
Everything is working great (displaying row to specific user) but the user cannot modify his row.
Sharing settings of the Spreadsheets is set to allow a person from the domain to modify the Spreadsheet and I've copy/past the url edit view from Awesome Table and insert it in the Google Site.
Am I missing something?
Source : "In conjunction with a Google Form, Awesome Table can display only the entries submitted by the user and allow them to modify those."
Hello E- Pédagogie,
This article is about displaying different content for every user, so there is nothing you did wrong.
When you give someone edit rights for your spreadsheet, they will be able to edit your spreadsheet but not your Awesome Table directly. You cannot edit the settings of your Awesome Table from somewhere else than awesome-table.com (so you can't display the"edit view" in your website).
If you want users to edit the content of your spreadsheet, I would advise you to follow this tutorial on connecting Awesome Table with a Google Form.
Nicolas
I don't know where else to post my comment -
I want people to be able to view the Awesome Table via a Google Site.
I don't want them to be able to view the spreadsheet that feeds the data to the Awesome Table view, becuase it contains additional information that I don't want them to see.
So having to share the source spreadsheet defeats the purpose.
Is the proxy the only way to avoid this?
Thanks,
Hi Kbella,
When you integrate an Awesome Table view in your website, people can only access to your spreadsheet if you give them permission (if you share the spreadsheet).
So you don't need a proxy to avoid this.
Thomas
Hi,
I'm getting this error message when I test the web app after running the script and deploying.
Invalid argument: url (line 18, file "Code")
I've gone to Script Editor on my Form Responses 1 sheet and copied the proxy script into a new project.
Thanks!
Sam
Hi Sam Haller,
Would it be possible for you to share your spreadsheet at "support@awesome-table.com"? If you have sensitive data, you can make a copy with similar data.
It would be helpful for us to take a look at your code.
Regards,
Thomas
Hi,
I was looking for an article about setting permissions and this is the only one I found, sorry if it's not 100% relevant.
You wrote "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". My sheet is set on private and is shared with specific people only and I would like my Awesome Table to be shared with the same people, however it seems to be open to everyone with a link. Is there a way to set permissions or adopt the permissions from the source sheet?
Thanks for an amazing product,
Nahdi
Hi Nahdi,
I suggest you consult this article or this one. If you want to share your view just with specific people, you just need to select "Off - Specific People". Then you need to fill in the mail of the people you want they have access.
Kind regards,
Thomas
Thank you for the quick reply Thomas.
As I mentioned I already set up the sharing preferences as you suggested, but for some reason could still see the data even when I was logged out, and also after refreshing. Strangely enough, the problem is gone now - perhaps it was a caching issue as I couldn't recreate it.
Actually - now I have the opposite problem: when logged out or when logged to an unauthorized Google account, I do get the log in screen, but the "Log In" button does nothing when clicked on. Tested several times on both Chrome and IE.
Another issue - apparently, Awesome Table first renders the table, and only then checks permissions. On slow connection such as the one I'm currently using, this means that the data is actually shown for a long while until it's being covered by the log in screen. This way unauthorized users can still get a glimpse of some of the data.
Hi Nahdi,
Indeed it's strange. Would it be possible for you to share your Awesome Table view and your spreadhseet at "support@awesome-table.com"? If you have sensitive data, you can make a copy with similar data. Just make sure we have access to your data.
The problem with the login button is now solved and it should work for you.
Kind regards,
Thomas
Thank you Thomas. Log in issue is solved.
Excellent support and incredible product! Thank you!
Hi Nahdi,
We are really glad you like our product!
It's a great satisfaction for us. :)
Kind regards,
Thomas
Dear Awesome Table,
I am very impressed by your product. However, I need to display my newest entries first from a query that I have set up. Is there any way in which I can do this?
Regards,
Simon
Hi Sfaulkner,
You can try to use the "query()" formula and get the results of this formula. But you need to use header = 0 (the third parameter of the formule) otherwise it will concatenate the two first lines and it won't work in Awesome Table.
Kind regards,
Thomas
Dear Thomas,
Thanks for your reply on this. This is an awesome gadget. I realised that it was possible to get my query sorted before passing the data to awesome table. Doing this, means that it is automatically sorted by date and time, so no matter which user is looking at the data, the most recent row appears first. I'm sure as I continue to build a tutoring system around this gadget, I will have other questions..
Thanks
Hi Sfaulkner,
I'm glad you resolved your issue and you like our product!
Please let us know if you have additional questions.
Kind regards,
Thomas
Hey,
Is it possible to use Proxy Level Permissions with the Advanced Summary?
Regards,
Simon.
Hi Sfaulkner,
Indeed it is possible to use a proxy with any type of templates.
Kind regards,
Thomas
Hi Thomas,
Unfortunately, doing this is giving this error.
This also happens when I try to use the proxy row level permissions on another spreadsheet.
Any ideas?
Regards,
Simon.
Is it possible to allow one user to see all the rows and the users to only see their data for themselves?
Hi Sfaulkner,
Sorry for the delay of the response!
Would it be possible for you to share your Awesome Table view, your spreadsheet with your proxy at "support@awesome-table.com"? If you have sensitive data, you can make a copy with similar data. Just make sure you give us access to your data.
Thanks in advance.
Thomas
Hi Angel Christian,
It is definitely possible to allow only one user to see all the data and the rest of users see only their data. You just need to modify the script.
Here is an example.
(line 33, I added a way to check if the connected user is admin / he must have access to everything and line 51 I display all the lines if it is admin)
Kind regards,
Thomas
Hi Thomas,
I have added the script that you supplied. I have also added the user's email who would be the admin to the script but the admin still cannot see it. Do I need to change/edit/save/run this script manually or in any other way to make this happen please? Is there a step that I have missed?
Kind regards,
Angel.
Hi Angel Christian,
Indeed, you need to save an run manually your code in your editor:
Please, contact us at "support@awesome-table.com" if you have additional questions.
Kind regards,
Thomas
Hi Thomas,
I have done that but it still doesn't work. Can you help please?
Thanks,
Angel.
Hi Angel Christian,
Please, share us your view and your spreadsheet at "support@awesome-table.com" and remain us briefly bout your problem.
If you have sensitive data, you can make a copy with similar data. Just make sure you give us access to your data.
Thanks in advance.
Thomas
hi,
i wanted this piece of script should work in sheets (not connecting form), do i need any extra coding or something to change from it.
i got this error while running from other account on opening the sheet
also, how to trigger the script to work on who else open the sheet.
Hello ali ahmed,
We are sorry but your question is outside the scope of our product.
I suggest you consult this community that is more suited to your needs.
Thank you for your understanding.
Thomas
Please sign in to leave a comment.