csvgoogle-sheetsgoogle-apps-script
Google Sheet Export range as CSV change header value
My objective is to export specific columns of data from my sheet as a CSV file. The columns that are exported are designated by their column header name. This is required as new columns are occasionally added/removed from the sheet. In the export it is also important that these header values are changed as the program that the CSV is being imported to requires specific header values that are not always the same as what is used in my sheet. As there are over 20 different types of exports that need to be done from the sheet it does not make sense to create queries on separate tabs for download. As I have shown in the example file and script, this CSV export also requires that a From & To Date column is added to the data.
In short, I need to export columns based on their header value and change the header value that shows in the export.
So far the script works as it creates a new folder and file with a CSV containing the columns that I need based on the column headers which can then be downloaded using a url link. Where I am stuck is:
- I need to change the column headers in the CSV export. For example, I need the header for column 2 to be "Short Description" but in the sheet that I am exporting from the header is "Delivery Product Name".
- Preferably, I would like to delete the folder and file that are created in order to generate the link which is used to download the CSV. This is only a preference as the users could just manually delete the folder.
Example workbook: https://docs.google.com/spreadsheets/d/1k83XFeeItHgKvC9O9-YwY1pQQjb52E3-dnt0w4gCJnM/edit?usp=sharing
function WebInterface_CSVB() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Database');
var folder = DriveApp.createFolder(ss.getName().toLowerCase().replace(/ /g,'_') + '_csv_' + new Date().getTime());
fileName = sheet.getName() + ".csv";
var csvFile = Convert_WebInterfaceB(fileName, sheet);
var file = folder.createFile(fileName, csvFile);
var downloadURL = file.getDownloadUrl().slice(0, -8);
WebInterface_urlB(downloadURL);
}
function WebInterface_urlB(downloadURL) {
var link = HtmlService.createHtmlOutput('<a href="' + downloadURL + '">Click here to download</a>');
SpreadsheetApp.getUi().showModalDialog(link, 'Your CSV file is ready!');
}
function Convert_WebInterfaceB(csvFileName, sheet) {
const wb = SpreadsheetApp.getActiveSpreadsheet();
const sh = wb.getSheetByName("Database");
const allvalues = sh.getRange(1,1,sh.getLastRow()-1,sh.getLastColumn()).getValues() // Get values (Row,Column,OptNumRows,OptNumColumns)
const header1 = sh.getRange(1, 1, 1,sh.getLastColumn()).getValues().flat().indexOf("SKU");
const header2 = sh.getRange(1, 1, 1,sh.getLastColumn()).getValues().flat().indexOf("Delivery Product Name");
const header3 = sh.getRange(1, 1, 1,sh.getLastColumn()).getValues().flat().indexOf("Delivery Product Description");
var date1 = Utilities.formatDate(new Date(), "GMT+1", "yyyy-MM-dd")
var date2 = Utilities.formatDate(new Date(new Date().setFullYear(new Date().getFullYear() + 1)), "GMT+1", "yyyy-MM-dd")
var data = allvalues.map(function(o){return [o[header1],o[header2],o[header3],date1,date2]});
try {
var csvFile = undefined;
if (data.length > 1) {
var csv = "";
for (var row = 0; row < data.length; row++) {
for (var col = 0; col < data[row].length; col++) {
if (data[row][col].toString().indexOf(",") != -1) {
data[row][col] = "\"" + data[row][col] + "\"";
data[row][col] = "\"" + data[row][col] + "\"";
}
}
if (row < data.length-1) {
csv += data[row].join(",") + "\r\n";
}
else {
csv += data[row];
}
}
csvFile = csv;
}
return csvFile;
}
catch(err) {
Logger.log(err);
Browser.msgBox(err);
}
}
[1]: https://docs.google.com/spreadsheets/d/1k83XFeeItHgKvC9O9-YwY1pQQjb52E3-dnt0w4gCJnM/edit?usp=sharing
Solution
You want to change the header values in certain columns, and add additional columns containing start and end dates.
function WebInterface_CS: Very minor (possibly unnecessary) changes only.
- formatted a different
timevalue to use for file save; probably can be ignored, but I couldn't follow the time values in the original script.
function Convert_WebInterface: No changes after try (line 77)
- shortened/simplified
indexOfcode; used the existing data array - assigned replacement header values
allvalues[0][header2] = "Short Description"and following
- created variables for the start and end dates;
var startingDateand following - created variables for start and end headers;
var header4 = "Start Date"and following - created temporary arrays for the start and end dates, added the headers, and used a loop to add the date values;
var startArray = []and following - added the temporary arrays to the existing (3 column) data array;
data[i].push(startArray[i][0],endArray[i][0])and following
The result is a 2D array (data) that is ready for converting to CSV and saving.
I
function WebInterface_CSV() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Database');
var folderTime = Utilities.formatDate(new Date(), "GMT-8", "yyyy-MM-dd'_'HH:mm:ss")
//Logger.log("DEBUG: Folder date = "+folderTime) // DEBUG
//Logger.log("DEBUG: Proposed folder name: "+ss.getName().toLowerCase().replace(/ /g,'_') + '_csv_' + folderTime) // DEBUG
//Logger.log("DEBUG: Proposed file name: "+sheet.getName() + ".csv") // DEBUG
var folder = DriveApp.createFolder(ss.getName().toLowerCase().replace(/ /g,'_') + '_csv_' + folderTime);
var fileName = sheet.getName() + ".csv";
var csvFile = Convert_WebInterface(fileName, sheet);
var file = folder.createFile(fileName, csvFile);
var downloadURL = file.getDownloadUrl().slice(0, -8);
WebInterface_url(downloadURL);
}
function WebInterface_url(downloadURL) {
var link = HtmlService.createHtmlOutput('<a href="' + downloadURL + '">Click here to download</a>');
SpreadsheetApp.getUi().showModalDialog(link, 'Your CSV file is ready!');
}
function Convert_WebInterface(csvFileName, sheet) {
const wb = SpreadsheetApp.getActiveSpreadsheet();
const sh = wb.getSheetByName("Database");
const allvalues = sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn()).getValues() // Get values (Row,Column,OptNumRows,OptNumColumns)
// Logger.log("DEBUG: Database range = "+sh.getRange(1,1,sh.getLastRow(),sh.getLastColumn()).getA1Notation())
// get the column Index of the headers
const header1 = allvalues[0].indexOf("SKU")
const header2 = allvalues[0].indexOf("Delivery Product Name")
const header3 = allvalues[0].indexOf("Delivery Product Description")
//Logger.log("DEBUG: Header Index values: #1 = "+header1+", #2 = "+header2+", #3 = "+header3)
// Assign replacement values
allvalues[0][header2] = "Short Description"
allvalues[0][header3] = "Long Description"
// extract only the columns that relate to the headers
var data = allvalues.map(function(o){return [o[header1],o[header2],o[header3]]})
// add the Start/End dates
var startingDate = Utilities.formatDate(new Date(), "GMT-8", "yyyy-MM-dd")
var startingDatePlusOne = new Date((new Date()).valueOf() + 1000*3600*24);
var endingDate = Utilities.formatDate(startingDatePlusOne, "GMT-8", "yyyy-MM-dd")
// Logger.log("DEBUG: start date = "+startingDate+", ending date = "+endingDate)
// add headers for the Start and End dates
var header4 = "Start Date"
var header5 = "End date"
// create temporary arrays to hold the date values
var startArray = []
var endArray = []
// push the headers onto the arrays
startArray.push([header4])
endArray.push([header5])
// get the number of rows for the loop
var lr = sh.getLastRow()
// Logger.log("DEBUG: the last row is "+lr)
// build the temporary arrays
// start=1 to ignore header
for (var i=1;i<lr;i++){
startArray.push([startingDate])
endArray.push([endingDate])
}
// Logger.log(startArray) // DEBUG
// Logger.log(endArray) // DEBUG
``// add the start and end date array to the data
for( var i=0;i<12;i++){
data[i].push(startArray[i][0],endArray[i][0])
}
// Logger.log(data) // DEBUG
try {
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 row = 0; row < data.length; row++) {
for (var col = 0; col < data[row].length; col++) {
if (data[row][col].toString().indexOf(",") != -1) {
data[row][col] = "\"" + data[row][col] + "\"";
data[row][col] = "\"" + data[row][col] + "\"";
}
}
// join each row's columns
// add a carriage return to end of each row, except for the last one
// if (row < data.length) {
if (row < data.length-1) {
csv += data[row].join(",") + "\r\n";
}
else {
csv += data[row];
}
}
csvFile = csv;
}
return csvFile;
}
catch(err) {
Logger.log(err);
Browser.msgBox(err);
}
}
///// Alternate Script if Needed;
// Quotes themselves must be escaped (double them -> " becomes "")
// Commas must be escaped (put string in quotes)
//New lines must be escaped (put string in quotes)
function convertRangeToCsvFile_(csvFileName, sheet) {
try {
var data = sheet.getDataRange().getDisplayValues();
if (data.length > 1) {
var rows = [];
data.forEach(row => {
var cols = [];
row.forEach(col => {
cols.push(`"${col.replace(/"/g, '""')}"`);
});
rows.push(cols.join(','));
});
return rows.join('\n');
}
} catch(err) {
Logger.log(err);
Browser.msgBox(err);
}
}
SAMPLE-OUTPUT
REVISION#2 TO CATER FOR FIELDS CONTAINING COMMAS
Some data cells contain commas and these commas should be retained in the CSV file. The solution for this is in two parts:
- a map function to wrap the entire cell in double quotes; as explained by @Tanaike in How can I include commas when creating CSV files with Google apps script?
- modifying the data loop to insert
"\r\n"ONLY between each row
Issues
Some cells (such as the product description for SKU: 817192) contain commas AND double quotes. This something for the OP to resoolve.
function Convert_WebInterface(csvFileName, sheet) {
const wb = SpreadsheetApp.getActiveSpreadsheet();
const sh = wb.getSheetByName("Database");
var lc = sh.getLastColumn()
var lr = sh.getLastRow()
var data = sh.getRange(1,1,lr,lc).getValues()
// get the column Index of the headers
const header1 = data[0].indexOf("SKU")
const header2 = data[0].indexOf("Delivery Product Name")
const header3 = data[0].indexOf("Delivery Product Description")
const header4 = data[0].indexOf("PL Region/Country")
// Logger.log("DEBUG: Header Index values: #1 = "+header1+", #2 = "+header2+", #3 = "+header3+", #4 = "+header4)
// Assign replacement values
data[0][header2] = "Short Description"
data[0][header3] = "Long Description"
// extract only the columns that relate to the headers
var data = data.map(function(o){return [o[header1],o[header2],o[header3],o[header4]]})
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 row = 0; row < data.length; row++) {
// join each row's columns
// add a carriage return to end of each row, except for the last one
if (row < data.length-1) {
csv += data[row].join(",") + "\r\n";
}
else {
csv += data[row];
}
}
csvFile = csv;
}
return csvFile;
}
SAMPLE OUTPUT
REVISION#3: convert existing double quotes to Unicode
/*
// convert data to csv
// REVISION#1
// limit array columns to nominated header values
// REVISION#2
// some cells contain commas;
// wrap and cell containing a comma in double quotes
// REVISION#3
// some cells contain double quotes;
// convert existing double quotes to unicode
*/
function Convert_WebInterface(csvFileName, sheet) {
const wb = SpreadsheetApp.getActiveSpreadsheet();
const sh = wb.getSheetByName("Database");
var lc = sh.getLastColumn()
var lr = sh.getLastRow()
var data = sh.getRange(1,1,lr,lc).getValues()
// get the column Index of the headers
const header1 = data[0].indexOf("SKU")
const header2 = data[0].indexOf("Delivery Product Name")
const header3 = data[0].indexOf("Delivery Product Description")
const header4 = data[0].indexOf("PL Region/Country")
// Logger.log("DEBUG: Header Index values: #1 = "+header1+", #2 = "+header2+", #3 = "+header3+", #4 = "+header4)
// Assign replacement values
data[0][header2] = "Short Description"
data[0][header3] = "Long Description"
// extract only the columns that relate to the headers
var data = data.map(function(o){return [o[header1],o[header2],o[header3],o[header4]]})
// 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;
}
- Viewing a very large CSV file?
- Powershell ConvertFrom-JSON to csv file(s)
- Getting "An error occurred while calling o58.csv" error while writing a spark dataframe into a csv file
- How can I turn a DataTable to a CSV?
- Populate a 2d array with qualifying data from a multi-line txt file containing comma-separated values
- How to retrieve values form csv file in Netlogo?
- How to write HashMap to CSV?
- How to export SSRS report directly to csv without rendering
- PHP league/csv Reader How to know which delimiter was used?
- How import also column name in CSV file
- Export .csv file (with headers) from PostgreSQL table using PHP PDO, first data row missing
- Replace commas not inside single quotes with an @ symbol
- Split string on commas not inside double quotes
- I keep on getting a KeyError in Python
- Split a CSV where some entries have double quotes
- PostgreSQL COPY command from csv file problem for array data
- Rearrange columns using cut
- Convert a .sav file to .csv file in Python
- Comparing two csv files by column and value and displaying line numbers of differing values
- Adapt existing script for converting csv to html table to include <tfoot>
- Is there any free tool to convert a file with more than 65000 registers from DBF format to CSV?
- Parsing a CSV file using gawk
- How to concatenate text from multiple rows into a single text string in SQL Server
- How to copy all files with specific extension and rename them adding the subdirectory names
- interpret hex as numbers, when importing csv into sqlite3
- Modifing a Pandas Dataframe using Pivot Tables or Group By
- polars.read_csv() with german number formatting
- convert a fixed width file from text to csv
- ANGULAR 5 : how to export data to csv file
- Parsing CSV file in Swift