1

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 "|"?

Atabai Fekri
  • 301
  • 2
  • 4
  • 20
Simon GIS
  • 1,045
  • 2
  • 18
  • 37

1 Answers1

2

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

iansedano
  • 6,169
  • 2
  • 12
  • 24
  • You are a savior thanks so much! I will try this and see how it goes! I was having so much trouble finding a solution for that. Question: if I run a trigger every hour by example, it will overwrite the current file or create a copy each time? In my final solution i need to overwrite to simply overwrite the same file...so it doesn't change names. Thanks for you example well detailed! – Simon GIS May 19 '21 at 14:13
  • 1
    A pleasure. As it is it would create a new file each time. I have added an edit to show how you might replace the same file each time. – iansedano May 19 '21 at 15:52
  • Normally on Google sheets, i get the file Id at the end... Example edit#gid=1769972812. Now on google docs, I only get this link https://docs.google.com/document/d/10eI7WAO688nQHRqj7ymscN5I5fJpP4fVTUn_qZMuq3Y/edit so obviously when i put DriveApp.getFileById("[0eI7WAO688nQHRqj7ymscN5I5fJpP4fVTUn_qZMuq3Y]").setContent(csvContent) its not working.... I cant find the file id of this doc... How come? – Simon GIS May 19 '21 at 17:14
  • A google doc is very different from a csv file though. If you want to output csv like content into a Google Doc the process is very different. This answer shows you how to get it into a CSV/PLAIN_TEXT file, I have edited answer again with how to get the ID of any file in drive. – iansedano May 20 '21 at 06:56