How to fix this authorization Google Apps Script suggest box library issue?

Question

I'm trying to add an autocomplete feature (a Ui in a popup window in a spreadsheet) in my Google Spreadsheet using this Google Apps Script suggest box library from Romain Vialard and James Ferreira's book (modified):

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "my_sheet" ) { //checks that we're on the correct sheet
    var r = s.getActiveCell();
    if( r.getColumn() == 1) {

        var names = ["Adam", "Peter", "Benjamin", "Ceaser", "Prometheus", "Gandi", "Gotama", "Mickey Mouse"];
        var app = UiApp.createApplication();
        var suggestBox = SuggestBoxCreator.createSuggestBox(app, 'contactPicker', 200, names);
        app.add(suggestBox);
        SpreadsheetApp.getActive().show(app);
        var dataCell0 = r.offset(0, 1);

      var dataCell0 = r.offset(0, 1); 
      if( dataCell0.getValue() == '' )
        otherTestTunction();
   }
  }
}

But when I start editing column 1 of "my_sheet" and the Ui box appears, this autorization error happens (In my language it says: "you must have permission to perform this action"):

enter image description here

The documentation says that onEdit() trigger "They cannot access any services that require authentication as that user. For example, the Google Translate service is anonymous and can be accessed by the simple triggers. Google Calendar, Gmail, and Sites are not anonymous and the simple triggers cannot access those services."

Since I'm not using ContactsApp, I suppose that the suggest box library require authorization.

How could I set up an installable on Edit trigger that will ask for authorization? (Could you give me some code sample?)

Here is my test spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0AtHEC6UUJ_rsdFBWMkhfWUQ0MEs2ck5OY1BsYjRSLXc&usp=drive_web#gid=0

Serge insas Serge insas · Accepted Answer · 2013-11-09T19:34:59

Out of curiosity about what you suggested on authorizations needed by the library I made a test on a new sheet, the exact code below asks for spreadsheet access, nothing more.

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
        var names = ["Adam", "Peter", "Benjamin", "Ceaser", "Prometheus", "Gandi", "Gotama", "Mickey Mouse"];
        var app = UiApp.createApplication();
        var suggestBox = SuggestBoxCreator.createSuggestBox(app, 'contactPicker', 200, names);
        app.add(suggestBox);
        SpreadsheetApp.getActive().show(app);
        var dataCell0 = r.offset(0, 1);
}

To handle that you just have to ask your user to run one function in your script (no matter what function) and they will get the following popup :

enter image description here

After this initial step your function will work as expected, ie the Ui will show up.

Beside that, I'm not sure I understand what you want to achieve is feasible, the onEdit trigger fires when the edit is done, meaning after you hit ENTER, then the value in the suggestBox is not taken into account... moreover you have to handler in the UI to do anything so I'm still wondering what do you really expect to do with this code ? (what would be ideal is an onClick event in spreadsheetApp but unfortunately it doesn't exist so far...)

But maybe I missed something obvious.

About installable onEdit that you mention also in your post, you should note that it doesn't need to be authorized by the end user since it would run as YOU, the person that creates the trigger and not the user accessing the SS , therefor accessing your own data and not the user's ones... That might be a non negligible restriction (as Zig mentioned in a comment earlier...)

How to fix this authorization Google Apps Script suggest box library issue?

1 Answers