I made a question in how to Convert Google Spreadsheet to excel with AppScript 2015
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('email@gmail.com', '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.
There’s a few weird things in your code, like duplicating the sheet to generate the xls, or creating a drive file out of the xls to send via email, wrong way to set the attachment name, etc.
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:
It’s been a long time since Henrique’s answer, and
exportLinks
is no longer working. For this reason I leave a Script that does what you need.