javascript - How to get the file URL from file name in Google Sheets with correct Authorization via custom function/script

Question

I would like to create a custom function that pulls a Drive URL from a file name in Google Sheets.

So, using the code below:

1. If I have a valid file name in cell A1

2. The function =getFile(A1) would return the URL

   • When I run my script from within the script editor, the return value works.
   • When I run the function getFile() from within my sheet, I get the error below.

My code:

function getFile(cell) {

  var filename = encodeURI(cell);

  var url = "https://www.googleapis.com/drive/v3/files?fields=files(id,name)&q=name+contains+'" + filename + "' and trashed=false";
  var params = {
    method: "GET",
    headers: {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
    muteHttpExceptions: true
  };
  var res = UrlFetchApp.fetch(url, params).getContentText();
  var json = JSON.parse(res);
  return res; // outputs response below
  if(json){
    var objFiles = json.files[0];
    var fileID = objFiles.id
    var resURL = "https://docs.google.com/spreadsheets/d/" + fileID;
    Logger.log(resURL);
    //return resURL; // only works when run within script editor
  }

}

Error:

"{
 "error": {
  "errors": [
   {
    "domain": "global",
    "reason": "authError",
    "message": "Invalid Credentials",
    "locationType": "header",
    "location": "Authorization"
   }
  ],
  "code": 401,
  "message": "Invalid Credentials"
 }
}
"

I'm guessing something's wrong with my Auth token. Can someone direct me to resolving this? Thanks in advance!

Answer 1

Custom functions runs as if run by a anonymous animal(user). ScriptApp.getOAuthToken will return a anonymous token without the required scopes. What you're attempting is not possible, unless the file in question is public.

References:

• Custom functions permissions
• Custom functions access services