Download "automatically" spreadsheet as .xlsx to local machine on event

Question

See below the function that converts the active Google Spreadsheet to a .xlsx file. The script saves the file in Google Drive.

function downloadAsXlsx() {

var spreadSheet = SpreadsheetApp.getActiveSpreadsheet(); 
var ssID = spreadSheet.getId();

Logger.log(ssID);

var url = "https://docs.google.com/spreadsheets/d/"+ssID+"/export?format=xlsx";   
var params = {method:"GET", headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};
var response = UrlFetchApp.fetch(url, params);

// save to drive
DriveApp.createFile(response);

}

If you replace the ssID in the URL above by the actual file id of the active Google Spreadsheet and copy and paste the URL in the browser, the active spreadsheet is downloaded "automatically". That is exactly what I need to be added to above script.

My question is how to change and/or extend the function above so that the file is instead downloaded to the local machine in the default download folder?

Answer 1

The code you posted in your question, as you've noticed, exports an XLS version of the Google Spreadsheet to Google Drive. If you're syncing Google Drive to your PC, then you end up with a copy on your PC as well... but that's not what you're after. You want a way to trigger an HTTP download of the Google Sheet that uses the system dialog to let you choose where on your PC to save the downloaded file.

To accomplish this, we can't use server-side Google Apps Script techniques, as the server has no access to your machine's resources. Instead, we need to leverage the browser. Since you're interested in doing this from the "active spreadsheet", it's logical to extend the UI of your Google Spreadsheet with a custom menu item to serve up a dialog that includes a download link.

Remarkably little code is required to support this. The work is done by your browser, based on the single line of HTML containing the <a> (anchor) tag.

/**
 * Adds a custom menu
 *
 * @param {Object} e The event parameter for a simple onOpen trigger.
 */
function onOpen(e) {
  SpreadsheetApp.getUi()
      .createMenu('Custom')
      .addItem('Download as XLSX', 'downloadXLS_GUI')
      .addToUi();
}


/**
 * Display a modal dialog with a single download link.
 *
 * From: http://stackoverflow.com/a/37336778/1677912
 */
function downloadXLS_GUI() {
  // Get current spreadsheet's ID, place in download URL
  var ssID = SpreadsheetApp.getActive().getId();
  var URL = 'https://docs.google.com/spreadsheets/d/'+ssID+'/export?format=xlsx';

  // Display a modal dialog box with download link.
  var htmlOutput = HtmlService
                  .createHtmlOutput('<a href="'+URL+'">Click to download</a>')
                  .setSandboxMode(HtmlService.SandboxMode.IFRAME)
                  .setWidth(80)
                  .setHeight(60);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Download XLS');
}