0
votes

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");

}