RLearnsR RLearnsR - 4 months ago 17
Javascript Question

Problems with formatting date by replacing dots to slashes

I have two columns which are

row[0]
and
row[1]
in my spreadsheet on Google Spreadsheets. The dates inside them are preliminary written as
dd.mm.yyyy
while I want to replace
.
by
/
inside them automatically and get dates formatted as
dd/mm/yyyy
and nevertheless don't convert these
dd/mm/yyyy
to, for example,
Fri Sep 02 2016 00:00:00 GMT+0300 (MSK)
in second iteration of the script.

I searched for this on Stackoverflow and found a lot of tips but unfortunately I've not succeeded in achieving desired outcome.

Here is what I found and what I tried to add: Replacing all dots in a string with backslashes in Java. The same negative effect has been observed.

Here is my source code:

function FormatCheckInAndCheckOutDates() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // Строка с первым бронированием для начала обработки
var numRows = sheet.getLastRow()-1; // Количество строк с бронированиями для обработки
var dataRange = sheet.getRange(startRow, 1, numRows, 3); // Охват столбцов для обработки ячеек, в данном случае с 1-го по 3-й.
var data = dataRange.getValues(); // Получить значение ячеек в каждой ячейке каждой строке в рамках столбцов, охваченных верхним кодом.
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var checkInDate = row[0].toString().valueOf();
var checkOutDate = row[1].toString().valueOf();
var replaceDotsInCheckIn = checkInDate.replace(".", "/");
var replaceDotsInCheckOut = checkOutDate.replace(".", "/");
sheet.getRange(startRow + i, 1).setValue(replaceDotsInCheckIn);
sheet.getRange(startRow + i, 2).setValue(replaceDotsInCheckOut);
SpreadsheetApp.flush(); // Здесь завершается обновление ячеек 17-го столбца, в котором проставляется статус отправки уведомлений.
Logger.log("Произведена замена точек на слэши в датах заезда и выезда всех существующих бронирований.");
}
}


How can I prevent transforming
02.09.2016
to
Fri Sep 02 2016 00:00:00 GMT+0300 (MSK)
when I secondly/thirdly/... run my script? Any help with relevant advices is strongly appreciated in advance.


UPD № 1. Having comments above as well as one answer I did an update of my source code which is now:

function FormatCheckInAndCheckOutDates() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 2; // Строка с первым бронированием для начала обработки
var numRows = sheet.getLastRow()-1; // Количество строк с бронированиями для обработки
var dataRange = sheet.getRange(startRow, 1, numRows, 3); // Охват столбцов для обработки ячеек, в данном случае с 1-го по 3-й.
var data = dataRange.getValues(); // Получить значение ячеек в каждой ячейке каждой строке в рамках столбцов, охваченных верхним кодом.
for (var i = 0; i < data.length; ++i) {
var row = data[i];

/* var checkInDate = row[0].toString().valueOf();
var checkOutDate = row[1].toString().valueOf(); */

var checkInDate = new Date(row[0].replace(/\./g, '/'));
var checkOutDate = new Date(row[1].replace(/\./g, '/'));

var replaceDotsInCheckIn = (checkInDate.getMonth() + 1).toString() + '/' + checkInDate.getDate().toString() + '/' + checkInDate.getFullYear().toString();
var replaceDotsInCheckOut = (checkOutDate.getMonth() + 1).toString() + '/' + checkOutDate.getDate().toString() + '/' + checkOutDate.getFullYear().toString();
sheet.getRange(startRow + i, 1).setValue(replaceDotsInCheckIn);
sheet.getRange(startRow + i, 2).setValue(replaceDotsInCheckOut);
SpreadsheetApp.flush(); // Здесь завершается обновление ячеек 17-го столбца, в котором проставляется статус отправки уведомлений.
}
Logger.log("Произведена замена точек на слэши в датах заезда и выезда всех существующих бронирований.");
}


But I still get following error:


