EN VI

Google Sheet Export Range container Special Characters as CSV?

2024-03-14 09:00:07
How to Google Sheet Export Range container Special Characters as CSV

On a previous post I was able to accomplish part of my objective but I am encountering issues with the data. The script is intended to create a CSV export where specific columns of data are exported based on their header values and the header values are changed in the exported data.

For example, I want to export the column data from the column with the header "Product Name - Full" but in the export I need the header "Product Name - Full" changed to "Description 1".

The issue that is occurring is that I have multiple fields that contain punctuation marks such as commas, quotation marks, etc. and currently the quotation marks are resulting in an error. The script is changing the first quotation mark to ï and then if there is a second quotation mark it is pushing the data over to the next column.

Here is a link to the test workbook. The script is below.

function Export_Database() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName('Database');
  var folderTime = Utilities.formatDate(new Date(), "GMT-8", "yyyy-MM-dd'_'HH:mm:ss")   // Logger 1-3 
  var folder = DriveApp.createFolder(ss.getName().toLowerCase().replace(/ /g,'_') + '_csv_' + folderTime);
  var fileName = sheet.getName() + ".csv";
  var csvFile = Convert_Database(fileName, sheet);                              //****REF
  var file = folder.createFile(fileName, csvFile);
  var downloadURL = file.getDownloadUrl().slice(0, -8);
  Export_DatabaseURL(downloadURL);                                               //****REF
  //1 Logger.log("DEBUG: Folder date = "+folderTime) // DEBUG
  //2 Logger.log("DEBUG: Proposed folder name: "+ss.getName().toLowerCase().replace(/ /g,'_') + '_csv_' + folderTime) // DEBUG
  //3 Logger.log("DEBUG: Proposed file name: "+sheet.getName() + ".csv") // DEBUG
}

function Export_DatabaseURL(downloadURL) {                                       //****REF
  var link = HtmlService.createHtmlOutput('<a href="' + downloadURL + '">Click here to download</a>');
  SpreadsheetApp.getUi().showModalDialog(link, 'Your CSV file is ready!');
}


function Convert_Database(csvFileName, sheet) {
  const wb = SpreadsheetApp.getActiveSpreadsheet();  
  const sh = wb.getSheetByName("Database");
  const allvalues = sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn()).getValues()         // Logger 1 | Get values (Row,Column,OptNumRows,OptNumColumns) 
  
  const header1 = allvalues[0].indexOf("SKU")                                               // get the column Index of the headers
  const header2 = allvalues[0].indexOf("Product Name - Full") 
  const header3 = allvalues[0].indexOf("Date Updated")  
  const header4 = allvalues[0].indexOf("Purchase Unit")
  const header5 = allvalues[0].indexOf("PRICE1")      
  const header6 = allvalues[0].indexOf("Conversion") 
  const header7 = allvalues[0].indexOf("Delivery Product Name")
  const header8 = allvalues[0].indexOf("Delivery Product Description")
                                           
  allvalues[0][header2] = "Description 1"                   // Assign replacement values
  allvalues[0][header3] = "Date"
  allvalues[0][header4] = "P. Unit"
  allvalues[0][header5] = "Price 1"
  allvalues[0][header6] = "Conv"
  allvalues[0][header7] = "Short Description"
  allvalues[0][header7] = "Long Description"

  // extract only the columns that relate to the headers 
  var data = allvalues.map(function(o){return [  
  o[header1],o[header2],o[header3],o[header4],o[header5],o[header6],o[header7],o[header8]
  ]})          
  
  // convert double quotes to unicode
  //loop over the rows in the array
  for (var row in data) {
    //use Array.map to execute a replace call on each of the cells in the row.
    var data_values = data[row].map(function(original_datavalue) {
      return original_datavalue.toString().replace('"', '"');
    })
    //replace the original row values with the replaced values
    data[row] = data_values;
  }

  // wrap any value containing a comma in double quotes
  data = data.map(function(e) {return e.map(function(f) {return ~f.indexOf(",") ? '"' + f + '"' : f})})
  
  // Logger.log("data rows = "+data.length+", data columns = "+data[0].length)

  var csvFile = undefined
  // loop through the data in the range and build a string with the csv data
    if (data.length > 1) {
      var csv = "";
      for (var dataRow = 0; dataRow < data.length; dataRow++) {

        // join each row's columns
        // add a carriage return to end of each row, except for the last one
        if (dataRow < data.length-1) {
          // valid data row
          csv += data[dataRow].join(",") + "\r\n";
          //Logger.log("DEBUG: row#"+dataRow+", csv = "+data[dataRow].join(",") + "\r\n")
        }
        else {
          csv += data[dataRow];
        }
      }
      csvFile = csv;
    }
    return csvFile;
}

Solution:

In your situation, as a simple modification, how about converting the sheet to CSV data using an endpoint? When this is reflected in your showing script, it becomes as follows.

Please modify your function Convert_Database as follows.

Modified script:

function Convert_Database(csvFileName, sheet) {
  const wb = SpreadsheetApp.getActiveSpreadsheet();
  const sh = wb.getSheetByName("Database");
  const allvalues = sh.getRange(1, 1, sh.getLastRow(), sh.getLastColumn()).getValues();

  const header1 = allvalues[0].indexOf("SKU");
  const header2 = allvalues[0].indexOf("Product Name - Full");
  const header3 = allvalues[0].indexOf("Date Updated");
  const header4 = allvalues[0].indexOf("Purchase Unit");
  const header5 = allvalues[0].indexOf("PRICE1");
  const header6 = allvalues[0].indexOf("Conversion");
  const header7 = allvalues[0].indexOf("Delivery Product Name");
  const header8 = allvalues[0].indexOf("Delivery Product Description");

  allvalues[0][header2] = "Description 1";
  allvalues[0][header3] = "Date";
  allvalues[0][header4] = "P. Unit";
  allvalues[0][header5] = "Price 1";
  allvalues[0][header6] = "Conv";
  allvalues[0][header7] = "Short Description";
  allvalues[0][header7] = "Long Description";

  var data = allvalues.map(function (o) { return [o[header1], o[header2], o[header3], o[header4], o[header5], o[header6], o[header7], o[header8]] });


  // --- I modified the below script.
  const temp = wb.insertSheet();
  temp.getRange(1, 1, data.length, data[0].length).setValues(data.map(r => r.map(c => c instanceof Date ? c.toString() : c)));
  SpreadsheetApp.flush();
  const url = `https://docs.google.com/spreadsheets/export?id=${wb.getId()}&exportFormat=csv&gid=${temp.getSheetId()}`;
  const csvFile = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getContentText();
  wb.deleteSheet(temp);
  return csvFile;
}
  • When this script is run with your provided Spreadsheet, it seems that each cell values are correctly converted. But, please confirm it again.
  • If you want to use the display values of date values, please modify data.map(r => r.map(c => c instanceof Date ? c.toString() : c)) to date.
  • By the way, in your script, it seems that the values of csvFileName, sheet are not used.
Answer

Login


Forgot Your Password?

Create Account


Lost your password? Please enter your email address. You will receive a link to create a new password.

Reset Password

Back to login