IssueTracker

For the avoidance of doubt, if I create a function

function myFunction() {
  Library.libFunction1();
}

and then run myFunction, that works fine.

Yes, libFunction1 - library function should be called directly from the menu, not from the upper level script. Currently it works in next way:

-----------LIB-----------
function libMenu() {
  var mySheet = SpreadsheetApp.getActiveSpreadsheet();  
  var menuEntries = [ {name: "libFunction1", functionName: "Library.libFunction1"},
                      {name: "libFunction2", functionName: "libFunction1"}
                     
                    ];
  mySheet.addMenu("Library Functions", menuEntries);
}
-----------LIB-----------

-----------CLIENT-----------
function onOpen() {
  Library.libMenu();
}

function libFunction1() {
  Library.libFunction1();
}
-----------CLIENT-----------

but client script should contain only onOpen function.

[Comment deleted]

When you plan to fix it? It's very important issue. Thanks.

Please fix!

Yes, a big inconvenience. One way to circumvent it could be to call a master libfuntion in the client, with a parameter:

function libFunction1(menuitem) {
  Library.libFunction1(menuitem);
}
 but... menuitems do not accept parameters.

So, my second choice will be to populate my client script (which should ideally only contain onOpen) with a number of "generic" menu function triggers, some of them just sitting there, ready for future expansions, hopefully enough so that I will never have to alter the client script when i expand the library with more menu items, and at the same time not having misleading names (not descriptive either, unfortunately):

function libFunction1() {
  Library.libFunction1();
}

function libFunction2() {
  Library.libFunction2();
}

...

function libFunction20() {
  Library.libFunction20();
}

Instead of passing a functionName, why not passing the function itself?
e.g.,
-----------LIB-----------
function libMenu() {
  var mySheet = SpreadsheetApp.getActiveSpreadsheet();  
  var menuEntries = [ {name: "menuItem1", handler: menuItem1},
                      {name: "libFunction2", handler: function() {menuItem2(3);}}
                    ];
  mySheet.addMenu("Library Functions", menuEntries);
}

function menuItem1() {
}

function menuItem2(someArg) {
}
-----------LIB-----------

-----------CLIENT-----------
function onOpen() {
  Library.libMenu();
}
-----------CLIENT-----------

Without this feature, libraries are really nobbled!  

+1 for passing the function itself!

I suspect these 2 are the same issue: https://code.google.com/p/google-apps-script-issues/issues/detail?id=1346

Please Fix it.

I actually thought cooledco's suggestion was a current solution to the problem, but alas it is just a suggestion to how a possible solution might be implemented.

+1 for the idea though. This passing of function "names" seems very old skool!?

A year later and this has been ignored?? What is the point of having a global library if you have to write a freaking wrapper for it each time?! The mind boggles. Please fix this, it should be callable from both the spreadsheet cells AND any buttons (images -> via assign script) on the spreadsheet that has the library included.

I, too, have been forced to constantly edit my client scripts any time I want to make changes to the Library. I came across this post as I was looking for a solution, but it looks like there really is no convenient solution at the moment. That would be very nice if there were.

+1 for calling a library function directly from the menu

to me works SpreadsheetApp.getUi().createMenu('MenuName').addItem('ItemName', 'Library.libFunction1').addToUi();

Here is a workaround that allows you to call any library function if you paste in this one generic wrapper function.  Then you can call this from the spreadsheet.

For example, if I had a library called MyLib with a function add(x, y) (pretend x is in cell A1 and y is in cell A2) I could call it like this:
=LIB_FUNC("MyLib", "add", A1, A2).

It's a little ugly but at least allows me to only have to paste this one function and then access any library function.  Note that this depends on undocumented structure of the "this" object that is in scope when calling the wrapper function.  Small chance this could break over time.  Might see if I can publish this as an add on.

