Google-sheets – How to export CSV without commas in unpopulated cells

csvgoogle sheetsgoogle-apps-scriptgoogle-sheets-arrayformularegex

I have a form converter which simply rearranges columns. I use a script to paste Values only into another sheet for export and uploading onto another website.

However, when I go to export, it seems to save a bunch of commas after the populated cells. As far as I can tell, these cells are and must be empty.

When I open in Excel, and manually clear contents of the empty cells, the commas disappear.

My question is how do I export without saving these commas, just whats in the populated cells?

Here's my export script:

function CopyPasteValues() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('O3:AS90').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Export'), true);
  spreadsheet.getRange('A2').activate();
  spreadsheet.getRange('Converter!O3:AS90').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
  spreadsheet.getRange('F27').activate();
};

Best Answer

Well, that is kind of the standard behaviour when exporting Comma Separated Values from Google Sheets. If you wish to "correct" it, you can run a regex on it and then apply a copy on the values:

=ARRAYFORMULA(REGEXREPLACE(A:A, ",,,,,,|,,,,,|,,,,|,,,|,,", ","))

Related Solutions

Google-sheets – How to add carriage returns to CSV downloads

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);
  }
}

Google-sheets – Export all sheets in a spreadsheet to csv in Google Apps

Couldn't find any built in way to do it so I wrote a script to use in Google Sheets

Just paste it in script editor and run

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 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);
  }
}

See more here: http://drzon.net/export-all-google-sheets-to-csv/

Best Answer

Related Solutions

Google-sheets – How to add carriage returns to CSV downloads

Google-sheets – Export all sheets in a spreadsheet to csv in Google Apps

Related Topic