Currently, I am working on a project and trying to determine if a specific store appears within a certain date range in a set of cell ranges. Once identified, I need to count how many times that particular store shows up within the range. Although my formula is mostly functional, I am encountering an issue when comparing the store to the unique values in the range - they do not match even though the input for both values is identical.
Array.prototype.getUnique = function () {
var u = {}, a = [];
for (var i = 0, l = this.length; i < l; ++i) {
if (u.hasOwnProperty(this[i])) {
continue;
}
a.push(this[i]);
u[this[i]] = 1;
}
return a;
}
function countOnDate(datestart,dateend,daterange,sheet,valuesrange){
var datestart = Utilities.formatDate(new Date(datestart), "GMT+1", "M/d/yyy")
var dateend = Utilities.formatDate(new Date(dateend), "GMT+1", "M/d/yyy")
var range = sheet.getRange(daterange).getValues();
var countrange = sheet.getRange(valuesrange).getValues()
var count = [];
var countrangeunique = countrange.getUnique()
//Logger.log("countrange:"+countrange)
//Logger.log("unique"+countrangeunique)
for (var a =0;a < countrangeunique.length;a++){
var countnumber = 0;
for (var b = 0; b < range.length;b++){
if (range[b] !="" || range[b] !=[]){
var rangeformated = Utilities.formatDate(new Date(range[b]),"GMT+1","M/d/yyy")
Logger.log((datestart <= rangeformated) +":"+(rangeformated <= dateend))
if (datestart <= rangeformated && rangeformated <= dateend){
Logger.log("activestore:"+countrangeunique[a]+" storescan:"+countrange[b]+"match:"+(countrange[b] == countrangeunique[a]))
if (countrange[b] == countrangeunique[a]) {
countnumber++
count[a][0] = countrangeunique[a]
count[a][1] = countnumber
Logger.log("counternumber"+countnumber+" store: "+countrangeunique[a])
}
}
}
}
}
Initially, the 'countUnique' function is invoked to identify the unique value in the store range specified by the 'valuesrange' variable. This range contains IDs for the stores where the employee has worked, such as 2512 or 1533. The 'countOnDate' function then searches for unique store IDs and verifies whether each ID falls within the current month based on the provided start ('datestart') and end ('dateend') dates along with the 'daterange'. Dates are formatted as 'm/d/yyy' representing when the employee worked. Any dates outside the month or empty should be disregarded. The system proceeds to compare the unique number against the range of store numbers occurring within the month and aims to increment their count. However, the comparison 'countrange[b] == countrangeunique[a]' does not yield correct matches despite same values being present. How can this discrepancy be rectified? Currently, the logs indicate: