CODEWITHSUNDEEP
google-apps-scriptexportgoogle-sheets
Richard Michael
Richard Michael

Reputation: 71

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

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());
}

Upvotes: 6

Views: 31711

Answers (5)

Warren U
Warren U

Reputation: 11

This is the function that I found when looking up the same question. This function was linked to by @Mogsdad and the link no longer exists.

function convertRangeToCsvFile_(csvFileName) {
  // Get the selected range in the spreadsheet
  var ws = SpreadsheetApp.getActiveSpreadsheet().getActiveSelection();
  try {
    var data = ws.getValues();
    var csvFile = undefined;
    // Loop through the data in the range and build a string with the CSV data
    if (data.length > 1) {
      var csv = "";
      for (var row = 0; row < data.length; row++) {
        for (var col = 0; col < data[row].length; col++) {
          if (data[row][col].toString().indexOf(",") != -1) {
            data[row][col] = "\"" + data[row][col] + "\"";
          }
        }
        // Join each row's columns
        // Add a carriage return to end of each row, except for the last one
        if (row < data.length-1) {
          csv += data[row].join(",") + "\r\n";
        }
        else {
          csv += data[row];
        }
      }
      csvFile = csv;
    }
    return csvFile;
  }
  catch(err) {
    Logger.log(err);
    Browser.msgBox(err);
  }
}

I found it here and here

Upvotes: 1

Fausto R.
Fausto R.

Reputation: 1334

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.

Upvotes: 5

Michael
Michael

Reputation: 22937

In case you are looking to export all of the sheets in s spreadsheet to csv without having to manually do it one by one, here's another thread about it:

Using the google drive API to download a spreadsheet in csv format

Upvotes: 2

boskicthebrain
boskicthebrain

Reputation: 545

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;
}

Upvotes: 2

eddyparkinson
eddyparkinson

Reputation: 3700

The download can be done. But not the "Write to the hard drive" of the computer.

Write issue: You mean write a file to the hard drive of the computer, using Google Apps Script? Sorry, but you will need more than GAS to do this. For security reasons, I doubt this is possible with only GAS, have never seen anything like this in GAS.

Google Drive API will let you do a download, just needs OAuth and the URL you gave.

Upvotes: 0

Related Questions