Skip to content

Instantly share code, notes, and snippets.

@mderazon
Last active April 2, 2024 22:25

Revisions

  1. mderazon revised this gist Apr 21, 2015. 1 changed file with 1 addition and 2 deletions.
    3 changes: 1 addition & 2 deletions export-to-csv.gs
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,6 @@
    /*
    * script to export data in all sheets in the current spreadsheet as individual csv files
    * files will be named according to the name of the sheet
    * Note: This script uses the DocsList.createFile() method, which is only available for Google Apps accounts.
    * author: Michael Derazon
    */

    @@ -15,7 +14,7 @@ function saveAsCSV() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ss.getSheets();
    // create a folder from the name of the spreadsheet
    var folder = DocsList.createFolder(ss.getName().toLowerCase().replace(/ /g,'_') + '_csv_' + new Date().getTime());
    var folder = DriveApp.createFolder(ss.getName().toLowerCase().replace(/ /g,'_') + '_csv_' + new Date().getTime());
    for (var i = 0 ; i < sheets.length ; i++) {
    var sheet = sheets[i];
    // append ".csv" extension to the sheet name
  2. mderazon revised this gist Apr 5, 2014. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion export-to-csv.gs
    Original file line number Diff line number Diff line change
    @@ -25,7 +25,7 @@ function saveAsCSV() {
    // create a file in the Docs List with the given name and the csv data
    folder.createFile(fileName, csvFile);
    }
    Browser.msgBox('Files are waitig in a folder named ' + folder.getName());
    Browser.msgBox('Files are waiting in a folder named ' + folder.getName());
    }

    function convertRangeToCsvFile_(csvFileName, sheet) {
  3. mderazon revised this gist Mar 20, 2014. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion export-to-csv.gs
    Original file line number Diff line number Diff line change
    @@ -1,7 +1,7 @@
    /*
    * script to export data in all sheets in the current spreadsheet as individual csv files
    * files will be named according to the name of the sheet
    * Note: This section uses the DocsList.createFile() method, which is only available for Google Apps accounts.
    * Note: This script uses the DocsList.createFile() method, which is only available for Google Apps accounts.
    * author: Michael Derazon
    */

  4. mderazon created this gist Mar 20, 2014.
    65 changes: 65 additions & 0 deletions export-to-csv.gs
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,65 @@
    /*
    * script to export data in all sheets in the current spreadsheet as individual csv files
    * files will be named according to the name of the sheet
    * Note: This section uses the DocsList.createFile() method, which is only available for Google Apps accounts.
    * author: Michael Derazon
    */

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

    function saveAsCSV() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheets = ss.getSheets();
    // create a folder from the name of the spreadsheet
    var folder = DocsList.createFolder(ss.getName().toLowerCase().replace(/ /g,'_') + '_csv_' + new Date().getTime());
    for (var i = 0 ; i < sheets.length ; i++) {
    var sheet = sheets[i];
    // append ".csv" extension to the sheet name
    fileName = sheet.getName() + ".csv";
    // convert all available sheet data to csv format
    var csvFile = convertRangeToCsvFile_(fileName, sheet);
    // create a file in the Docs List with the given name and the csv data
    folder.createFile(fileName, csvFile);
    }
    Browser.msgBox('Files are waitig in a folder named ' + folder.getName());
    }

    function convertRangeToCsvFile_(csvFileName, sheet) {
    // get available data range in the spreadsheet
    var activeRange = sheet.getDataRange();
    try {
    var data = activeRange.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);
    }
    }