0

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?

Export form. Cells below should be completely empty

Windows preview, showing commas. should not be there

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

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, ",,,,,,|,,,,,|,,,,|,,,|,,", ","))

0

0

The answers pretty embarrassing. I had an if statement populating " " a bunch of rows instead of "" - i.e where nothing was required. Adding a space will add a comma in CSV. I did not know.

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.