I have this script to export my google sheet to a CSV file. I want to output a custom delimiter CSV of tabs and line breaks.
function openSidebar() {
const html = HtmlService.createHtmlOutputFromFile("download").setTitle("MENU");
SpreadsheetApp.getUi().showSidebar(html);
}
function createDataUrl(type) {
const mimeTypes = { csv: MimeType.PLAIN_TXT, };
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();
let url = null;
if (type == "csv") {
url = `https://docs.google.com/spreadsheets/d/${ss.getId()}/gviz/tq?tqx=out:csv&gid=${sheet.getSheetId()}`;
}
if (url) {
const blob = UrlFetchApp.fetch(url, {
headers: { authorization: `Bearer ${ScriptApp.getOAuthToken()}` },
}).getBlob();
return {
data:
`data:${mimeTypes[type]};base,`
Utilities.baseEncode(blob.getBytes()),
filename: `${sheet.getSheetName()}.csv`,
};
}
return { data: null, filename: null };
}
CodePudding user response:
To create try, with separator (sep) and break line (br)
function createXSVFile() {
var data = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getDataRange().getValues();
var sep = '|';
var br = '\n';
content = data.map(l => l.join(sep) br).join('');
DriveApp.createFile('myXSVFile.xsv', content)
}
to retrieve the file by email
function createAndSendXSVFile() {
var emailAddress = '[email protected]'
var data = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getDataRange().getValues();
var sep = '|';
var br = '\n';
content = data.map(l => l.join(sep) br).join('');
var name = SpreadsheetApp.getActiveSpreadsheet().getName() '.xsv'
var file = DriveApp.createFile(name, content)
MailApp.sendEmail(emailAddress, 'your file ' name, 'Please, find hereafter your file',
{ attachments: DriveApp.getFilesByName(name).next().getBlob() }
);
}
To create and download the file
gs :
function onOpen() {
SpreadsheetApp.getUi().createMenu('⇩ M E N U ⇩')
.addItem('