0

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("xxx@xxx.com", "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

j0nthn
  • 11
  • 1

1 Answers1

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("xxx@xxx.com", "oh man", " Body", { attachments: app })
}
Spencer Easton
  • 5,642
  • 1
  • 16
  • 25
  • Thanks Spencer. The sidebar actually never shows up, even though the script processes the code without an error. The only thing that opens is a standard authorization popup, which happens even without the 'sidebar' code. – j0nthn Jun 12 '15 at 04:13
  • Oh yea. You would have to manually run the function to show the sidebar or add a menu item to launch it. And there will be two auth flows. One for the script and one for the Oauth library. Also look at my edit. – Spencer Easton Jun 12 '15 at 11:58
  • Thank you for the edit, it works perfectly! In fact, I only need above code (and then manually authorize the drive api in the project) – j0nthn Jun 15 '15 at 03:50