0

I am having issues with google scripts and saving my google sheet as a csv. I tried to convert the sheet to csv using code I found on the internet but when I run it the file type says unknown not csv. Without it saving it to csv the google sheet is useless. Here is the code online that I found. NOT MY CODE BUT CODE I'VE Been trying to use. 

   /*
     * 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_');
      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);
    var files = folder.getFiles();
    while (files.hasNext()){
      var file = files.next();
      var destination = DriveApp.getFolderById("0B21i2lCR63QPTGdrcE9rMFgtLVk");
      destination.addFile(file);
      /*
      var pull = folder;
      pull.removeFile(file);
      */
    }


  }

  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);
  }
}
Ralph Legge
  • 1

2 Answers2

0

I believe that this link may help you: http://www.instructables.com/id/Export-data-from-google-spreadsheet-in-required-CS/ (If you can't use PHP then here's the download link: http://php.net/downloads.php). I assume you don't need the script to be in javascript.

  • Thank you, but i'd need the created csv file to be a google folder not in a php connected site. – Ralph Legge Aug 17 '16 at 22:37
  • https://developers.google.com/apps-script/articles/docslist_tutorial#section3 is where I assume you got the script from? –  Aug 17 '16 at 22:44
  • Yes, and also from the guy in the first comment – Ralph Legge Aug 17 '16 at 23:01
  • This might help. (Exports current selection) http://stackoverflow.com/questions/27150751/google-spreadsheets-columns-export-to-csv –  Aug 18 '16 at 02:56
0

I figured it out. looks like all I have to do is add MimeType.CSV to the end of the createFile method. Now it saves it as a csv definitely. 

folder.createFile(fileName, csvFile,MimeType.CSV);
Ralph Legge
  • 1