function LIB_FUNC(libraryName, functionName) {
  var result;
  var lib = this[libraryName];
  var extraArgs = [];
 
  if (lib) {
    var func = lib[functionName];
   
    if (func) {
      if (arguments.length > 2) {
        extraArgs = Array.apply(null, arguments).slice(2);
      }
     
      result = func.apply(this, extraArgs);
    } else {
      throw "No such function: " + functionName;
    }
  } else {
    throw "No such library: " + libraryName;
  }
 
  return result;
}

Just in case people are still looking for solutions.
function TESTF() {
  return myLib.TESTF.apply(null,arguments);
}
Can be applied in a for loop for every method on myLib.. I use this in all of my scripts so I just have a 5 line script in the document, add the library, and walah, the library is directly part of this script as if I was "using namespace myLib;"

[edit] Also, this means that spreadsheets can call those functions as well as if they were actually written in this script.

Hope it helps.

Comment 20 explanation is a little opaque to me. Can anyone elaborate?

Seems silly that library functions aren't exposed to the documents once included. Makes them somewhat pointless if a wrapper must be included in every document, or unless #20 is on to something with a generic 5 line wrapper.

I am also interested in a deeper explanation of comment #20

I understand she/he meants using a loop to navigate all methods in library, in order to easily define a new function inside the sheet-bounded-script for each library method.

But, how can we loop every method on the library?  And how can we define a new function inside that loop, which takes its name from the library?

same here, please explain if possible !

As far as I can tell, calling library functions from menus as in SpreadsheetApp.getUi().createMenu('MenuName').addItem('ItemName', 'Library.libFunction1').addToUi() has been supported since 2013. I don't see any additional work that needs to be done here.

Judging by the comments in the thread, I believe the expectation is that functions in a Library could be called from not only a menu item, but also from the Sheet's own script, and even directly from a formula entered in a cell, just like any other function local to the Sheet's script would be called, only difference being that the referred function would be in the Library.

This is indeed a big problem. After DAYS trying to use IMPORTRANGE and ARRAYFORMULA for generating a column which repeats a "title" from other cell beside every imported line, I thought that the only way I could do it would be using Apps Script. So I went on trying to learn the whole thing, having to learn JavaScript (it's a shame that Google's documentation *requires* that you know JS previously), and then I find that it also will not be possible.

At least if we could use the built-in formulas in the scripts, ok, we might deal with IMPORTRANGE's limitations. But this is another big issue in Google Sheets. A workaround, please...

[Comment deleted]

