GAS replace comma with tab

Question

I am trying to write a simple app to take the content from one,or a few, row(s) and output a text file. I have 2 questions (see below code snippet):

function saveAsTSV3() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = SpreadsheetApp.getActiveSheet();
  var range = s.getRange("A53:HY54");
  var values = range.getValues();
  var text = values.join('\r\n');
  regex = new RegExp(',','g');
  text = text.replace(regex,'\t');
  DriveApp.createFile("A file.tsv", text, MimeType.PLAIN_TEXT);
}

It does create the file and put it in the right folder.

But, it is filled with commas instead of tab characters.
2. Similarly, I need a line-break character at the end of each row. Presently it puts everything together into one long row.

Anyone know how to do these two things? Any assistance is much appreciated!

Here is a sample spreadsheet.

Are you looking that the column separator be a tab instead of a comma? — Rubén, Oct 29 '17 at 23:58

Rubén · Accepted Answer · 2017-10-31T01:04:31.240

0

getValues return a JavaScript array. One way to get the desired result is

to join the values of the unique row by using \n or \r\n as line separator.
replace the commas inserted by the previous step by usen replace with the g modificator.
to create the file by using MimeType.PLAIN_TEXT instead of MimeType.CSV as the last is intended for comma separated values files.

The code will be similar to the following snipeet:

function saveAsTSV() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = SpreadsheetApp.getActiveSheet();
  var range = s.getRange("A1:C6");
  var values = range.getValues();
  var end_of_line = '\n';
  var text = values.join(end_of_line);
  regex = new RegExp(',','g');
  text = text.replace(regex,'\t');
  DriveApp.createFile("A file.tsv", text, MimeType.PLAIN_TEXT);
}

NOTE: According to answer \n is the end of line for UNIX systems, \r\n for Windows systems. Another answer on the same Q&A thread refers to https://blog.codinghorror.com/the-great-newline-schism/

Saving as CSV through google script handling newline characters

edited Oct 31 '17 at 01:04

answered Oct 30 '17 at 00:31

Rubén

34,714
9
70
166

Thank you. That successfully separated the columns into tabs. Do you know how to separate each row with a break. It may be something like /n. – Neill Oct 30 '17 at 00:39
Ok, I hope I have further clarified #2 in my original question. I am not sure how to further describe the problem. – Neill Oct 30 '17 at 00:50
For some reason, the line-break is two positions too late. In the exported file, Row2 begins with Column C. Thus, the two rows don't line up in the exported file. – Neill Oct 30 '17 at 01:05
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/157765/discussion-between-neill-and-ruben). – Neill Oct 30 '17 at 01:31
1

I got it! It is var text = values.join('\r\n'); – Neill Oct 30 '17 at 03:42
@Neil: The required line separator could vary depending on the target system. I updated my answer. – Rubén Oct 31 '17 at 01:05

GAS replace comma with tab

1 Answers1