CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-drive-api
Arthur
Arthur

Reputation: 1548

Access Google Drive from Sheets: authorization

I have found a code excerpt on the net that gets file metadata from Google Drive. I added it to a custom menu in Google Sheets but it returns 403 from both the script editor and Sheets. It asked for authorization the first time as an unverified app, never again.

I tried to figure it out but I got totally confused about the authorization part. The code retrieves and sends an Authorization header, but where the token comes from in Sheets? Shouldn't it be created automatically between Google services? And how do I specify the scopes Drive needs? I cannot find any examples for Sheets scripts doing authorization, only NodeJS, etc. clients.

I accidentally found the Advanced Google Services menu in the script editor and enabled both Sheets and Drive API. There is no related custom GCP project (This script has an Apps Script-managed Cloud Platform project).

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('First item', 'menuItem1')
      .addToUi();
}

function menuItem1() {
  getFileMetaData("1GrX86wNWbEBf3Or90bXCyd31w-yVINiM"); // hardcoded for now
}

function getFileMetaData(fileID) {
  var api = "https://www.googleapis.com/drive/v2/files/" + fileID;

  var params = {
    method:"get",
    contentType: "application/json",
    headers: {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
    muteHttpExceptions:true,
  };

  var json = UrlFetchApp.fetch(api, params).getContentText();
  var meta = JSON.parse(json);
  Logger.log(json);
  SpreadsheetApp.getUi().alert(json);
}

Result:

{
 "error": {
  "errors": [
   {
    "domain": "global",
    "reason": "insufficientPermissions",
    "message": "Insufficient Permission: Request had insufficient authentication scopes."
   }
  ],
  "code": 403,
  "message": "Insufficient Permission: Request had insufficient authentication scopes."
 }
}

If I remove the Authorization header:

{
 "error": {
  "errors": [
   {
    "domain": "usageLimits",
    "reason": "dailyLimitExceededUnreg",
    "message": "Daily Limit for Unauthenticated Use Exceeded. Continued use requires signup.",
    "extendedHelp": "https://code.google.com/apis/console"
   }
  ],
  "code": 403,
  "message": "Daily Limit for Unauthenticated Use Exceeded. Continued use requires signup."
 }
}

Upvotes: 0

Views: 240

Answers (1)

dwmorrin
dwmorrin

Reputation: 2734

... how do I specify the scopes Drive needs?

You need to have a call to DriveApp somewhere in your code. Just putting it in the comments works. I have used

// DriveApp.getStorageLimit();

in the past to trigger Drive authorization when I want to use the API via UrlFetchApp.

You can check what scope are already authorized in the online editor by opening the dialog found at File > Project Properties. Click the Scopes tab to see a list of the current authorization status of your script.

Edit:

Another option would be to set the authorization scopes on the manifest file: Setting explicit scopes.

Also, if you were using the Advanced Service instead of calling UrlFetchApp, you wouldn't need to worry about specifying the right scopes.

Upvotes: 3

Related Questions