I'm totally frustrated, I have GAS code used over 5 years to support all CRM relations in our non government organisation. I developed and used libraries which build over Spreadsheets object data layer, so all application functionality  is accessing data from sheets via that layer. It means that it is simple to add, modify, reallocate columns or change cell functions without code changes or knowledge of column names, only change in configuration sheet is necessary. And now without possibility to open second sheet form code in first sheet a have to again merge all data to one big spreadsheet, which is almost impossible due to limitation in cut & paste functionality. Thankx google... I'm going to move to other solution where I will have lifecycle in code in my hands :-(

Why would Google change something so important like this at such a late stage of the product life cycle? This is surely stuff that should have been decided on before release and before developers made all their code work using these methods?

Just ran into this too and found this thread after going down many rabbit holes. Has anyone found a workaround? This seems like a major problem and it's a bit irritating that the maintainers of this code would break their API without even bothering to update the docs.

The workaround is to use a script with a time based trigger to copy/paste your data to update. You can no longer use a custom function that auto updates with a trigger such as "on change". This works, unless you are doing very frequent triggers and start receiving the notice "using too much cpu time for one day" and then your script gets killed until the next day.

Same here no permission to SpreadsheetApp.openById("abc1234567");

Please fix this!

Great, just wasted one day to write scripts which worked fine in script editor. But of course do not work when called from Android app, which I started developing today.

Why did you not even mention that in your documentation?
https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app#openById(String)

[Comment deleted]

Same problem trying to capture the "Edit Response" link from a Goofle Forms survey, along with the respondent's answers, in the vary Goofle Sheet that the Form is linked to.

Bad enough that this feature change wasn't preceded by any design-change logic.  Even worse that it was not documented ... EVEN AFTER THE FACT.

Completely unprofessional, Goofle.

if i have 2 spreadsheet where the owner is same, why is a security problem when use  SpreadsheetApp.openById() ???.

And I agree with before post (#35): "Bad enough that this feature change wasn't preceded by any design-change logic.  Even worse that it was not documented ... EVEN AFTER THE FACT."

And should be careful with these types of changes that affect existing developments. In my recent experience once it allowed me to insert a row using "SpreadsheetApp.openById ()" and then gave the error "You don 't have permission to call openById". It is unclear too!

Not work in mode read only, I need fast resolution for this issue!

I apologize for the long delay in updating the documentation. I've added a note here:

https://developers.google.com/apps-script/guides/sheets/functions#using_apps_script_services

Ok, I use the method get, in read only mode but not work fine, have same error. Have u check?

"Read only (can use most get*() methods, but not set*()).
Cannot open other spreadsheets (SpreadsheetApp.openById() or SpreadsheetApp.openByUrl())."

ok, i also wasted few hours trying to figure it out. and now i have no idea how to prepare a list of the sheetnames from the source spreadsheet.
any workaround?

So what is the workaround? My code references the last edited cell so a menu item really doesn't work for me

without using openById, how can I write data from one spreadsheet to another? Do tell me to do it manually.

By the way, I got this issue this week, it was working fine at the beginning of this week. Is that the security reasons means when google found my project is almost become a working system, then I'm not allow to use openById.
And yesterday, I'm good to save data between different files, but not today!!

Until last week I was able to use SpreadsheetApp.openByUrl after a user authorized an ImportRange function call to the same url. This now seems broken.

Given that a user has explicitly provided authorization for access from one spreadsheet to the other, shouldn't this be allowed?

Iam cannt believe this.

Yesterday ( 09-13-2017 )  SpreadsheetApp.openById still work it!
Today 09-14-2017, not any more.

the sentences " SpreadsheetApp.openById " , is inside a Library ! that Library, i set to run with my user (owner) and it can call for anyone ...

Why it say me that i dont have permission ??
It is castratofic and it is shame ... i cannt relies in my code anymore????

Is there any update on the issue? I am working on an alternative, but I would like to know if this is going to be fixed or if it is not going to be supported anymore.

To renew permissions with some steps:

1) Copy all your code to an editor (Ex: Notepad)
2) Go File > Delete Preject (on GAS window of the working project)
3) Return to the related Spreadsheet or Form and open Script Editor again. (A new project will be opened)
4) Paste your code in the new project
5) Run the function that access the external spreadsheet and GAS will ask again for permission to access.

If it happened on triggers, just remove the trigger and insert again that GAS will ask for permission.

Save!

Peace for all!

Racional Culture, the knowledge of the origin of the humanity. Research and get your own proves.

Hello,

Is there any update on this issue? Inside my onEdit function, I need to call other file by using openById. It runs perfectly in run or debug mode. However if I edit something on the spreadsheet, I'm getting You do not have permission to call openById. This was working perfectly previously.

Is this issue going to be resolved? I need an answer please. Otherwise what is the best solution to read other spreadsheet during onedit? Appreciate any feedback.

Any work around???

Hi, i'm using var document = SpreadsheetApp.openById(getDocumentId()); upon a menu action and getting You do not have access to perform that action. Please ask the owner of this item to grant access to you

Could you share how did you work around it?
Thanks

Last week, I made sure that openByUrl was working before I go deep in making project and it did. I dont know what the hell is going on! Plus, as commented by others, how can this be a security issue for depreciating this important feature?

ARREGLEN ESTO GOOGLEEEEEEEEEEEEEEEEE

Cuál es la solución a esto?