Running a google script from within a spreadsheet,

Question

I have a spreadsheet which I want only to be modifiable when the user is using custom menu functions (using the .addMenu function). If I set the sheet's sharing permissions so that only I can edit, but everyone else can view, the custom menu is greyed out for anyone logging in.

I tried to work around this by giving the users access rights to the spreadsheet, but then protecting each sheet but the same issue occurs.

Basically I want the user to be able to add data to the spreadsheet but only when using the custom functions. For example when one of my employees wants to place a new order, he must get authorisation from the Chief Accountant to do so. I have made it so that this order request is entered into a form by the user, which then populates the spreadsheet and alerts via email the accountant that there is a new order pending approval. I want the accountant to then open the sheet, and use the menu item 'approve/deny', which will mark the approval column of the specific request as 'approved/denied'. I do NOT want him to be able to edit this column manually.

Basically I think I need the script to be able to be called by the user, but run as myself. Is this possible?

Thanks for any assistance.

Answer 1

You cannot do it by controlling access to the spreadsheet. The best solution for you is to have your accountant NOT opening the spreadsheet at all ! You can embed a HTML form in the email that is sent to the accountant from where he/she can approve/reject the request. To see how you can build such a system, see this video from I/O 2011 http://www.youtube.com/watch?v=96URE_-aj-8

It demonstrates a use case very similar to yours.