Google Sheets - Convert comma to "#" before generate .CSV-CodePudding

I have the following script in a Google Sheet:

/** 
 * Create CSV file of Sheet2
 * Modified script written by Tanaike
 * https://stackoverflow.com/users/7108653/tanaike
 * 
 * Additional Script by AdamD.PE
 * version 13.11.2022.1
 * https://support.google.com/docs/thread/188230855
 */

/** Date extraction added by Tyrone */
const date = new Date();

/** Extract today's date */
let day = date.getDate();
let month = date.getMonth()   1;
let year = date.getFullYear();


if (day < 10) {
    day = '0'   day;
}

if (month < 10) {
    month = `0${month}`;
}

/** Show today's date */
let currentDate = `${day}-${month}-${year}`;
/** Date extraction added by Tyrone */

function sheetToCsvModelo0101() {
  var filename = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetName()   "-01"   " - "   currentDate; // CSV file name
  filename = filename   '.csv';
  var ssid = SpreadsheetApp.getActiveSpreadsheet().getId();

  var folders = DriveApp.getFileById(ssid).getParents();
  var folder;
  if (folders.hasNext()) {
    folder = folders.next();
    var user = Session.getEffectiveUser().getEmail();
    if (!(folder.getOwner().getEmail() == user || folder.getEditors().some(e => e.getEmail() == user))) {
      throw new Error("This user has no write permission for the folder.");
    }
  } else {
    throw new Error("This user has no write permission for the folder.");
  }
  var SelectedRange = "A2:AB3";
  var csv = "";
  var v = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(SelectedRange).getValues();
  v.forEach(function (e) {
    csv  = e.join(",")   "\n";
  });
  var newDoc = folder.createFile(filename, csv, MimeType.CSV);
  console.log(newDoc.getId()); // You can see the file ID.
}

This script basically creates a .CSV file in the same folder where the worksheet is, using the range defined in var SelectedRange.

This script is applied to a button on the worksheet.

The question is: how do I make every comma typed in this spreadsheet be converted into another sign, like # before generating the .CSV file in the folder?

I would also like to know if instead of generating 1 file in the folder it is possible to generate 2 files, each with a name.

CodePudding user response：

If you are doing this to avoid conflicts between the comma in the cells and the csv delimiter then try doing the csv like this:

function sheetToCsv() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0")
  const params = { "method": "GET", "headers": { "Authorization": "Bearer "   ScriptApp.getOAuthToken() } };
  const url = "https://docs.google.com/spreadsheets/d/"   ss.getId()   "/export?gid="   sh.getSheetId()   "&format=csv";
  const r = UrlFetchApp.fetch(url, params);
  const csv = r.getContentText();
  return csv;
}

And then put it back in a spreadsheet like this:

function csvToSheet(csv) {
  const vs = Utilities.parseCsv(csv,',');
  const osh = ss.getSheetByName("Sheet1");
  osh.getRange(1,1,vs.length,vs[0].length).setValues(vs);
}

CodePudding user response：

In the meantime I've found a solution that almost works the way I'd like.

I created 2 functions, one to convert , to # and another to convert # to , again, then after the .csv file creation is complete the script switches back from # to , .

/** 
 * Create CSV file of Sheet2
 * Modified script written by Tanaike
 * https://stackoverflow.com/users/7108653/tanaike
 * 
 * Additional Script by AdamD.PE
 * version 13.11.2022.1
 * https://support.google.com/docs/thread/188230855
 */

var SelectedRange = "A2:AB3";
function searchAndReplace_ToHash() {
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(SelectedRange).createTextFinder(',').replaceAllWith('#');
}

function searchAndReplace_ToComma() {
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(SelectedRange).createTextFinder('#').replaceAllWith(',');
}

function sheetToCsv_02() {
  var filename = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetName()   "-01"   " - "   currentDate; // CSV file name
  filename = filename   '.csv';
  var ssid = SpreadsheetApp.getActiveSpreadsheet().getId();

searchAndReplace_ToHash()

  // I modified below script.
  var folders = DriveApp.getFileById(ssid).getParents();
  var folder;
  if (folders.hasNext()) {
    folder = folders.next();
    var user = Session.getEffectiveUser().getEmail();
    if (!(folder.getOwner().getEmail() == user || folder.getEditors().some(e => e.getEmail() == user))) {
      throw new Error("This user has no write permission for the folder.");
    }
  } else {
    throw new Error("This user has no write permission for the folder.");
  }
  var csv = "";
  var v = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(SelectedRange).getValues();
  v.forEach(function (e) {
    csv  = e.join(",")   "\n";
  });
  var newDoc = folder.createFile(filename, csv, MimeType.CSV);
  console.log(newDoc.getId()); // You can see the file ID.
  searchAndReplace_ToComma()
}

It solves the problem, but it would be perfect if this change was not visible in the spreadsheet.

Is it possible to make this substitution without displaying it in the spreadsheet?

As for your script suggestion, I would like to change as little as possible in this script I'm using, it works exactly the way I need it to work, except for the fact that the commas of words conflict with the column divisions.

Anyway, thank you very much for all your attention and patience!