CODEWITHSUNDEEP
google-apps-script
Nagaraj Shet
Nagaraj Shet

Reputation: 57

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

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

Upvotes: 3

Views: 14321

Answers (2)

Cooper
Cooper

Reputation: 64032

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.

Upvotes: 0

Tanaike
Tanaike

Reputation: 201378

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 :

  1. Using "onOpen()", it addes menu for launching a dialog.

  2. 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()".

  3. 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.

Upvotes: 7

Related Questions