I have an extremely simple loop but I'm completely stuck on how I would check for a date & time variable.
I have a very large spreadsheet where one of the columns includes cells with the following format: "M/DD/YYYY, H:MM PM/AM"
So, for example, it might look like this: “5/25/2022, 6:45 PM”
I am running a loop through the sheet and when it encounters this cell, I’m currently hiding the entire row if the cell has the value “5/25/2022, 6:45 PM.” However, what I really want to do, is only to hide the row if the value is between 12pm and 8pm. I’m completely lost as to how to do this, does anyone have any suggestions?
My current script:
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getSheetByName("Sheet2");
let lastRow = sheet.getLastRow();
let flaggedDate = sheet.getRange(1, 11, lastRow).getValues();
for (i=0; i <= lastRow; i++) {
if (flaggedDate[i] == "5/25/2022, 4:30 PM") {
sheet.hideRows(i+1);
}
}
}
function ltesto() {
let ds = "5/25/2022, 4:30 PM";
let dtv = new Date(ds).valueOf();
let ss = SpreadsheetApp.getActiveSpreadsheet();
let sheet = ss.getSheetByName("Sheet2");
let lastRow = sheet.getLastRow();
let flaggedDate = sheet.getRange(1, 11, lastRow).getValues();
for (i = 0; i <= lastRow; i++) {
Logger.log(Utilities.formatDate(new Date(flaggedDate[i]),Session.getScriptTimeZone(),"MMM/dd/yyyy HH:mm"));
if (new Date(flaggedDate[i]).valueOf() == dtv ) {
sheet.hideRows(i + 1);
}
}
}
If this doesn't work then you may have to split the date strings and reconstruct the date with the date constructor. But I already tried it on your provided string and it seems to recognized the date properly. And when I say date, I mean datetime.
Just a simple function for checking if you datestring is recognized by the date constructor
function test() {
let ds = "5/25/2022, 6:45 PM";
let dt = new Date(ds);
let dts = Utilities.formatDate(dt, Session.getScriptTimeZone(), "MMM/dd/yyyy HH:mm");
Logger.log(dts);
}
If this is a purely javascript project, here is the code but if you have a backend like PHP involved, there's an option and in my opinion, it's better to do this at the backend.
// JAVASCRIPT CODE
let str_date = "5/25/2022, 8:30 PM";
let [time, modifier] = str_date.split(', ')[1].split(' ');
let [hour, minute] = time.split(':');
modifier = modifier.toLocaleLowerCase();
if(hour == 12 && modifier == 'am') hour = '00';
else if(hour == 12 && modifier == 'pm') hour = 12;
else hour = modifier == 'am' ? '0'+hour : parseInt(hour) + 12;
console.log(hour);
if(parseInt(hour) >= 12 && parseInt(hour) <= 20) {
console.log("There you go!");
}
HERE IS THE PHP ALSO
<?php
$str_date = '5/25/2022, 4:30 AM';
$date_array = explode(',', $str_date);
// convert time to 24 hour clock
$_24hour = date("H:i", strtotime(trim($date_array[1])));
$_24hour_array = explode(':', $_24hour);
if($_24hour_array[0] >= 12 && $_24hour_array[0] <= 20) {
echo "It's that time!";
}