1

I created a custom script to be run from google sheets which I need to create a file. The script uses the following code excerpt:

  /*
Custom function to call IEXAPI IEXkeystatearningsdate 
@customfunction
*/

function IEXkeystatearningsdate(inputsymbol, stat, version) {

  if (version == "Sandbox" || version == "sandbox")
    var url="https://sandbox.iexapis.com/stable/stock/"+inputsymbol+"/stats/"+stat+"?token=xyz";
  else
    var url="https://cloud.iexapis.com/stable/stock/"+inputsymbol+"/stats/"+stat+"?token=xyz";
  
  var response=UrlFetchApp.fetch(url);  //Call REST API
  var json=response.getContentText();   
  var data = JSON.parse(json);          //Parse into JSON object
  var d = new Date();
  var n = d.toLocaleString();
  var fn = "IEXkeystatearningsdate_" + n;
  DriveApp.createFile(fn,inputsymbol, MimeType.CSV);
  return (data);
}

However, I receive this message:

"Exception: You do not have permission to call DriveApp.createFile. Required permissions: https://www.googleapis.com/auth/drive (line 20)"

When I run this script directly from the script editor, I don't receive this message.

This is my manifest file:

{

"oauthScopes": [
    "https://www.googleapis.com/auth/drive"
  ],

  "timeZone": "America/Los_Angeles",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8"
}

I do not use G-Suite. I only use google/sheets for my personal use. The OAuth FAQ says this call should be allowed for personal use. Can someone help me with what I need to do to get this to work?

Sam
  • 11
  • 2
  • First post all your code we need to see how you are authorizing. Second logout your user and reauthenticate your application im betting you change the scope in your code but have not requested consent of the user again for the new scope. – Linda Lawton - DaImTo Sep 04 '20 at 06:12
  • Are you running the script `onEdit`? – ziganotschka Sep 04 '20 at 09:25
  • @DaImTo - Added full copy of function now. Regards to logging out, not sure what this means other than signing out of my chrome browser and signing back in. I did this and error still exists. – Sam Sep 05 '20 at 17:30
  • @ziganotschka - not running onEdit. I did not know about this until I saw your post. – Sam Sep 05 '20 at 17:31

2 Answers2

0

From your error description I can assume that you are running your script on a simple onEdit trigger.

Simple onEdit trigger cannot access services that require authorization.

See restrictions.

For you it means that you can perform to DriveApp on simple trigger.

Solution:

Use an Installable trigger instead.

To do so:

  • Rename the function onEdit() to something else that is not a key word
  • Go on Edit -> Current project's triggers
  • Click on + to create a new trigger
  • Specify the funciton to run
  • Set Select event type to On edit
ziganotschka
  • 25,866
  • 2
  • 16
  • 33
  • Per my comment above, I did not know about triggers. Let me try this next. – Sam Sep 05 '20 at 17:32
  • What is then the alternative of `When I run this script directly from the script editor, I don't receive this message.`? Do you run it as a custom funciton? Or how else do you run it in order to get the error message? – ziganotschka Sep 07 '20 at 07:44
  • 1
    Thanks for responding to my question. I'm running into new problems now. Let me resolve them, then I'll come back here with my results. thank you! – Sam Sep 10 '20 at 07:21
0

Custom functions cannot be used to call services that access personal data:

Unlike most other types of Apps Scripts, custom functions never ask users to authorize access to personal data. Consequently, they can only call services that do not have access to personal data

That, of course, includes DriveApp. And it also includes things like getOAuthToken() (it returns null when called via custom functions), so calling Drive API directly through UrlFetch cannot work either.

You should either install an onEdit trigger, as ziganotschka suggested, or call this function through a custom menu instead of a custom function, so that you're asked for authorization when trying to run it.

Reference:

Iamblichus
  • 18,540
  • 2
  • 11
  • 27