7

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?

juseit
  • 81
  • 1
  • 1
  • 5
  • Note: the answer you linked to in your question is client-side JavaScript, while your question is about Google Apps Script. – Mogsdad May 13 '16 at 14:29
  • Thx for pointing me in the right direction. I will try the answer in the question you mentioned coming days. – juseit May 13 '16 at 22:53
  • 1
    @Mogsdad Note: You marked my question as duplicated. In fact the answer on the question you refers to is doing the same as my script above: creating a new file with extension .xlsx in the Google Drive. That is OK but in addition what I need is to have this file automatically downloaded to a folder on the local machine. In fact, as you can read above, if you replace the ssID with the actual file id of an existing file in Drive in the url and copy and paste the url in a browser, the correct file is downloaded. That is what I need. Hope you can help me with that part also. – juseit May 18 '16 at 06:46
  • Fair enough. But first, edit it to focus on the unique question it poses. Then ping me again, and I'll reopen. – Mogsdad May 18 '16 at 11:34
  • @Mogsdad is this OK now? Problem is that I don't really understand the http requests. I guess I need to do something more with the response. It looks like the response contains the file as the last step (also with the response as parameter) is saving the .xlsx file in Drive. – juseit May 19 '16 at 19:18
  • Great job refocusing that. You'll probably be able to get started with the info in [Download file from Google Drive to local folder from Google Apps Script](http://stackoverflow.com/q/17376102/1677912). – Mogsdad May 20 '16 at 00:51
  • @Mogsdad Thx for updating the question. I am a newby here and learn a lot from you. The question you refer to I have seen before. I also tried to add ContentService to my script before posting my question. Without success. I have also seen your answer on my question. This looks great to me and I am going to try later today and give you feedback. I am a bit confused about the ContentService now, reading your answer. I guess I don't need it anymore. Thx again. – juseit May 20 '16 at 09:00
  • True - the Content Service direction was incorrect; it would work for serving _text_, in the form of CSV content, say, but not for a binary XLS file. Turned out that coaxing the browser to do the work was the way to go. – Mogsdad May 20 '16 at 15:38

1 Answers1

7

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.

screenshot

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');
}
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
  • Almost there. First I updated the question. It was in the text but not in the title. You solution still requires an action of the user. So I added automatically and event to the title. I believe event onSave does not exists (I could not find it) because Google saves automatically. onOpen is OK for now. I also want to save it not to the default downloadfolder. I don't need a dialog but a fix path to antoher location then the default location is OK. Do you think it is possible what I am looking for (automatically by onOpen event)? No is also an answer. – juseit May 22 '16 at 09:57
  • No, it's not possible to get any remote server to update a file on your client PC automatically. (Huge security concerns.) – Mogsdad May 23 '16 at 01:11
  • Clear. I thought it would be something like that. Thanks for answering my questions. I will mark this question as answered. – juseit May 24 '16 at 20:24
  • Either Google has changed how stuff works, or I don't understand this answer because when I try to use this code (which I appreciated), my Google Sheets prompts me to grant access to Google Drive. I thought the goal was to allow users to download values straight to a file whose name and location they choose on their local computer (having nothing to do with Google Drive). Thanks. – Ryan Dec 08 '20 at 22:47
  • 1
    @Ryan This is because of the OAuth scopes and because the referenced resource is stored in a Google Drive (see the `var URL ...` code line). Even though and most likely it will be a spreadsheet owned by you, OAuth requires you to grant access to any kind of potentially risky things including Apps Script written by yourself and stored in your own Drive. – eddipedia May 20 '21 at 12:56