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?
-
Was my answer of use?– Jacob Jan TuinstraJul 27, 2013 at 18:10
1 Answer
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:
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 ..– PaulJul 4, 2013 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. Jul 4, 2013 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)– PaulJul 4, 2013 at 10:30
-
1
-