CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheetsexport-to-csv
Simon GIS
Simon GIS

Reputation: 1055

Google App Script export CSV to a customer Delimiter

I have this script to export my google sheets to CSV file. I would like to export has a CSV with a customer delimited tab "|".

var pasteDataRequest = Sheets.newPasteDataRequest();
  pasteDataRequest.coordinate = {
    sheetId: SpreadsheetApp.getActiveSheet().getSheetId(),
    rowIndex: 0,
    columnIndex: 0
  };
  pasteDataRequest.data = data;
  pasteDataRequest.type = SpreadsheetApp.CopyPasteType.PASTE_VALUES;
  pasteDataRequest.delimiter = '|';

I have the full script here.

If i do export in the current way, its still split by seperated comma ",".

How u I can export my data demilited in csv with "|"?

Upvotes: 1

Views: 2125

Answers (1)

iansedano
iansedano

Reputation: 6481

How to ouput a text file similar to a CSV but with a custom delimiter

Here is a very simple method to ouput your file as a type of CSV-like file but instead of being separated with , is separated with | or any delimiter you want.

function createCsvContent(values, delimiter) {

  // This converts the values 2D array into a simple array of strings delimited by the delimiter
  const stringArray = values.map(row => row.join(delimiter))
  // Then it joins all the strings with newlines
  const output = stringArray.join("\n")

  return output
}


function createCsv() {
  // Get all the values from the sheet as a 2D array
  const file = SpreadsheetApp.getActive();
  const sheet = file.getSheetByName("Sheet1");
  const range = sheet.getDataRange();
  const values = range.getValues();

  // call the function to create the csv-like content
  const csvContent = createCsvContent(values, "|")

  // create the file in drive
  DriveApp.createFile('csvFile', csvContent, MimeType.PLAIN_TEXT)
}

Note that the last line creates the file as a plain text file. I was going to make this CSV but since its no longer comma-separated, it can no longer be classified as a CSV file. Though if you change this, it will work all the same.

The main creation happens in the createCsvContent function. It uses the map method of arrays to transform the 2D array that is obtained from the spreadsheet into a simple array of strings.

From:

[
    [1,2,3],
    [4,5,6],
    [7,8,9],
]

To:

[
    "1|2|3",
    "4|5|6",
    "7|8|9",
]

Then finally using another join on the main array to transform it to:

"1|2|3\n4|5|6\n7|8|9"

The \n means "newline", in the end it will be rendered like this:

1|2|3
4|5|6
7|8|9

Then its a case of calling DriveApp.createFile with the name of the file you want, the content as generated by the createCsvContent function, and the mime type.

Edit

To replace the same file each time, instead of:

// create the file in drive
DriveApp.createFile('csvFile', csvContent, MimeType.PLAIN_TEXT)

You would need to get the file id of the "csv" and use getFileById and setContent:

DriveApp.getFileById("[FILE_ID]").setContent(csvContent)

setContent()

EDIT 2

To get the ID of any file in Drive you first go to drive and right click on the file you want. Click on "Get Link", then here you will find the ID:

enter image description here

References

Upvotes: 2

Related Questions