Automated OAuth2 token not working - Google Apps Script

Question

I am trying to run a google apps script, in a document, that sends an email with an attached google spreadsheet as an .xlsx file automatically, running every few hours.

Below is the solution that works if I use a manual OAuth2 code coming from the google OAuth2 playground :

function downloadXLS() {
  var AUTH_TOKEN = "xxxx"; 
  var auth = "AuthSub token=\"" + AUTH_TOKEN + "\"";
  var file = Drive.Files.get('xxxx');
  var response = UrlFetchApp.fetch('https://docs.google.com/spreadsheets/d/xxx/export?format=xlsx',{headers: {Authorization: auth}});      
  var doc = response.getBlob();
  app = DriveApp.createFile(doc).setName(file.title + '.xls')
  MailApp.sendEmail("[email protected]", "oh man", " Body", { attachments: app })
}    

To try to auto-generate the authorization token I followed exactly all the steps here:

https://github.com/googlesamples/apps-script-oauth2

Changing on the script : .setClientId('...') .setClientSecret('...') I also put in the URI the the Project Id inside the https://script.google.com/macros/d/myprojectkey/usercallback of the google developer console

But when i run the function makeRequest() it tells me : "Access not granted or expired"

So i wonder which step i missed.

Do you have any clue on what is going on ?

Help is much appreciated, Thanks

Answer 1

You need to do step 2: Direct the user to the authorization URL
When the sidebar loads you will click the link and the Oauth dialog will open. After you allow access you can use the getAccessToken() method.

EDIT: For your specific case you do not need a separate OAuth flow. You can use Apps Script to get the token you need to do that export. As you are already requesting access to drive your token will work for the export call.

function downloadXLS() {

  var file = Drive.Files.get('xxxx');
  var response = UrlFetchApp.fetch('https://docs.google.com/spreadsheets/d/xxx/export?format=xlsx',{headers: {Authorization: "Bearer " + ScriptApp.getOAuthToken()}});      
  var doc = response.getBlob();
  app = DriveApp.createFile(doc).setName(file.title + '.xls')
  MailApp.sendEmail("[email protected]", "oh man", " Body", { attachments: app })
}