-1

So I have a sheet where in column A I have multiple rows with dates.

I am trying this to get the dates:

cells = sheet.getRange("A5:A").getDisplayValues();

It displays something like this:

[ 
  [ '7/30/2020 1:00:00' ],
  [ '7/31/2020 19:00:00' ],
  [ '8/1/2020 1:00:00' ],
  [ '8/2/2020 19:00:00' ],
  [ '7/30/2021 1:00:00' ],
  [ '7/31/2021 19:00:00' ],
  [ '8/1/2021 1:00:00' ],
  [ '8/2/2021 19:00:00' ] 
]

I need a way to get an array with the row numbers where the date is August 2020 for example. In this case it would be rows 3 and 4. So like this:

[ 3, 4 ]

Please note the dates are using en_US date format. It would have to be a method that works even if locale is set for for ex. en_GB where the array of arrays with dates looks like this:

[ 
  [ '30/07/2020 01:00:00' ],
  [ '31/07/2020 19:00:00' ],
  [ '01/08/2020 01:00:00' ],
  [ '02/08/2020 19:00:00' ],
  [ '30/07/2021 01:00:00' ],
  [ '31/07/2021 19:00:00' ],
  [ '01/08/2021 01:00:00' ],
  [ '02/08/2021 19:00:00' ] 
]

Is such a thing possible?

Best result so far I got was with this:

cells = sheet.getRange("A5:A").createTextFinder("MM/YYYY").findAll().map(x => x.getRow());

But maybe createTextFinder() is not the best way to do this? Also the above only works when date format is dd/MM/YYYY which for users from en_US won't work, and also for other areas as well, which separate the MM from the YYYY with d like in case of en_US is M/d/YYYY. Other locales also have the date format like YYYY/MM/d or even YYYY/dd/MM and so on.

In order to fix this I created a long list of switch statements with every possible locale which got big. And in cases like en_US, createTextFinder() won't work for combination of month and year separated by day: M/d/YYYY.

So maybe this question is 2 questions into one. First I asked how to filter array of arrays with dates, but maybe there is a better way of getting the row numbers with the desired dates without using getDisplayValues() which will output not an array of arrays but something else?

Then the second part asks how to search the supposed array, array of arrays or array of object etc, for the row numbers which have a date which combines a specific month and year, in my example August 2020, regardless of the locale and date format, either its en_US or en_GB or any other.

Here is my file: https://docs.google.com/spreadsheets/d/1ExXtmQ8nyuV1o_UtabVJ-TifIbORItFMWjtN6ZlruWc/edit?usp=sharing

Verminous
  • 490
  • 3
  • 14
  • Take a look at this: [Get Locale Short Date Format using javascript](https://stackoverflow.com/a/9893752/12567365). It lets you look up a date format based on a language code. I have no idea how correct it is - but you can use whatever codes and date formats you need. However, if you already have a date object (not a date in a string) then none of this should be necessary - but I am not sure if that is your situation or not. What a user sees in a cell is just the display representation of the underlying data (if it really is a date value, not a string). – andrewJames Sep 13 '21 at 01:13

1 Answers1

1

You could use Array.reduce with cells to get an resulting array of the indexes where the string dates meet your criteria.

const wantedYear = 2020
const wantedMonth = 7 // August


const cells = [
  ['7/30/2020 1:00:00'],
  ['7/31/2020 19:00:00'],
  ['8/1/2020 1:00:00'],
  ['8/2/2020 19:00:00'],
  ['7/30/2021 1:00:00'],
  ['7/31/2021 19:00:00'],
  ['8/1/2021 1:00:00'],
  ['8/2/2021 19:00:00']
]

const result = cells.reduce((acc, curr, index) => {
  const date = new Date(curr[0])
  const year = date.getFullYear()
  const month = date.getMonth()
  if (year === wantedYear && month === wantedMonth) {
    return [...acc, index]
  }
  return acc
}, [])

console.log(result)

Note it is not recommended (or even possible?) to attempt to parse a string to a date where the format is unknown ahead of time. What would you assume the format to be for "10/7/2019"?

ksav
  • 20,015
  • 6
  • 46
  • 66
  • Its a sheet that will be used by people from different parts of the world so maybe 50% might be from `en_US` but other 50% will be from other areas. Maybe 30% `en_GB` other 20% from multiple countries from EU. Tiny percentage from Asia. I wanted a line of code that adapts to everything. One question though any reason here why August is `7` and not `8`? Because in an array January `=0`? – Verminous Sep 12 '21 at 22:09
  • It's fine If I do `const month = date.getMonth()+1` then I can use `8`for August. – Verminous Sep 12 '21 at 22:16
  • Yep although this works for `en_US` it does not for `en_GB`. – Verminous Sep 12 '21 at 22:21
  • You need to store your dates in a universal format. What would you say is the month specified in this date `10/7/2019`? – ksav Sep 12 '21 at 23:21
  • I just realized I can use array intersection method. I can intersect arrays with month rows with year rows and get the result. Like: `var cellsM = sheet.getRange("A5:A").createTextFinder(getMonth).findAll().map(x => x.getRow()); var cellsY = sheet.getRange("A5:A").createTextFinder(getYear).findAll().map(x => x.getRow()); var intersectMY = cellsM.filter(e => ~cellsY.indexOf(e)); `. I just wish I didn't have to use `createTextFinder()` because it doesn't feel right. – Verminous Sep 12 '21 at 23:23
  • If its `en_US` the month is `10`. If not its `7`. – Verminous Sep 12 '21 at 23:28
  • Yes. If you don't have control over the format of your string dates in your source data, you are going to have trouble. – ksav Sep 12 '21 at 23:29
  • What would you say is the month specified in this date 10/7/2019? – ksav Sep 12 '21 at 23:30
  • I'd say If its en_US the month is 10. If not its 7 – Verminous Sep 12 '21 at 23:37
  • Without any other information, which format would you say that date is in? – ksav Sep 12 '21 at 23:53
  • Well in my country its `dd/MM/YYYY` so out of habit I'd say it's the month of July, so en_GB except en_GB displays 2 characters for month so maybe it's Finland? `fi_FI` it's `'d"."m"."yyyy"` – Verminous Sep 12 '21 at 23:56
  • @Verminous Store date as date type in spreadsheet(dates will be aligned right of the cell by default, while strings are aligned to the left). Then use `.getValues()` instead of `.getDisplayValues()`. That'll return JavaScript date objects instead of strings. – TheMaster Sep 13 '21 at 08:42