How to export to CSV from spreadsheet to drive or download folder

Question

I have tried with the below script is not working. When I download into CSV format it's all in one column instead standard format. Using Google Apps script, how to download to CSV format and file save in my drive or download?

function onOpen() { 
    var ss = SpreadsheetApp.getActiveSpreadsheet(); 
    var csvMenuEntries = [{name: "export as csv files", functionName: "saveAsCSV"}]; 
    ss.addMenu("csv", csvMenuEntries);
};

You forgot to add the code you have tried. http://stackoverflow.com/help/how-to-ask — Aaron, Feb 15 '17 at 16:26
function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var csvMenuEntries = [{name: "export as csv files", functionName: "saveAsCSV"}]; ss.addMenu("csv", csvMenuEntries); }; — Nagaraj Shet, Feb 15 '17 at 16:26
I have tried but unable to add code in my question TAB please help me on this — Nagaraj Shet, Feb 15 '17 at 16:29
Here is the link of the script https://script.google.com/macros/d/MAnZw7U6nIlzOWK6Jk6YmVSwLGULI8KsJ/edit?uiv=2&mid=ACjPJvGGtI0DcYT575lSrn0PgZwf7bgWEEa8TvY5C2Gj6X8D001hC2LXkR7Tj0k_PAxrw5R9zS-gnwC45aJhPwVC7g5_qJfdG5MbuBrfyhtUcuZebpmIJSI58ZxL4sHwuSaU3jZsh81r6l8 — Nagaraj Shet, Feb 15 '17 at 16:32

score 5 · Answer 1 · answered Feb 16 '17 at 02:15

5

How about this script? This script exports a CSV file from spreadsheet and make an user download it.

Please put both HTML and script in a GAS project.

html :

This file name is "download.html".

<!DOCTYPE html>
<html>
  <body>
    Download CSV?
    <form>
      <input type="button" value="ok" onclick="google.script.run
                                              .withSuccessHandler(executeDownload)
                                              .saveAsCSV();" />
    </form>
  </body>
  <script>
    function executeDownload(url) {
      window.location.href = url;
    }
  </script>
</html>

Script :

function onOpen() {
  SpreadsheetApp.getUi()
                .createMenu('csv')
                .addItem('export as csv files', 'dialog')
                .addToUi();
}

function dialog() {
  var html = HtmlService.createHtmlOutputFromFile('download');
  SpreadsheetApp.getUi().showModalDialog(html, 'CSV download dialog');
}

function saveAsCSV() {
    var filename = "#####"; // CSV file name
    var folder = "#####"; // Folder ID

    var csv = "";
    var v = SpreadsheetApp
            .getActiveSpreadsheet()
            .getActiveSheet()
            .getDataRange()
            .getValues();
    v.forEach(function(e) {
      csv += e.join(",") + "\n";
    });
    var url = DriveApp.getFolderById(folder)
              .createFile(filename, csv, MimeType.CSV)
              .getDownloadUrl()
              .replace("?e=download&gd=true","");
    return url;
}

Process :

Using "onOpen()", it addes menu for launching a dialog.
After launching the dialog, "saveAsCSV()" is launched by pushing a button. "saveAsCSV()" exports a CSV file and outputs download URL. At current script, all of data on active sheet is retrieved and exported CSV. If you want to retrieve a range you need, please use "getRange()".
The CSV file is downloaded by "executeDownload()".

You can set csv file-name and output folder by "filename" and "folder", respectively. In this sample, the source sheet is the sheet which opens currently. If you want to other sheet, please change this script.

If this will be helpful for you, I'm glad.

answered Feb 16 '17 at 02:15

Tanaike

181,128
11
97
165

I have tried it's not working and error is throwing in "var url = DriveApp.getFolderById(folder)" – Nagaraj Shet Feb 16 '17 at 10:53
Perhaps your folder ID is incorrect. You can go to the folder with Google Drive and right click on the drive and select get shareable link and the "id=" will be in the query string. – Cooper Feb 16 '17 at 18:45
@Nagaraj Shet This script works fine. It may be a problem for your modification of this script. Can I ask you about your script? – Tanaike Feb 16 '17 at 22:09
@Cooper Thank you for your support. – Tanaike Feb 16 '17 at 22:09
@Tanaike Glad to help – Cooper Feb 17 '17 at 00:45
@Nagaraj Shet That's good. If your question was solved, please push accepted button. If you push accepted button on this your question, everyone will be able to see this as a solved question. And also I'm glad. If you don't know about "accepted", please check this. http://stackoverflow.com/tour – Tanaike Feb 18 '17 at 01:30
@Tanaike while looking for a solution I found your answer here. Question please: is it possible with your approach to run the code for specific sheets, let's say `var mySheets = [Sheet1,Sheet2,Sheet3]` and likewise instead of the active range or getRange, is it possible possible to consider having multiple ranges in an array, for example: `var myRanges = ["A1:C", "G1:I", "M1;N"]`. Thank you for any help that you might provide. Regards – Nabnub Jun 23 '20 at 01:22
@Nabnub Thank you for your comment. I have to apologize for my poor English skill. Unfortunately, I cannot understand about your comment. Can you post it as new question by including the detail information? By this, it will help users including me think of the solution. And also, this is not your question. So even when I could find the solution, I cannot post it as an answer. So when you can cooperate to resolve your issue, I'm glad. – Tanaike Jun 23 '20 at 01:54
Note that if there are commas in any of the cell values, they will be interpreted as additional columns when `csv` is parsed into the file, so columns will no longer line up. – Aaron Dunigan AtLee Dec 31 '20 at 19:53

score 0 · Answer 2 · answered Feb 16 '17 at 19:42

I could not get this to work either.

var csv = "";
var v = SpreadsheetApp
            .getActiveSpreadsheet()
            .getActiveSheet()
            .getDataRange()
            .getValues();
    v.forEach(function(e) {
      csv += e.join(",") + "\n";
    });

I've never used the join method but I'm sure it's an intelligent approach however I decided to rewrite it in a much more simple minded approach. It doesn't handle the cases when there are commas in the fields and it doesn't put double quotes around any of the fields. To be quite honest about this sort of thing I almost always use ~~~ as my delimiter. Create columns with text to columns and life runs much smoother for me. In other words, if I can, I'll always take the easy way out.

Here's my version of just that section of code. Perhaps everything will run better for you now.

var csv = '';
  var v = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getDataRange().getValues();
  for( var i = 0;i < v.length;i++)
  {

    for(var j = 0; j < v[i].length;j++)
    {
      if(j>0)
      {
        csv += ', ';
      }
      csv += v[i][j].toString();  
    }
    csv += '\n';
  }

The rest of Tanaike's code looks like it should work. If you have any further troubles come on back and I'll be glad to take another look at it.

I have doubt, If I download the file it's not coming in same format in the trix. I have selected Pipe delimited in control panel. Please let me know do we have any chances to come same format as per the trix when download the file — Nagaraj Shet, Feb 17 '17 at 15:03

How to export to CSV from spreadsheet to drive or download folder

2 Answers2

Linked