Converting Google Spreadsheet to Excel with AppScript and send it via email

Question

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.

@Mogsdad here is the full code – Rodrigo E. Principe Sep 02 '15 at 15:11 — Rodrigo E. Principe, Sep 02 '15 at 15:11

score 3 · Accepted Answer · answered Sep 02 '15 at 17:07

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:

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('example@gmail.com', 'Subject', 'Mensaje', {attachments:[doc]});
}

Hi Henrique, I duplicate the file because I also needed a copy (backUp) on my GDrive.. and the file creation through Drive was part of the code I took from the last answer I had.. I see that Mogsdad updated it. Well, it works perfect! Thank you Henrique and Mogsdad — Rodrigo E. Principe, Sep 02 '15 at 20:02
But you need on drive, a gsheet backup, an excel or both? Because you were adding both.. @Rodrigo — Henrique G. Abreu, Sep 02 '15 at 21:20
Yes, you are right, I just needed a copy of the gsheet.. my mistake =) @henrique — Rodrigo E. Principe, Sep 03 '15 at 22:45

score 2 · Answer 2 · answered Feb 15 '21 at 23:49

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.

function SendSheetAsXlsxAttach() {

  try {

    /*Get your Spreadsheet */
    var ss = SpreadsheetApp.openById("[YourSpreadsheetID]");

    /*Create URL to Export as xlsx */
    var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + ss.getId() + "&exportFormat=xlsx";

    var params = {
      method: "get",
      /*add authorization to get the file */
      headers: { "Authorization": "Bearer " + ScriptApp.getOAuthToken() },
      muteHttpExceptions: true
    };

    /* with an http query get the blob file */
    var blob = UrlFetchApp.fetch(url, params).getBlob();

    /* sets the file extension */
    blob.setName(ss.getName() + ".xlsx");

    /* Send Mail with attachments */
    MailApp.sendEmail("[MailHere]", "Subject", "This mail contains .XLSX file attached", { attachments: [blob] });

  } catch (f) {
    Logger.log(f.toString());
  }

}

Live Automation Experts LAE · Answer 3 · 2022-11-21T14:45:29.737

0

Perfect solution. Working like a charm. For the spreadsheet ID, use the following:

var ss = SpreadsheetApp.openById(SpreadsheetApp.getActiveSpreadsheet().getId());

edited Nov 21 '22 at 14:45

answered Oct 21 '21 at 14:48

Live Automation Experts LAE

1
1

Converting Google Spreadsheet to Excel with AppScript and send it via email

3 Answers3

Linked