Skip to content

Instantly share code, notes, and snippets.

@cosmobug

cosmobug/export-named-sheet-as-csv.gs

Forked from mrkrndvs/export-named-sheet-as-csv.gs
Created May 18, 2019 10:29
Show Gist options
  • Save cosmobug/57dd09fab038d0f21f3209e5b9e8486d to your computer and use it in GitHub Desktop.
Save cosmobug/57dd09fab038d0f21f3209e5b9e8486d to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @mrkrndvs mrkrndvs revised this gist Sep 19, 2017. 2 changed files with 78 additions and 64 deletions.
    78 changes: 78 additions & 0 deletions export-named-sheet-as-csv.gs
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,78 @@
    /*
    * script to export data of the named sheet as an individual csv files
    * sheet downloaded to Google Drive and then downloaded as a CSV file
    * file named according to the name of the sheet
    * original author: Michael Derazon (https://gist.github.com/mderazon/9655893)
    */

    function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var csvMenuEntries = [{name: "Download Primary Time File", functionName: "saveAsCSV"}];
    ss.addMenu("Creating a Timetable", csvMenuEntries);
    };

    function saveAsCSV() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Primary Time');
    // create a folder from the name of the spreadsheet
    var folder = DriveApp.createFolder(ss.getName().toLowerCase().replace(/ /g,'_') + '_csv_' + new Date().getTime());
    // 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
    var file = folder.createFile(fileName, csvFile);
    //File downlaod
    var downloadURL = file.getDownloadUrl().slice(0, -8);
    showurl(downloadURL);

    }

    function showurl(downloadURL) {
    var app = UiApp.createApplication().setHeight('60').setWidth('150');
    //Change what the popup says here
    app.setTitle("Your timetable CSV is ready!");
    var panel = app.createPopupPanel()
    //Change what the download button says here
    var link = app.createAnchor('Click here to download', downloadURL);
    panel.add(link);
    app.add(panel);
    var doc = SpreadsheetApp.getActive();
    doc.show(app);
    }

    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);
    }
    }
    64 changes: 0 additions & 64 deletions export-to-csv.gs
    View file
    Original file line number Diff line number Diff line change
    @@ -1,64 +0,0 @@
    /*
    * 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
    * 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 = 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
    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 waiting 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);
    }
    }
  2. @mderazon 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
    View file
    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
  3. @mderazon 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
    View file
    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) {
  4. @mderazon 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
    View file
    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
    */

  5. @mderazon mderazon created this gist Mar 20, 2014.
    65 changes: 65 additions & 0 deletions export-to-csv.gs
    View file
    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);
    }
    }