2

In reference to this question: Google Spreadsheet downloaded as CSV comes out on 1 line?, is there any way to have a downloaded Google Spreadsheets incorporate carriage returns and linefeeds such that if the CSV is opened in Notepad on Windows it appears as multi-line?

2

On the Google Developer site, Google Apps Script, the following tutorial can be found: Interacting With Your Docs List. In section 3, it describes how to save a selected range to CSV.

This particular line of code introduces a carriage return:

// 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];
}

If you run this script and select a particular range, the CSV file will show up (in notepad) having line breaks:

enter image description here

I've added the code below and only revised the onOpen script:

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

function saveAsCSV() {
  // Prompts the user for the file name
  var fileName = Browser.inputBox("Save CSV file as (e.g. myCSVFile):");

  // Check that the file name entered wasn't empty
  if (fileName.length !== 0) {
    // Add the ".csv" extension to the file name
    fileName = fileName + ".csv";
    // Convert the range data to CSV format
    var csvFile = convertRangeToCsvFile_(fileName);
    // Create a file in the Docs List with the given name and the CSV data
    DocsList.createFile(fileName, csvFile);
  }
  else {
    Browser.msgBox("Error: Please enter a CSV file name.");
  }
}

function convertRangeToCsvFile_(csvFileName) {
  // Get the selected range in the spreadsheet
  var ws = SpreadsheetApp.getActiveSpreadsheet().getActiveSelection();
  try {
    var data = ws.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);
  }
}
  • Hmm, I'm not keen to get involved with "Development" in relation to Google apps, but if that's the only way, it's the only way .. – Paul Jul 4 '13 at 10:21
  • 1
    @Paul You have seen that the standard Google export doesn't result in a multi-line CSV format. Under Tools, Script editor, you can add the script. Then press the "bug" button to authenticate the script. – Jacob Jan Tuinstra Jul 4 '13 at 10:25
  • Aye aye, can't properly assess your answer where I am now, I'm sure I'll be accepting when I get home. Thankyou. (Edit: Annoyingly I can't even up-vote at 13rep) – Paul Jul 4 '13 at 10:30
  • 1
    @Paul solved !! – Jacob Jan Tuinstra Jul 4 '13 at 10:33
  • Hoped you'd take the hint :p – Paul Jul 4 '13 at 10:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.