TypeError: Cannot find function replace in object Sun May 01 2016
00:00:00 GMT+0300 (MSK). (line 16, file ...




UPD № 2. I put below the source come I've finally come up with:

function FormatCheckInAndCheckOutDates() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetName = "2016"; // Здесь указывается название листа таблицы, в котором требуется производить форматирование дат заезда и выезда.
var sheet = ss.getSheetByName(sheetName);
var startRow = 2; // Строка с первым бронированием для начала обработки.
var numRows = sheet.getLastRow()-1; // Количество строк с бронированиями для обработки.
var dataRange = sheet.getRange(startRow, 1, numRows, 3); // Охват столбцов для обработки ячеек, в данном случае с 1-го по 3-й.
var data = dataRange.getValues(); // Получить значение ячеек в каждой ячейке каждой строке в рамках столбцов, охваченных верхним кодом.
for (var i = 0; i < data.length; ++i) {
var row = data[i];
var checkInDate = row[0];
var checkOutDate = row[1];
if (typeof checkInDate == "object") { // Здесь обрабатываем даты заезда.
var formattedCheckInDate = Utilities.formatDate(checkInDate, "GMT+0300", "dd/MM/yyyy");
} else {
var formattedCheckInDate = checkInDate.toString().valueOf().replace(/\./g, "/");
Logger.log("В дате заезда в строке № " + [i+2] + " разделяющие точки были заменены на слэши.");
}
if (typeof checkOutDate == "object") { // Здесь обрабатываем даты выезда.
var formattedCheckOutDate = Utilities.formatDate(checkOutDate, "GMT+0300", "dd/MM/yyyy");
} else {
var formattedCheckOutDate = checkOutDate.toString().valueOf().replace(/\./g, "/");
Logger.log("В дате выезда в строке № " + [i+2] + " разделяющие точки были заменены на слэши.");
}
sheet.getRange(startRow + i, 1).setValue(formattedCheckInDate); // Обновляем столбец значениями обработанных дат заезда.
sheet.getRange(startRow + i, 2).setValue(formattedCheckOutDate); // Обновляем столбец значениями обработанных дат выезда.
SpreadsheetApp.flush();
}
}


Thanks you for all your contributions in solving this issue!

Answer

Your code to replace the dots with slashes is working perfectly, the problem is that Google docs is doing some simple logic here to convert types automatically.

Google docs is converting things that look like dates to dates automatically. Then, when you try to retrieve the value, you do this:

var checkInDate = row[0].toString().valueOf();
var checkOutDate = row[1].toString().valueOf();

At this point, the Date() object is being converted into the format you dislike.

What you need is to do the toString manually before getting the result. You can do that with something like this.

var checkInDate = (row[0].getMonth() + 1).toString() + '/' + row[0].getDate().toString() + '/' + row[0].getFullYear().toString()

Here's an example of the different ways this data can look.

var date = '12.21.2012';
console.log(date);
var cleanDate = new Date(date.replace(/\./g, '/'));
console.log(cleanDate);
console.log((cleanDate.getMonth() + 1).toString() + '/' + cleanDate.getDate().toString() + '/' + cleanDate.getFullYear().toString());

For more information on javascript dates, check out https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date


Per the update:

You're trying to run replace on an object that is a Date, rather than a string. The data from the document is coming back as a Date already.

Basically, in your particular scenario, the dot replacement isn't doing anything:

This is why I wrote it the way I did above. row[0] is already a Date object. You need to store it in the variable as a string of the format you want.

Try replacing this:

var checkInDate = new Date(row[0].replace(/\./g, '/'));
var checkOutDate = new Date(row[1].replace(/\./g, '/'));

var replaceDotsInCheckIn = (checkInDate.getMonth() + 1).toString() + '/' + checkInDate.getDate().toString() + '/' + checkInDate.getFullYear().toString();
var replaceDotsInCheckOut = (checkOutDate.getMonth() + 1).toString() + '/' + checkOutDate.getDate().toString() + '/' + checkOutDate.getFullYear().toString();
sheet.getRange(startRow + i, 1).setValue(replaceDotsInCheckIn);
sheet.getRange(startRow + i, 2).setValue(replaceDotsInCheckOut);

with this:

var checkInDate = (row[0].getMonth() + 1).toString() + '/' + row[0].getDate().toString() + '/' + row[0].getFullYear().toString();
var checkOutDate = (row[1].getMonth() + 1).toString() + '/' + row[1].getDate().toString() + '/' + row[1].getFullYear().toString();
sheet.getRange(startRow + i, 1).setValue(checkInDate);
sheet.getRange(startRow + i, 2).setValue(checkOutDate);

You say "secondly and thirdly run my script" which implies that your data may be changing formats because of this script. It's leading me to the suspicion that your data may start as 12.21.2012 but then your script was fixing it to 12/21/2012 but then, when you went to read it, your script was finding 2012-12-21T05:00:00.000Z or some other silliness. So you're looking for an all-in-one solution to multiple problems here. This is easy enough too, just use type checking.

var checkInDate = '';
if (typeof row[0] === 'object') {
    checkInDate = (row[0].getMonth() + 1).toString() + '/' + row[0].getDate().toString() + '/' + row[0].getFullYear().toString();
}
else {
    checkInDate = row[0].toString().replace(".", "/");
}
Comments