CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Sadness Kuro
Sadness Kuro

Reputation: 17

Rewrite download CSV script to have delimiter

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 };
}

Upvotes: 0

Views: 168

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15318

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('👉 Download file ...', 'openSidebar')
    .addToUi();
}
function openSidebar() {
  const html = HtmlService.createHtmlOutputFromFile("index").setTitle("DOMNLOAD XSV FILE");
  SpreadsheetApp.getUi().showSidebar(html);
}
function createDataUrl(type) {
  const mimeTypes = { xsv: MimeType.CSV };
  var source = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getDataRange().getValues();
  var sep = '|';
  content = source.map(l => l.join(sep) + '\n').join('');
  var name = SpreadsheetApp.getActiveSpreadsheet().getName() + '.xsv'
  DriveApp.createFile(name, content)
  const blob = DriveApp.getFilesByName(name).next().getBlob();
  return {
    data:
      `data:${mimeTypes[type]};base64,` + Utilities.base64Encode(blob.getBytes()),
    filename: `${name}`,
  };
}

index.html :

<!DOCTYPE html>
<html>
<head>
</head>
<body onload="autoRun()">
  Wait during your file will automatically download ...<br/><br/>
  If any issue, click here <input type="button" value="download as XSV" onclick="download('xsv')" />
<script>
  function download(type) {
    google.script.run
      .withSuccessHandler(({ data, filename }) => {
        const a = document.createElement("a");
        document.body.appendChild(a);
        a.download = filename;
        a.href = data;
        a.click();
      })
      .createDataUrl(type);
  }
  function autoRun(){
    download('xsv')
    window.setTimeout(function(){google.script.host.close()},5000)
  }
</script>
</body></html>

enter image description here

Upvotes: 1

Related Questions