How To Download / Export Sheets In Spreadheet Via Google Apps Script

Question

The task is to automate the manual process accomplished by the menu option "File | Download As | Plain Text"

I want to be able to control the saved file name, which cannot be done via the menu.

At the time this is invoked, the user would be sitting on the sheet in the spreadsheet. Ultimately, I'd make it a menu option, but for testing I'm just creating a function that I can run manually.

After reading several other threads for possible techniques, this is what I've come up with.

It builds a custom name for the file, makes the call, and the response code is 200.

Ideally, I'd like to avoid the open / save dialog. In other words, just save the file without additional user intervention. I'd want to save in a specific folder and I've tried it with a complete file spec, but the result is the same.

If I copy the URL displayed in the Logger and paste it into a browser, it initiates the open / save dialog, so that string works.

Here's the code as a function.

function testExportSheet() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var oSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var sId = ss.getId();
var ssID=sId + "&gid=" + oSheet.getSheetId();
var url = "https://spreadsheets.google.com/feeds/download/spreadsheets/Export?key="
  + ssID + "&exportFormat=tsv"; 
Logger.log(url);
var fn =  ss.getName() + "-" + oSheet.getSheetName() + ".csv";
var sHeaders = {"Content-Disposition" : "attachment; filename=\"" + fn + "\""};
var sOptions = {"contentType" : "text/html", "headers" : sHeaders};
Logger.log(sOptions);

x = UrlFetchApp.fetch(url, sOptions)
Logger.log(x.getResponseCode());
}

Answer 1

I have exported a spreadsheet as CSV directly into a local hard drive as follows:

1. Get the CSV content from current sheet using a variation of function convertRangeToCsvFile_() from the tutorial on this page https://developers.google.com/apps-script/articles/docslist_tutorial#section3

   var csvFile = convertRangeToCsvFile_(...);
   

2. Then select a drive folder that is syncing to a local computer using Drive

   var localFolder = DocsList.getFolderById("055G...GM");
   

3. And finally save the CSV file into the "local" folder

   localFolder.createFile("sample.csv", csvFile);
   

That's it.

Answer 2

This app script returns a file for download instead of web page to display:

function doGet(){
    var outputDocument = DocumentApp.create('My custom csv file name'); 
    var content = getCsv();
    var textContent = ContentService.createTextOutput(content);
    textContent.setMimeType(ContentService.MimeType.CSV);
    textContent.downloadAsFile("4NocniMaraton.csv");
    return textContent;
}