使用AppScript将Google Spreadsheet转换为Excel并通过电子邮件发送 [英] Converting Google Spreadsheet to Excel with AppScript and send it via email

问题描述

我在如何将Google Spreadsheet转换为Excel时遇到了问题AppScript 2015

并被引导到这篇文章的答案 https://stackoverflow.com/a/27281729/1677912

and been guided to the answer in this other post https://stackoverflow.com/a/27281729/1677912

它似乎可行，但我遇到了一个新问题：创建的文件较小比它应该是，并且不打开（损坏）。这里是完整的代码：

It seemed to work, but I had a new problem: the created file is smaller than it should be, and doesn't open (corrupted). Here is the complete code:

function crearBackUp() {

//pone en la variable -sheet- a la hoja activa
var sheet = SpreadsheetApp.getActiveSpreadsheet();

//obtiene la fecha para ponerle al nombre del archivo
var d = new Date();
var mes = d.getMonth() + 1;
var anio = d.getFullYear();

var nombre = "backUP_BaseDatos_BN_"+mes.toString()+anio.toString();

//creo una copia y obtengo el ID para pasarlo a Drive
var theBKP = sheet.copy(nombre);
var theID = theBKP.getId();

//Abro el arch con Drive y exporto a XLS
var file = Drive.Files.get(theID);
var url = file.exportLinks[MimeType.MICROSOFT_EXCEL];

var response = UrlFetchApp.fetch(url);      
var doc = response.getBlob();

//crea el archivo con DriveApp pasasndole el Blob
var theFile = DriveApp.createFile(doc).setName(file.title + '.xlsx');

//Manda el mail
MailApp.sendEmail('[e-mail]', 'Subject','Mensaje',   {
  filename: nombre,
  attachments: theFile
  })
}

我不知道是否没有一个更容易或更直接的方法来做到这一点，因为这对我来说是一个很好的解决方法。

I wonder if there isn't an easier or more direct method to do it, as this is for me quite a workaround.

推荐答案

无论如何，当我尝试这个代码时，关键问题是Drive Advanced Service返回的是一个html而不是excel文件，并且看起来像是一个授权页面。所以我想我们需要为 UrlFetch 提供令牌才能使用它。

Anyway, the key issue when I tried this code was that the Drive Advanced Service was returning an html instead of the excel file, and looking at this html it looked like an authorization page. So I figured that we need to provide the token to the UrlFetch to make it work.

这是我的结果代码：

Here's my resulting code:

function crearBackUp() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();

  var d = new Date();
  var mes = d.getMonth() + 1;
  var anio = d.getFullYear();      
  var nombre = "backUP_BaseDatos_BN_"+mes.toString()+anio.toString();

  var file = Drive.Files.get(sheet.getId());
  var url = file.exportLinks[MimeType.MICROSOFT_EXCEL];

  var response = UrlFetchApp.fetch(url,
    {headers:{Authorization:"Bearer "+ScriptApp.getOAuthToken()}});
  var doc = response.getBlob();
  doc.setName(nombre+ '.xlsx');

  MailApp.sendEmail('[e-mail]', 'Subject', 'Mensaje', {attachments:[doc]});
}

这篇关于使用AppScript将Google Spreadsheet转换为Excel并通过电子邮件发送的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！