google-apps-script google-spreadsheet Custom function Permission WorkAround

Question

I whant to go to the spreadsheet and use the function

=index(MYCustomFunctionThatReturnsAnArrayWithTheFolderAndFileNamesFromMyDrive(),1,0).

I would like to have the file or folder name from some item in my drive.

MYCustomFunctionThatReturnsAnArrayWithTheFolderAndFileNamesFromMyDrive()

looks like this:

function MYCustomFunctionThatReturnsAnArrayWithTheFolderAndFileNamesFromMyDrive(){

      var data= [];
  var j =0 , i = 0;

   //  Method: getRootFolder() Return type: Folder
   var rootfolders = DriveApp.getRootFolder().getFolders();

   while (rootfolders.hasNext()) {
     var folder = rootfolders.next();
     var folderName = folder.getName();
     data[j] = folderName;
     j++;
   }

   var files = DriveApp.getRootFolder().getFiles();

   while (files.hasNext()) {
     var file = files.next();
     var fileName = file.getName();
     data[j+i] = [file];
     i++;
   }
     return data; 
}

And by the time I do that, I get the following Error Message: "You do not have permission to call getRootFolder (line 7)."

I have searched around and I do understand that this is due to custom functions are Userless and there fore have no Rights.

Does someone has an Workaround?

I thank in advance.

Regards,

Answer 1

It Worked like a charm! Thank you for pointing out the small letters in the bottom of the page that I have been seekeing for a while but couldnt find.

Here is my working progress code. (dont forget to Set Manualy the range)

function onOpen() {
  SpreadsheetApp.getUi() // Or DocumentApp or FormApp.
  .createMenu('LerDRIVE')
  .addItem('Export Drive', 'exportFromDrive')
  .addToUi();
}

function driveRootList() {

  var data= [];
  var j =0 , i = 0;

   var rootfolders = DriveApp.getRootFolder().getFolders();

   while (rootfolders.hasNext()) {
     var folder = rootfolders.next();
     var folderName = folder.getName();
     data[j] = folderName;
     j++;
   }
   var files = DriveApp.getRootFolder().getFiles();

   while (files.hasNext()) {
     var file = files.next();
     var fileName = file.getName();
     data[j+i] = fileName;
     i++;
   }
//   Logger.log(data[4]);
   return data; 
}


/**
 * Export Array Data from Drive Folder
 */
function exportFromDrive( ) {
  var ui = SpreadsheetApp.getUi(); // Same variations.
  var sheet = SpreadsheetApp.getActiveSheet();  
  var driveData = driveRootList(); 
  // The code below will set range A1 in the first sheet as the active range
  var range = sheet.getRange("B6:B18");
  var data = [];  
  for (i=0; i<driveData.length; i++) {
     data[i] = [driveData[i]];
  }
  //  Logger.log('data height = '+data.length+', data width = '+data[0].length+' and range height is '+range.getHeight()+', range width is '+range.getWidth()+' ... does it fit ?');
  // Record all array data to spreadsheet
  range.setValues(data);
}

Answer 2

Check this link and see the attached screenshot.

You'll have to use the custom menu option.