Google app script export CSV from google spreadsheet date format

1
votes

I am using a Google app script to export from spreadsheet to CSV. In my spreadsheet, I have columns with date format: DD/MM/YYYY But in the CSV the date becomes like that: 

Fri Mar 20 2020 00:00:00 GMT+0300 (heure normale d’Afrique de l’Est)

Instead of DD/MM/YYYY

Any help will save my day :-)

In my code I have a line regarding the date, but it doesn't work or transform date data to string? : function isValidDate(date)

The code I use : 

function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var csvMenuEntries = [{
name: "export as csv file",
functionName: "saveAsCSV"
}];
ss.addMenu("CSV Export", csvMenuEntries);
var a1 = ss.getSheetByName("Name").getRange("A1").getValue();
var name = "MyCompanyName_"+a1;
ss.rename(name);  
};

function saveAsCSV() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ssname = ss.getName();
var sheet = ss.getActiveSheet(); 
var sheetname = sheet.getSheetName();
var folder = DriveApp.getFileById(ss.getId()).getParents().next();
var fileName = ssname + ".csv";
var csvFile = so_4225484202(fileName);
folder.createFile(fileName, csvFile);

}


function isValidDate(date) {
return date && Object.prototype.toString.call(date) === "[object 
 Date]" && !isNaN(date);
}
function so_4225484202(filename) {

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var paramsheet = ss.getSheetByName("Parameters");

var linearray = [];
var rowdata = [];
var csv = "";
var fieldvalue = "";

var param = paramsheet.getRange(2, 2, 2);
var paramValues = param.getValues();
var fieldDelimiter = paramValues[0][0];
var textDelimiter = paramValues[1][0];
var rangeData = sheet.getDataRange();
var lastColumn = rangeData.getLastColumn();
var lastRow = rangeData.getLastRow();
var rangeValues = rangeData.getValues();

for (i = 0; i < lastRow; i++) {
for (j = 0; j < lastColumn; j++) {

    var value = rangeValues[i][j];
    var theType = typeof value;

    if (theType === "object") {
        var testdate = isValidDate(value);
        //Logger.log("if typeof is object: testdate: 
"+testdate);//DEBUG
        var testtype = typeof testdate;
        if (testtype === "boolean") {
            // variable is a boolean
            //Logger.log("Its a date");//DEBUG
            theType = "date";
        } else {
            //Logger.log("Its not a date");//DEBUG
        }
    }
    if (theType === "string") {
        value = textDelimiter + value + textDelimiter;
    }

    rowdata.push([value]);
};

//Logger.log("DEBUG: rowdata: "+rowdata);//DEBUG
csv += rowdata.join(fieldDelimiter) + "\n";
var rowdata = [];

};

return csv;

 }
1
dategoogle-apps-scriptexport-to-csv
Can you provide the code you use to export the sheet? – ziganotschka
yes, sorry, I forgot to do it – Jean
I see, there is an easier way to export a spreadsheet to CSV, I will post a sample as an answer. – ziganotschka

1 Answers

3
votes

How to export a spreadsheet to a CSV file and avoid problems with date formatting?

Perform a Urlfetch request to the Files: export method

Sample:

function saveAsCSV() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var ssname = ss.getName();
  var sheet = ss.getActiveSheet(); 
  var folder = DriveApp.getFileById(ss.getId()).getParents().next();
  var fileName = ssname + ".csv";
  var url = "https://docs.google.com/spreadsheets/d/" + ss.getId() + "/export?exportFormat=csv&format=csv";  
  var token = ScriptApp.getOAuthToken();  
  var response = UrlFetchApp.fetch(url + sheet.getSheetId(), {
    headers: {
      'Authorization': 'Bearer ' +  token
    }
  });
  folder.createFile(response.getBlob().setName(fileName));
}