谷歌Apps脚本通过电子邮件发送电子表格谷歌Excel版本(Google apps script t

我想编写一个应用程序的脚本电子邮件我的谷歌电子表格的Excel版本。 我知道我可以将电子表格保存为一个Excel文件。 我不知道我是否可以使用脚本通过电子邮件发送的Excel版本出来作为附件。 如何才能做到这一点？

Answer 1:

在之后的另一个最近的文章答案 （ 托马斯面包车Latum ），我尝试了建议的文档API，并得到一个有趣的结果......这里是我使用的测试代码和除文件正在很好地为XLSX格式，而不是在XLS但是这并不一定是一个问题，这些天：

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')
}

注意：如果你不将其重命名，它的默认名称为Export.xlsx ，它可能会更有用得到它的ID后使用它...所以最后一行可能是这样的，而不是：

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

---

编辑：触发授权过程，尝试这样的小功能，从脚本编辑器运行

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();  
}

Answer 2:

为我工作的一个：

var AUTH_TOKEN = "Enter your OAuth_Token"; 

ssID = SpreadsheetApp.getActiveSpreadsheet().getId();
var url = "http://spreadsheets.google.com/feeds/download/spreadsheets/Export?key="+ ssID + "&exportFormat=xls"; 

//Add &gid=x at the end of above url if you only want a particular sheet

var auth = "AuthSub token=\"" + AUTH_TOKEN + "\"";
var res = UrlFetchApp.fetch(url, {headers: {Authorization: auth}});
var attachments = [{fileName:"<Filename>.xls", content: res.getContent(),mimeType:"application/vnd.ms-excel"}];

MailApp.sendEmail("<recipient's email id>", "<email subject>", "<email body>", {attachments: attachments});

取得OAuth文档与您从这里令牌https://developers.google.com/accounts/docs/OAuth2

Answer 3:

最新的工作版本如下。 基于这个例子，即在以前的答案相似，但使用谷歌服务帐户不需要人通过链接将要接收的令牌。 你必须从安装谷歌誓言库的指令是很清楚。

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('[e-mail]', 'xxx', 'Hi,\n\nPlease see the last data in attachment',{attachments:attachments});
}

Answer 4:

当我花了打侏儒怪大约四个小时，因为没有通常很老的代码片段的旧电子表格的版本和老OAUTH谷歌搜索时，你可以找到“谷歌文档脚本发送Excel附件”或类似（即你想利用现有的电子表格，将其转换为Excel格式，并把它作为电子邮件附件）实际工作，我终于找到了解决办法。

创建实际的附件内容，既不应该res.getContent（），也不res.getBlob（），也不res.getBytes独立工作。 这些提示是误导！

这对我的作品的唯一事情是response.getBlob（）的getContent（）！

整个代码：

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

  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getName();
  var email = "[e-mail]"
  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}); 

}

凡getDriveService（）是来自谷歌的“OAuth2以Google Apps脚本”自述上的功能https://github.com/googlesamples/apps-script-oauth2

Answer 5:

使用下面的代码片段在修改之后，以满足您的需求

var file = DocsList.getFileById(FILE_ID);
var attachment = file.getAs('application/vnd.ms-excel'); 
MailApp.sendEmail("[e-mail]", "Subject", " Body" , {"fileName": "Your_file_name" , "mimeType" : "application/vnd.ms-excel" , "content":attachment.getBytes() } ); 

请注意，这个代码不进行测试，所以请随时修正错误或两个可能会弹出

文章来源: Google apps script to email google spreadsheet excel version