I keep track of trading data in a private spreadsheet and, once a trade is closed, I push a button to run some AppsScript to copy the data across to a publicly visible spreadsheet. It all works well except the following point. I put the dates and times a trade is opened in two different columns and the same with the dates and times trades are closed. When I copy the dates across to the target spreadsheet, instead of "10 Jul 2022" it is showing as "10/07/2022 02:00:00". When I sort the spreadsheet on these date and time columns, this can often produce unwated results. What I want is for the date column to just display as "10 Jul 2022" and for that data to recognized as a date for sorting purposes. Is that possible?
function updatePublicSheet(){
var coinBought;
var dateBought;
var timeBought;
var priceBought;
var dateSold;
var timeSold;
var priceSold;
var sLrIndex;
var tLrIndex;
var sss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1cBGpD0nUqCGtQqq78svw4fxA4ZgwBqV2yzIbgY1tY2o/edit#gid=1107419239");
var sSheet = sss.getSheetByName('SS Open Trades');
var sRng = sSheet.getRange("H3:J").getValues();
// Find last entry in columns H, I or J in the Open Trades sheet
for (var i = sRng.length-1;i>=0;i--){
var sLrIndex = i;
if (!sRng[i].every(function(c){return c == "";})){
break;
}
}
Logger.log("Last source row is %s", sLrIndex + 3);
// Check that the closed information has been added to all columns
// index 8 = 8th column = H = dateSold
// index 9 = 9th column = I = timeSold
// index 10 = 10th column = J = priceSold
if ((sSheet.getRange((sLrIndex + 3),8).getValue().length) == 0 ||
(sSheet.getRange((sLrIndex + 3),9).getValue().length) == 0 ||
(sSheet.getRange((sLrIndex + 3),10).getValue().length) == 0){
SpreadsheetApp.getUi().alert("You cannot process partially completed entries");
return;
}
// If there is a complete closed entry, copy the values to the variables
if (sLrIndex > 0)
{
coinBought = sSheet.getRange((sLrIndex + 3),2).getValue();
dateBought = toUtcString(sSheet.getRange((sLrIndex + 3),4).getValue());
timeBought = sSheet.getRange((sLrIndex + 3),5).getValue();
priceBought = sSheet.getRange((sLrIndex + 3),6).getValue();
dateSold = toUtcString(sSheet.getRange((sLrIndex + 3),8).getValue());
timeSold = sSheet.getRange((sLrIndex + 3),9).getValue();
priceSold = sSheet.getRange((sLrIndex + 3),10).getValue();
//Logger.log(priceSold);
}
else
{
SpreadsheetApp.getUi().alert("There are no completed entries to process");
return;
}
var tss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1umkTCr95FZUrZzv0e_ZDD9QZYiiYuH1fJohPYQzNE9Q/edit#gid=1644116137");
var tSheet = tss.getSheetByName('Trade Tracker');
var tRng = tSheet.getRange("B3:J").getValues();
// Create a new row at Row 5 on the Trades Tracker Sheet
tSheet.insertRows(5, 1);//shift all rows down by one from row 5
// Copy values from row 4 to row 5, including the formulae
var tRange = tSheet.getRange(4, 1, 1, 26);
tRange.copyTo(tSheet.getRange(5, 1, 1, 26), {contentsOnly:false});
// Populate row 4, first 7 table columns, from the variables
//Logger.log(coinBought);
tSheet.getRange(4,2).setValue(coinBought);
tSheet.getRange(4,3).setValue(dateBought);
tSheet.getRange(4,4).setValue(timeBought);
tSheet.getRange(4,5).setValue(priceBought);
tSheet.getRange(4,6).setValue(dateSold);
tSheet.getRange(4,7).setValue(timeSold);
tSheet.getRange(4,8).setValue(priceSold);
// Format the cells with dates and times
tSheet.getRange(4,3).setNumberFormat("dd MMM yyyy"); // Short Date
tSheet.getRange(4,4).setNumberFormat("HH:mm"); // Short Time
tSheet.getRange(4,6).setNumberFormat("dd MMM yyyy"); // Short Date
tSheet.getRange(4,7).setNumberFormat("HH:mm"); // Short Time
// Sort the sheet by date/time closed
// Find last entry in Trade Tracker sheet
for (var i = tRng.length-1;i>=0;i--){
var tLrIndex = i;
if (!tRng[i].every(function(c){return c == "";})){
break;
}
}
Logger.log("Last target row is %s", tLrIndex + 4);
var tRange = tSheet.getRange(4, 2, tLrIndex + 4, 8)
tRange.sort([{column: 6, ascending: false}, {column: 7, ascending: false}]);
// On the source sheet, delete the row just copied and add another blank row at the bottom of the table
sSheet.deleteRows(sLrIndex + 3, 1);
var rowLast = sSheet.getLastRow();
sSheet.insertRowAfter(rowLast - 1);
// Copy values from the new last row to the new previous to last row, including the formulae
var sRange = sSheet.getRange(rowLast + 1, 1, 1, 10);
sRange.copyTo(sSheet.getRange(rowLast - 1, 1, 1, 10), {contentsOnly:false});
SpreadsheetApp.getUi().alert("One completed entry processed");
}