0

The below scrip to email a google spreadsheet as a pdf worked perfectly until last week. Now I get this message: "Authorization is required to perform that action". After reading up on it, I understand that Google no longer supports OAuth 1.0.

Can anyone guide me in the right direction towards updating the script for oAuth 2.0?

    function EmailSpreadsheetAsPdf() {

      // Google spreadsheet (key) + sheet (gid)
     var key = "1XJDY-M2oSfIG6AQ3IYv4SwKn_QmPW2m24ZNB38o7vCw";
     var gid = "1593627730";

     // Email recipient
     var emailTo = “testuser@gmail.com";
     var subject = “This is the subject”;
     var body = “This is the body“; 
     var filename = “Example" + ".pdf";

     // Make OAuth Connection
       var oauthConfig = UrlFetchApp.addOAuthService("google");
       var scope = "https://docs.google.com/feeds"
       oauthConfig.setConsumerKey("anonymous");
       oauthConfig.setConsumerSecret("anonymous");
       oauthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope=" + scope);
       oauthConfig.setAuthorizationUrl("https://accounts.google.com/OAuthAuthorizeToken");
       oauthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");

       var request = {
           "method": "GET",
           "muteHttpExceptions": true,
           "oAuthServiceName": "google",
           "oAuthUseToken": "always",
       };

       // Create the PDF using this hack with special option variables in the URL
       // As of 2/4/14 this seems to be the only way to export PDF with custom options (landscape, no gridlines, etc)
       // exportFormat = pdf / csv / xls / xlsx
       // gridlines = true / false
       // printtitle = true (1) / false (0)
       // size = legal / letter/ A4 (according to: http://goo.gl/nPrfdj, but doesn't seem to work?? letter only)
       // fzr (repeat frozen rows) = true / false
       // portrait = true (1) / false (0)
       // fitw (fit to page width) = true (1) / false (0)
       // add gid if to export a particular sheet - 0, 1, 2,..

       //define the params URL to fetch
       var params = "?gid=" + gid + "&fitw=true&exportFormat=pdf&format=pdf&size=A4&portrait=false&sheetnames=false&printtitle=false&gridlines=false";

       //fetching file url
       var blob = UrlFetchApp.fetch("https://docs.google.com/" + "spreadsheets/d/" + key + "/export" + params, request);
       var pdf  = blob.getBlob().setName(filename);

       // Send the email with attachment
       GmailApp.sendEmail(emailTo, subject, body, {attachments:[pdf]});

     }

Google ressources: https://developers.google.com/identity/protocols/OAuth_ref

Jazzpa
  • 1
  • 2
  • This is the documentation for migrating to 2.0: https://developers.google.com/identity/protocols/OAuth_ref#migration. what are you stuck with specifically? – HDCerberus May 24 '15 at 10:55
  • yes, what have you tried and didnt work? (showing the old code is not an attempt) – Zig Mandel May 24 '15 at 13:53
  • I have created a new project in the Google Developers Console (which I never used before) but can't find the "Google Sign-In API", as it says in the migration guide. – Jazzpa May 25 '15 at 16:06
  • UPDATE: I have created the access token to authorize sending emails from my Gmail account, by following this guide: http://stackoverflow.com/questions/19766912/how-do-i-authorise-an-app-web-or-installed-without-user-intervention-canonic But how do I modify the request to make the OAuth 2.0 Connection? – Jazzpa Aug 17 '15 at 19:53

1 Answers1

0

I finally solved my own question. I didn't even need to setup anything in the develop console, see my solution below. The fist time you run it, it will promt you to authorize access to your gmail. I hope it will be helpful to someone.

function EmailSpreadsheetAsPdf() {

// Google spreadsheet (key) + sheet (gid)
var key = "1XJDY-M2oSfIG6AQ3IYv4SwKn_QmPW2m24ZNB38o7vCw";
var gid = "1593627730";

// Email recipient
var emailTo = “testuser@gmail.com";
var subject = “This is the subject”;
var body = “This is the body“; 
var filename = “Example" + ".pdf";

// Base URL
var url = "https://docs.google.com/spreadsheets/d/" + key + "/export?";

/* Specify PDF export parameters
From: https://code.google.com/p/google-apps-script-issues/issues/detail?id=3579
*/

var url_ext = 'exportFormat=pdf&format=pdf'        // export as pdf / csv / xls / xlsx
+ '&size=letter'                       // paper size legal / letter / A4
+ '&portrait=false'                    // orientation, false for landscape
+ '&fitw=true&source=labnol'           // fit to page width, false for actual size
+ '&sheetnames=false&printtitle=false' // hide optional headers and footers
+ '&pagenumbers=false&gridlines=false' // hide page numbers and gridlines
+ '&fzr=false'                         // do not repeat row headers (frozen rows) on each page
+ '&gid=';                             // the sheet's Id

var token = ScriptApp.getOAuthToken();

// Convert worksheet to PDF
var pdf = UrlFetchApp.fetch(url + url_ext + gid, {
  headers: {
    'Authorization': 'Bearer ' +  token
  }
});

// Send email with PDF attachment
GmailApp.sendEmail(emailTo, subject, body, {
  attachments:[pdf]     
});


 }
Jazzpa
  • 1
  • 2