Home > Software design >  Rewrite download CSV script to have delimiter
Rewrite download CSV script to have delimiter

Time:04-02

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('           
  • Related