Error When Calling from Spreadsheet, works fine from menu

Question

So I did some work with Google Apps Scripts when they were first released, but I haven't worked with them much lately. This morning, I decided to develop a new script. Everything looks great and I love the new features. However, one very simple thing that worked before is now broken (or I'm just dumb).

When I call a particular function from a menu, it works fine. But when I call it from the spreadsheet directly I get:

You do not have permission to call inputBox (line 17)

What's weird is that I'm not even calling an inputBox. There is a set of events where an input box can be invoked but only if a particular variable isn't set (global) which in this case it is set.

I thought that maybe I just had a bug. So, I looked at my old apps scripts (which at one time were definitely working). And I'm now getting the same problem. So, whatever changed it has broken old scripts as well. For completeness, I'm now including my script code. The functions I'm trying to call directly are Pipeline and Activity:

var webhookurl = "";

var jsondata = [];

function __urlCheck_(str) {
    var v = new RegExp(); 
    v.compile("^[A-Za-z]+://[A-Za-z0-9-_]+\\.[A-Za-z0-9-_%&\?\/.=]+$");
    if (!v.test(str)) {
        return false;
    }
    return true;
}

function __getWebHook_(){
    var newwebhookurl = UserProperties.getProperty('webhookurl_HCR');
    if((newwebhookurl == null || newwebhookurl.length==0) && (webhookurl.length==0 || webhookurl==null)){
        var newwebhookurl = Browser.inputBox("Paste your web hook url here");
        var newwebhookurl = newwebhookurl.replace(/^\s*/, '').replace(/\s*$/, '');
        if('cancel' == newwebhookurl.toLowerCase()){
            Browser.msgBox('Before this connection will work, you will have to enter a webhook url.');
        }else if(newwebhookurl.length==0 || newwebhookurl == null || __urlCheck_(newwebhookurl) == false){
            Browser.msgBox('Sorry, you must specify a webhook url.');
            __getWebHook_();
        }else{
            UserProperties.setProperty('webhookurl_HCR',newwebhookurl);
            webhookurl = newwebhookurl;
        }
    }else if(webhookurl.length==0 || webhookurl==null){
        webhookurl = newwebhookurl;
    }
}


function __ClearWebHook_(){
    UserProperties.setProperty('webhookurl_HCR','');
    webhookurl = '';
    __getWebHook_();
}


function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [{name: "Pipeline", functionName: "__PipelineMenu__"}, 
                     {name: "Activity", functionName: "__ActivityMenu__"},
                     {name: "Reset WebHook", functionName: "__ClearWebHook_"}
                     ];
  ss.addMenu("Heap CRM Reports", menuEntries);

}


function onInstall(){
    onOpen();
}

function __PipelineMenu__(){
    var mydoc = SpreadsheetApp.getActiveSpreadsheet();
    var myapp = UiApp.createApplication().setTitle('Pipeline');
    var mygrid = myapp.createGrid(5, 2);
    mygrid.setWidget(0, 0, myapp.createLabel('Category:'));
    mygrid.setWidget(0, 1, myapp.createTextBox().setName('category'));
    mygrid.setWidget(1, 0, myapp.createLabel('Label:'));
    mygrid.setWidget(1, 1, myapp.createTextBox().setName('label'));
    mygrid.setWidget(2, 0, myapp.createLabel('User:'));
    mygrid.setWidget(2, 1, myapp.createTextBox().setName('user'));
    mygrid.setWidget(3, 0, myapp.createLabel('Type:'));
    var lbt = myapp.createListBox(false).setName('type');
    lbt.setVisibleItemCount(1);
    lbt.addItem('Lead');
    lbt.addItem('Opportunity');
    lbt.addItem('Customer');
    mygrid.setWidget(3, 1, lbt);
    mygrid.setWidget(4, 0, myapp.createLabel('Kind:'));
    var lbk = myapp.createListBox(false).setName('kind');
    lbk.setVisibleItemCount(1);
    lbk.addItem('Expected Value');
    lbk.addItem('Value');
    lbk.addItem('Count');
    mygrid.setWidget(4, 1, lbk);
    var panel = myapp.createVerticalPanel();
    panel.add(mygrid);
    var button = myapp.createButton('Submit');
    var handler = myapp.createServerClickHandler('__processPipeline');
    handler.addCallbackElement(mygrid);
    button.addClickHandler(handler);
    panel.add(button);
    myapp.add(panel);
    mydoc.show(myapp);
}


