Change Google Docs Date

Question

I have three scripts that are in a Google Docs spreadsheet. In this table, in column H (or column 8 ), if I type x , the script will change it to that date of the day.

After a few days, each date in column H changed from a date to a number . The numbers look like this: 40492, 40494, 40511 .

I am not sure what causes this. Maybe this is something wrong in my script. I inserted them below. Any ideas?

Here is the first one:

 function onEdit(e) { var colorA = "yellow"; var colorB = "#dddddd"; var colorC = "#dddddd"; var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Purchase Orders"); var range = e.source.getActiveRange(); var sheetName = SpreadsheetApp.getActiveSheet().getName(); if (sheetName == "Purchase Orders") { // 3 is column C if (range.getColumn() == 7 && range.getValue() != "") { var r = range.getRow() + 1; sheet.getRange("A" + r + ":G" + r).setBackgroundColor(colorC); } } var col = e.source.getActiveRange().getColumn(); if(col == 8 || col == 7) { var rows = sheet.getMaxRows(); //column C var rangeC = sheet.getRange("H1:H"+rows); var valuesC = rangeC.getValues(); //column H range var rangeH = sheet.getRange("G1:G"+rows); var colorH = rangeH.getBackgroundColors(); var valuesH = rangeH.getValues(); //iterate over each row in column C and H //then change color for (var row = 0; row < valuesC.length; row++) { //check for columnC and column H var hRow = colorH[row]; if (valuesC[row][0] != "" && valuesH[row][0] == "") { hRow[0] = colorA; } else if (valuesH[row][0] != "") { hRow[0] = colorB; } } sheet.getRange("G1:G" + rows).setBackgroundColors(colorH); } } 

Here's the second one:

 function onEdit(e) { var colorA = "yellow"; var colorB = "#dddddd"; var colorC = "#dddddd"; var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Purchase Orders"); var range = e.source.getActiveRange(); var sheetName = SpreadsheetApp.getActiveSheet().getName(); if (sheetName == "Purchase Orders") { // 3 is column C if (range.getColumn() == 3 && range.getValue() != "") { sheet.insertRowAfter(range.getRow()); var r = range.getRow() + 1; sheet.getRange("A" + r + ":H" + r).setBackgroundColor(colorC); } } var col = e.source.getActiveRange().getColumn(); if(col == 3 || col == 8) { var rows = sheet.getMaxRows(); //column C var rangeC = sheet.getRange("C1:C"+rows); var valuesC = rangeC.getValues(); //column H range var rangeH = sheet.getRange("H1:H"+rows); var colorH = rangeH.getBackgroundColors(); var valuesH = rangeH.getValues(); //iterate over each row in column C and H //then change color for (var row = 0; row < valuesC.length; row++) { //check for columnC and column H var hRow = colorH[row]; if (valuesC[row][0] != "" && valuesH[row][0] == "") { hRow[0] = colorA; } else if (valuesH[row][0] != "") { hRow[0] = colorB; } } sheet.getRange("H1:H" + rows).setBackgroundColors(colorH); } } 

Here's the third one:

 function onEdit(e) { var ss = e.source.getActiveSheet(); var r = e.source.getActiveRange(); //1 is A, 2 is B, ... 8 is H if (r.getColumn() == 8 && r.getValue() == "x") { r.setNumberFormat("MM/dd/yyyy") r.setValue(Utilities.formatDate(new Date(), "MST", "yyyy-MM-dd")); } } 

Answer 1

The first possibility is that another macro overwrites the column - it is worth excluding it. However, the most likely problem is that you are not setting the column format as a date format. If this is a common format, then the date is displayed as the number you see.

The reason is that it is how it is stored, and you do not tell the cells to interpret this number as a date, so it is not.

My excel script is not really suitable for providing code, but if you make sure you randomly format colum to display dates, then you should be fine. The reason why it looks normal at first is because when you add a date, it sees it as a date, but if it is not saved in a specific format, it opens again with the default value.

NTN

Answer 2

Following what Schrödinger said, try entering the number format in the date format

Select the range of cells you want to change:

 Ex. H24:H28 

From the drop-down menu, select Format-> Number, and then the date format you want to use. It may or may not work.

If this does not matter, you will need to explicitly specify the integer value in the script code in the date format (this conversion may have occurred implicitly before).

If you do not know how to create types in JavaScript, you should look or open a new question on this site.

Answer 3

In my locale (uk), on the PC keyboard, typing Ctrl +; (Ctrl with semicolon) today the date will be inserted into the selected cell (in the Col H field). You can try this and not the third script.

If you select column H and select Format> Number> Normal from the menu, the date will be sent as a number (as indicated in Kote Shredders). Conversely, selecting Format> Number> Date will return all numbers to the date format that matches the current spreadsheet settings.