Google Script - DriveApp.getFilesByName - Error (No Permission)

Question

I'm trying to call custom function pdfFile from Google Spreadsheets.

function pdfFile(number, revision) {
  // Log the name of every file in the user's Drive.
  Logger.log(number + '_R' + revision + '.pdf');

  var files = DriveApp.getFilesByName(number + '_R' + revision + '.pdf');
  while (files.hasNext()) {
    var file = files.next();
    //var name = file.getName();
    var id = file.getId();
    Logger.log(file.getName() + '|' + file.getId());

    //Return link to PDF file
    return '=HYPERLINK("https://docs.google.com/file/d/'+id+'/edit?usp=drivesdk";"PDF")';
  }
}

It works when I test it in Script Editor manually.

function test_pdfFile() {
  var range = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0].getRange("D2:E2");

  // Returns cell
  var number = range.getCell(1, 1).getValue();
  var revision = range.getCell(1, 2).getValue();

  result = pdfFile(number, revision);
  Logger.log(result);
}

But, when I use custom formula in Spreadsheet, error "You don't have permission to call function DriveApp.getFilesByName (row 5)" comes. I have already switched on Drive API in Resources->Additional Google Functions and in Developer Console. Please, find attached images (sorry, text in Russian). Please, help.

As perfectly explained in the below answer you are calling a secondary function that uses a service that requires authorization and that is not allowed in custom functions. Could you show the code of this "pdfFile()" function. — Serge insas

Mingzhong Mingzhong · Accepted Answer · 2017-07-22T04:27:09

My solution is:
1. create a menu inside onOpen(): SpreadsheetApp.getUi().createMenu('MenuName').addItem('FooName', 'fooFunction').addToUi(); the code generates a 'FooName' menu and makes it corresponding to fooFunction().
2. in fooFunction() or later, call getFilesByName().

Here is my assumption: getFilesByName() is allowed or only allowed when you are generating a new menu or sidebar. It has nothing to do with AuthMode. Under either authorization mode None or Limited, the script works fine.

"You can not call getFilesByName() in custom function" does not mean you can not, when you create a menu and call it from the menu, it is possible then.

The following are other ways I have tried before:
1. use onInstall(), which was proven useless for this problem.
2. call getFilesByName() inside onInstall() or onOpen(), which is not essential.

Some other useful links:
When you are testing your script as an add-on, check the difference between installed and enabled:
https://developers.google.com/apps-script/add-ons/lifecycle#installed_versus_enabled

AuthMode for onInstall():
https://developers.google.com/apps-script/guides/menus

Google Script - DriveApp.getFilesByName - Error (No Permission)

2 Answers