function __ActivityMenu__(){
    var mydoc = SpreadsheetApp.getActiveSpreadsheet();
    var myapp = UiApp.createApplication().setTitle('Activity');
    var mygrid = myapp.createGrid(7, 2);
    mygrid.setWidget(0, 0, myapp.createLabel('Category:'));
    mygrid.setWidget(0, 1, myapp.createTextBox().setName('category'));
    mygrid.setWidget(1, 0, myapp.createLabel('Label:'));
    mygrid.setWidget(1, 1, myapp.createTextBox().setName('label'));
    mygrid.setWidget(2, 0, myapp.createLabel('User:'));
    mygrid.setWidget(2, 1, myapp.createTextBox().setName('user'));
    mygrid.setWidget(3, 0, myapp.createLabel('Date:'));
    mygrid.setWidget(3, 1, myapp.createDateBox().setId('date'));
    mygrid.setWidget(4, 0, myapp.createLabel('Range:'));
    var lb = myapp.createListBox(false).setName('range');
    lb.setVisibleItemCount(1);
    lb.addItem('Week');
    lb.addItem('Month');
    lb.addItem('Year');
    mygrid.setWidget(4, 1, lb);
    mygrid.setWidget(5, 0, myapp.createLabel('Type:'));
    var lbt = myapp.createListBox(false).setName('type');
    lbt.setVisibleItemCount(1);
    lbt.addItem('Lead');
    lbt.addItem('Opportunity');
    lbt.addItem('Customer');
    mygrid.setWidget(5, 1, lbt);
    mygrid.setWidget(6, 0, myapp.createLabel('Kind:'));
    var lbk = myapp.createListBox(false).setName('kind');
    lbk.setVisibleItemCount(1);
    lbk.addItem('Expected Value');
    lbk.addItem('Value');
    lbk.addItem('Count');
    mygrid.setWidget(6, 1, lbk);
    var panel = myapp.createVerticalPanel();
    panel.add(mygrid);
    var button = myapp.createButton('Submit');
    var handler = myapp.createServerClickHandler('__processActivity');
    handler.addCallbackElement(mygrid);
    button.addClickHandler(handler);
    panel.add(button);
    myapp.add(panel);
    mydoc.show(myapp);
}


function __processActivity(inputvalues){
    var category = inputvalues.parameter.category;
    var label = inputvalues.parameter.label;
    var user = inputvalues.parameter.user;
    var date = inputvalues.parameter.date;
    var range = inputvalues.parameter.range;
    var type = inputvalues.parameter.type;
    var kind = inputvalues.parameter.kind;
    var answer = Activity(kind,type,date,range,category,user,'',label);
    var mysheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    mysheet.getActiveCell().setValue(answer);
    var app = UiApp.getActiveApplication();
    app.close();
    return app;
}

function Activity(kind,type,date,range,category,user,mylabel,label){
    var kind = kind || '';
    var type = type || '';
    var category = category || '';
    var user = user || '';
    var mylabel = mylabel || '';
    var label = label || '';
    var range = range || '';
    var date = date || '';
    var data = __getData_('activity ' + kind,category,user,label,mylabel,date,range);
    var type = type.toLowerCase();
    if(type=='lead' && data.hasOwnProperty('lead')==true){
        return data['lead'];
    }else if(type=='customer' && data.hasOwnProperty('customer')==true){
        return data['customer'];
    }else if(type=='opportunity' && data.hasOwnProperty('opportunity')==true){
        return data['opportunity'];
    }else{
        return 0;
    }
}

function Pipeline(kind,type,category,user,mylabel,label){
    var kind = kind || '';
    var type = type || '';
    var category = category || '';
    var user = user || '';
    var mylabel = mylabel || '';
    var label = label || '';
    var data = __getData_('pipeline ' + kind,category,user,label,mylabel);
    var type = type.toLowerCase();
    if(type=='lead' && data.hasOwnProperty('lead')==true){
        return data['lead'];
    }else if(type=='customer' && data.hasOwnProperty('customer')==true){
        return data['customer'];
    }else if(type=='opportunity' && data.hasOwnProperty('opportunity')==true){
        return data['opportunity'];
    }else{
        return 0;
    }
}

