How can I export to .csv with pipe delimiter

Question

I use Google Sheets (spreadsheet) to combine article data for different sources for my Gambio shop. To import the data I need the pipe symbol as delimiter / separator and " as text delimiter in a .csv file. In the Google Sheets menu for exporting to .csv there are no Options.

Is there a way to export to .csv with pipe separators in Google Sheets?

Answer 1

There are several ways to export a spreadsheet and/or a sheet from Google Sheets. Exporting a sheet as a csv file is built-in to Google Sheets (File, Download, CSV).

In this case, the OP introduces two complications that are not catered for by the "standard" methods.
1) the fields to be delimited by the 'pipe' character (|), and
2) all string fields to be enclosed in double quotes.

There are several scripts on GitHub that offer automation of the process of saving a sheet as csv. export-named-sheet-as-csv.gs by Michael Derazon (https://gist.github.com/mderazon/9655893) is an example, and I used this as a basis for this code. However, these scripts follow the "normal" rules of using a comma as the field delimiter, and no special treatment of strings.

The following code will save the active sheet as a csv file, and provides for pipe field delimiters and double quotes around strings. These parameters can be dictated by the user by editing fields on the Parameters sheet as seen in this screenshot.

---

The script uses typeof to identify strings, and a function isValidDate noted by Dmytro Shevchenko in Detecting an “invalid date” Date instance in JavaScript.

/*
 * 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
 * source: https://gist.github.com/mderazon/9655893
 * adapted by Ted Bell for https://stackoverflow.com/questions/49248498/how-can-i-export-to-csv-with-pipe-delimiter
 */
function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var csvMenuEntries = [{
        name: "export as csv file",
        functionName: "saveAsCSV"
    }];
    ss.addMenu("CSV Export", csvMenuEntries);
};

function saveAsCSV() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var ssname = ss.getName();
    var sheet = ss.getActiveSheet();
    var sheetname = sheet.getSheetName();
    //Logger.log("DEBUG: the name of the spreadsheet is "+ssname);//DEBUG
    //Logger.log("DEBUG: the sheet name is "+sheetname);// DEBUG

    //// create a folder from the name of the spreadsheet
    var folder = DriveApp.createFolder(ssname.toLowerCase() + '_' + sheetname.toLowerCase().replace(/ /g, '_') + '_csv_' + new Date().getTime());
    //Logger.log("DEBUG: the folder name is "+folder);//DEBUG

    // append ".csv" extension to the sheet name
    var fileName = ssname + '_' + sheetname + ".csv";

    // convert all available sheet data to csv format
    var csvFile = so_4225484202(fileName);

    // 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 isValidDate(date) {
    return date && Object.prototype.toString.call(date) === "[object Date]" && !isNaN(date);
}


function so_4225484202(filename) {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var paramsheet = ss.getSheetByName("Parameters");

    var linearray = [];
    var rowdata = [];
    var csv = "";
    var fieldvalue = "";

    var param = paramsheet.getRange(2, 2, 2);
    var paramValues = param.getValues();
    //Logger.log("DEBUG: parameters = "+param.getA1Notation());//DEBUG
    var fieldDelimiter = paramValues[0][0];
    var textDelimiter = paramValues[1][0];
    //Logger.log("DEBUG: field delimiter: "+fieldDelimiter+", text delim: "+textDelimiter);//DEBUG


    var rangeData = sheet.getDataRange();
    var lastColumn = rangeData.getLastColumn();
    var lastRow = rangeData.getLastRow();
    //Logger.log("DEBUG: lastColumn: "+lastColumn+", lastRow: "+lastRow);//DEBUG

    // Get array of values in the Data Range
    var rangeValues = rangeData.getValues();

    // Loop through array and build values for csv
    for (i = 0; i < lastRow; i++) {
        for (j = 0; j < lastColumn; j++) {

            var value = rangeValues[i][j];
            var theType = typeof value;

            if (theType === "object") {
                var testdate = isValidDate(value);
                //Logger.log("if typeof is object: testdate: "+testdate);//DEBUG
                var testtype = typeof testdate;
                if (testtype === "boolean") {
                    // variable is a boolean
                    //Logger.log("Its a date");//DEBUG
                    theType = "date";
                } else {
                    //Logger.log("Its not a date");//DEBUG
                }
            }
            if (theType === "string") {
                value = textDelimiter + value + textDelimiter;
            }

            rowdata.push([value]);
        };

        //Logger.log("DEBUG: rowdata: "+rowdata);//DEBUG
        csv += rowdata.join(fieldDelimiter) + "\n";
        var rowdata = [];

    };

    //Logger.log("DEBUG: csv: "+csv);//DEBUG
    return csv;

}

This spreadsheet contains sample data. A sheet containing almost 1,000 records is processed and saved in about 5 seconds.