1

I have found a script to export my google sheets workbook as .XLXS and email it to an address.

However it is not working when i try and set the name of the .xlxs file to a value in the workbook. code below:

function getGoogleSpreadsheetAsExcel(){

  try {

    var ss = SpreadsheetApp.getActive();

    var name = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Project Details').getRange(2,A).getValue()

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

    var params = {
      method      : "get",
      headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
      muteHttpExceptions: true
    };

    var blob = UrlFetchApp.fetch(url, params).getBlob();

    blob.setName( name + ".xlsx");

    MailApp.sendEmail("(i know an email address goes here)", "Tablet Data Import", "The XLSX file is attached", {attachments: [blob]});

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

Some help would be appreciated

Rdgsttb11
  • 13
  • 2
  • Does this answer your question? [How to convert a Google Docs-File to an Excel-File (XLSX)](https://stackoverflow.com/questions/27277058/how-to-convert-a-google-docs-file-to-an-excel-file-xlsx) – Rubén Mar 19 '20 at 15:25
  • Please explain precise how it is not working? – Cooper Mar 19 '20 at 15:57

2 Answers2

0

The problem is caused by the way you use the method getRange()

There are two ways to do it correctly:

getRange('A2')

getRange(2, 1)

ziganotschka
  • 25,866
  • 2
  • 16
  • 33
0

If the getValue change isn't effective then maybe...

Bring the setName function into the initial blob creation.

var blob = UrlFetchApp.fetch(url, params).getBlob();

blob.setName( name + ".xlsx");

to

var blob = UrlFetchApp.fetch(url, params).getBlob().setName( name + ".xlsx");

I'm not especially skilled in google apps script but I've found commands like this need to be after a var to be effective.

Community
  • 1
  • 1
Robert Devennie
  • 1