How can I include commas when creating CSV files with Google apps script?

Question

I'm working on developing some codes to create CSV files with Google apps script. I know how to create CSV files, but I have a problem when string values have commas. If array elements have commas, those elements will be separated when CSV files are created.

I am working on this code because I would like to upload CSV files to EC websites, such as eBay, amazon and things like that. So the format is fixed by them. There is no way I can avoid using commas in the array elements.

I tried to put "" to brace commas (ex: ","), but it didn't work at all. I also attempted to use different delimiter, but it didn't work either.

My code goes as follows:

  var folderId = DriveFolderId
  var array = [
    ["title1", "title2", "title3"],
    ["value1", "value2-1,value2-2", "value3"],
  ]
  for (var i in array) {
    array[i] = array[i].join(",");
  }
  var array = array.join("\n");
  var folder = DriveApp.getFolderById(folderId);
  var now = Utilities.formatDate(new Date(), "JST", "yyyy_MM_dd_HH_mm_ss");
  folder.createFile(now + ".csv", array, "text/csv");

Codes above works just fine unless array elements don't have commas in it. If they have commas, they are separated when creating CSV files. In the above example, it creates a file like this:

[title1],[title2],[title3]

[value1],[value2-1],[value2-2],[value3]

I want it to be something like this:

[title1],[title2],[title3]

[value1],[value2-1,value2-2],[value3]

When it executes folder.createFile(now + ".csv", array, "text/csv"), it ruins all my efforts.

Does anyone know how to solve this issue?

You need to escape special characters. This is not an issue unique to Google Apps Script — tehhowch
Thank you for your comment, tehhowch. I knew I have to escape some characters but I didn't know which and how. — kurtman

Tanaike Tanaike · Accepted Answer · 2019-06-03T23:00:27

You want to create a CSV file like below using the script in your question.

If my understanding is correct, how about this modification?

Modified script:

var array = [
  ["title1", "title2", "title3"],
  ["value1", "value2-1,value2-2", "value3"],
]
for (var i in array) {
  array[i] = array[i].join(",");
}

var array = [
  ["title1", "title2", "title3"],
  ["value1", "value2-1,value2-2", "value3"],
];

array = array.map(function(e) {return e.map(function(f) {return ~f.indexOf(",") ? '"' + f + '"' : f})}); // Added

for (var i in array) {
  array[i] = array[i].join(",");
}

In this modification, when the value has ,, "value2-1,value2-2" is converted to "\"value2-1,value2-2\"". By this, the result like above image can be retrieved.

Reference:

map()

If this was not the result you want, I apologize.

How can I include commas when creating CSV files with Google apps script?

1 Answers

Modified script:

Reference: