Google apps script to email google spreadsheet excel version

Question

I would like to write an apps script to email an excel version of my Google Spreadsheet. I know I can save the spreadsheet as an Excel file. I am not sure if I can use the script to email the excel version out as an attachment. How can this be done?

Solution 1

After an answer on another recent post (Thomas van Latum), I tried the suggested doc api and get an interesting result... here is the test code I used and that is working nicely except the file is in xlsx format, not in xls but this is not necessarily an issue these days :

function googleOAuth_(name,scope) {
  var oAuthConfig = UrlFetchApp.addOAuthService(name);
  oAuthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope="+scope);
  oAuthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");
  oAuthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
  oAuthConfig.setConsumerKey('anonymous');
  oAuthConfig.setConsumerSecret('anonymous');
  return {oAuthServiceName:name, oAuthUseToken:"always"};
}

function test(){
var id = 'spreadsheet_ID'
var url = 'https://docs.google.com/feeds/';
var doc = UrlFetchApp.fetch(url+'download/spreadsheets/Export?key='+id+'&exportFormat=xls',
googleOAuth_('docs',url)).getBlob()
DocsList.createFile(doc).rename('newfile.xls')
}

note : if you don't rename it, its default name is Export.xlsx , it might be more usefull to get its ID to use it later... so the last line could be like this instead :

var xlsfileID = DocsList.createFile(doc).getId()

---

EDIT : to trigger the authorization process, try a small function like this, run it from the script editor

function autorise(){
  // function to call to authorize googleOauth
  var id=SpreadsheetApp.getActiveSpreadsheet().getId();
  var url = 'https://docs.google.com/feeds/';
  var doc = UrlFetchApp.fetch(url+'download/documents/Export?exportFormat=html&format=html&id='+id,
                              googleOAuth_('docs',url)).getContentText();  
}

Solution 2

The latest working version is below. Based on this example, i.e. similar as in previous answer but uses Google Service Account which does not require a human going by link to receive a token. You have to install Oath library from Google, the instructions are pretty clear.

var PRIVATE_KEY = 'xxx'
var CLIENT_EMAIL = 'xxx';
var USER_EMAIL=Session.getActiveUser().getEmail()

function getOathService() {
  return OAuth2.createService('GoogleDrive:' + USER_EMAIL)
      // Set the endpoint URL.
      .setTokenUrl('https://accounts.google.com/o/oauth2/token')

      // Set the private key and issuer.
      .setPrivateKey(PRIVATE_KEY)
      .setIssuer(CLIENT_EMAIL)

      // Set the name of the user to impersonate. This will only work for
      // Google Apps for Work/EDU accounts whose admin has setup domain-wide
      // delegation:
      // https://developers.google.com/identity/protocols/OAuth2ServiceAccount#delegatingauthority
      .setSubject(USER_EMAIL)

      // Set the property store where authorized tokens should be persisted.
      .setPropertyStore(PropertiesService.getScriptProperties())

      // Set the scope. This must match one of the scopes configured during the
      // setup of domain-wide delegation.
      .setScope('https://www.googleapis.com/auth/drive');
}


function sendEmail() { 
  var oathService = getOathService();
  var ssID = SpreadsheetApp.getActiveSpreadsheet().getId(); 
  var file = Drive.Files.get(ssID );
  var url = file.exportLinks[MimeType.MICROSOFT_EXCEL];

  var file = UrlFetchApp.fetch(url, {
    headers: {
      Authorization: 'Bearer ' + oathService.getAccessToken()
    }
  });
   var attachments = [{
    fileName:'xxx.xls',//TODO DATE
    content: file.getBlob().getBytes(), 
    mimeType:"application//xls",
    headers: {
      Authorization: 'Bearer ' + oathService.getAccessToken()
    }
  }];

  MailApp.sendEmail('[email protected]', 'xxx', 'Hi,\n\nPlease see the last data in attachment',{attachments:attachments});
}

Solution 3

As I spent about four hours of playing Rumpelstiltskin because none of the typically very old code snippets for the old Spreadsheet versions and the old OAUTH you can find when googling "google docs script send excel attachment" or similar (i.e. you want to take an existing Spreadsheet, convert it to Excel format and send it as email attachment) actually worked, I finally found the solution.

To create the actual attachment content, neither the supposed res.getContent() nor res.getBlob() nor res.getBytes alone worked. These hints are misleading!

The only thing that works for me is response.getBlob().getContent()!

Whole code :

function sendCurrentDocAsEmail() {
  var driveService = getDriveService();
  var ssID = SpreadsheetApp.getActiveSpreadsheet().getId(); 

  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getName();
  var email = "[email protected]"
  var subject = "Here be Subjects";
  var body = "Don't even think about learning how to code. It's wasted time.";
  var file = Drive.Files.get(ssID );
  var url = file.exportLinks[MimeType.MICROSOFT_EXCEL];

  var response = UrlFetchApp.fetch(url, {
    headers: {
      Authorization: 'Bearer ' + driveService.getAccessToken()
    }
  });

  var attachments = [{
    fileName:sheetName+".xlsx",
    content: response.getBlob().getBytes(), // this single line has cost me hours!
    mimeType:"application//xls",
    headers: {
      Authorization: 'Bearer ' + driveService.getAccessToken()
    }
  }];

  MailApp.sendEmail(email,subject ,body, {attachments:attachments}); 

}

Where getDriveService() is a function from Google's "OAuth2 for Apps Script" readme on https://github.com/googlesamples/apps-script-oauth2

Comments

[Serge insas]

file.getAs('application/vnd.ms-excel') returns 'unsupported conversion request. Where did you get this format information ? is it something new ?

[Srik]

The getAs for PDF works, so guess it works for excel too. To answer your question, I got the content type from stackoverflow.com/questions/2937465/…

[Serge insas]

Thanks for your answer but I think that right now GAS only support 'application/pdf'(the post you refer to wasn't tagged gas), There is an open issue on that here, in the meantime and for now it doesn't work...

[Igor Romanov]

This MIME type (Excel) is not supported with getAs().

[Serge insas]

see issue 677 for more details. The oAuth requires a annoying authorization procedure, let's hope Google will improve that one of these days ;-)

[Christiaan Westerbeek]

This is an obsolete answer imo. When run, this error is thrown Script is using OAuthConfig which has been shut down. Learn more at goo.gl/IwCSaV (line 6, file "")

[Serge insas]

@christiaan, This is an obsolete answer indeed, look at the answer date. 2012 !! things have change since 2012 (and btw you don't need to downvote for such reason... what's the point anyway ?)

[Christiaan Westerbeek]

@Sergeinsas a downvote is not a critic but a way to get obsolete answer to the bottom. This is to prevent other readers to think that an answer is still relevant and consume their time needlessly. meta.stackexchange.com/questions/11705/…