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;
}
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.
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;
}
data.map(r => r.map(c => c instanceof Date ? c.toString() : c)) to date.csvFileName, sheet are not used.