function __processPipeline(inputvalues){
    var category = inputvalues.parameter.category;
    var label = inputvalues.parameter.label;
    var user = inputvalues.parameter.user;
    var type = inputvalues.parameter.type;
    var kind = inputvalues.parameter.kind;
    var answer = Pipeline(kind,type,category,user,'',label);
    var mysheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    mysheet.getActiveCell().setValue(answer);
    var app = UiApp.getActiveApplication();
    app.close();
    return app;
} 

function __getData_(mlist,mcategory,muser,label,mlabel,mdate,mrange) {
    var addon = '';
    var mlist = mlist || '';
    var mcategory = mcategory || '';
    var muser = muser || '';
    var label = label || '';
    var mlabel = mlabel || '';
    var mdate = mdate || '';
    var mrange = mrange || '';
    if(mcategory.length>0){
        addon = addon + "&category=" + encodeURIComponent(mcategory);
    }
    if(muser.length>0){
         addon = addon + "&user=" + encodeURIComponent(muser);
    }
    if(mlabel.length>0){
         addon = addon + "&mylabel=" + encodeURIComponent(mlabel);
    }
    if(label.length>0){
        addon = addon + "&label=" + encodeURIComponent(label);
    }
    if(mdate.length>0){
         addon = addon + "&date=" + encodeURIComponent(mdate);
    }
    if(mrange.length>0){
        addon = addon + "&range=" + encodeURIComponent(mrange);
    }
    __getWebHook_();

    if(webhookurl.length==0 || webhookurl == null){
        return [];
    }
    addon = "&list=" + encodeURIComponent(mlist) + addon;
    for(i=0;i<jsondata.length;i++){
        if(jsondata[i]['addon'].toLowerCase() == addon.toLowerCase()){
            return jsondata[i]['json'];
        }
    }

    var url = webhookurl + addon;
    var response = UrlFetchApp.fetch(url);
    if (response.getResponseCode() == 200) {
        var newid = jsondata.length;
        jsondata[newid] = {};
        jsondata[newid].json = Utilities.jsonParse(response.getContentText());
        jsondata[newid].addon = addon.toLowerCase();
        return jsondata[newid].json;
    }
}

score 2 · Accepted Answer · edited May 23 '17 at 11:52

2

This is the same as:

No permission to call msgBox in Google Apps Scripting

Custom functions will do nothing but return a value to a cell in which they reside unless they return an array which may affect contiguous cells.

A custom function cannot initiate a input box(Browser.inputBox())

A custom function is a function entered as all or part of a formula in a spreadsheet cell that calls a function from the script editor.

As you stated: 'It used to work' but has been disabled

edited May 23 '17 at 11:52

Community

1
1

answered May 27 '12 at 18:30

ScampMichael

3,688
2
16
23

Hi ScampMichael. Thing is that as I explained in my question, I'm only asking for an input box if a particular variable is empty. And I've tested it when it is not. So, I shouldn't be getting that error because it isn't calling for an InputBox. – tazz_ben May 27 '12 at 19:03
Hi tazz_ben, It seems that the error is being invoked not because you called the input box but because you can. In other words, at compile time it is asking itself, 'is it possible that his script is doing something that it shouldn't be?' and if so throw an error and don't run the script. If your not using the input box you might try commenting the input box and message boxes out before running the script and see if that works. – ScampMichael May 27 '12 at 19:20
I've written a workaround where I don't have the possibility of calling the input or or msgBox. But the problem turns out to be more fundamental. It appears that when called from custom function, global variables are not set and you get empty values if you call UserProperties.getProperty directly. This basically means you can't store data for the custom function, is that really the case? That makes scripts far less useful then they use to be. – tazz_ben May 27 '12 at 20:06
Ok! So, I found that ScriptProperties do work with custom functions, so I've basically loaded everything into a ScriptProperty that I need as a global. This works but makes me nervous because I was never clear on the scope of ScriptProperties. Ie. if I publish my script (which for me is the whole point), am I publishing my script property values? – tazz_ben May 27 '12 at 20:59
You can't access UserProperties because because custom spreadsheet functions can't access any user-specific data. Script properties are shared among all users, and so can be accessed. I don't believe they are copied when you publish to the gallery. – Eric Koleda May 30 '12 at 16:21

Error When Calling from Spreadsheet, works fine from menu

1 Answers1