I have a google spreadsheet (with multiple sheets) that contains a sheet that I would like to download as sheet_name.csv; I have tried this "https://docs.google.com/spreadsheets/d/{key}/gviz/tq?tqx=out:csv&sheet={sheet_name}" as a link to instantly download the the sheet but it is downloaded as "data.csv" rather than "sheetname.csv". Thus, question 1: is there a way to use this and end up with sheetname.csv as file name automatically rather than having to rename the file manually?
I also saw this code here which is designed to save a range to a csv, it works fine for my purpose but my date and time column lost the formatting. The date turned from "03/11/2020" to "Wed Mar 11 2020 00:00:00 GMT-0600 (Mountain Daylight Time)". The time turned from "3:05:00" to "Sat Dec 30 1899 03:05:00 GMT-0700 (Mountain Standard Time)".
Question 2: How would i keep the formatting unchanged? or could there be another solution and does not require looping through every single cell to create a csv file?
Thanks for your help.
function convertRangeToCsvFile_(csvFileName, sheet) {
// get available data range in the spreadsheet
var activeRange = sheet.getDataRange();
try {
var data = activeRange.getValues();
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] + "\"";
}
}
// 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;
}
catch(err) {
Logger.log(err);
Browser.msgBox(err);
}
}