How can I only compare dd/mm against dd/mm/yyyy in Google sheets?

user_17587917 0
Viewed 39

Today I have a bit more of a complicated question:

I have a Google sheet that has the has real dates (dd/mm/yyyy) in one column.

While checking, I want to check it against the following:

date_format = datetime.today().strftime('%d/%m')

Which would only check dd/mm as I am not interested in the year, but can't change it either.

Is Python/gspread able to take care of this? Is a comparison like this even possible?

While reading out the column, you can't subscript the 'Cell' of course.

I tried to use some regex I found:

criteria_re = re.compile('(?<!\d)((((0?\d)|([12]\d)|(3[01]))/(((0?((1[02]?)|([3578]))))|(1[0-2])))|(((0?\d)|([12]\d))/(0?2))|(((0?\d)|([12]\d)|(30))/((0?[2469])|(11))))/((19)|(20))\d\d')
cell_list = worksheet.findall(criteria_re, in_column=1)

# This is returned: [<Cell R1C1 '21/08/2020'>, <Cell R2C1 '23/08/2020'>]

Which however only checks for real dates, but I only want to check if an entry matches the dd/mm of today by using the date_format above.

I also tried to remove the last 5 characters, but that converts the output into a str and I need it as an int to then check something like this:

cell_list = worksheet.findall(date_format, in_column=1) # All values that match the date, only want to look for dd/mm though

for match in cell_list:

    values_list = worksheet.row_values(match.row) # Gets the row where the cell was found

I other words: I still need to get the location of the cell as I can't just search for dd/mm if the format in the sheet is dd/mm/yyyy

gspread google-sheets-api google-sheets python regex
1 Answers

I believe your goal is as follows.

  • You want to retrieve the rows by searching dd/mm from the values of dd/mm/yyyy.
  • You want to achieve this using gspreads for python.

In this case, how about the following sample script? In this sample script, from your question, I used the cell values as the string values.

Sample script:

searchColumn = 1  # Please set the search column. In this case, it's the column "A".
search = "22/08" # Please set the search value.

criteria_re = re.compile("(?<!\d)((((0?\d)|([12]\d)|(3[01]))/(((0?((1[02]?)|([3578]))))|(1[0-2])))|(((0?\d)|([12]\d))/(0?2))|(((0?\d)|([12]\d)|(30))/((0?[2469])|(11))))/((19)|(20))\d\d") # This is from your script.
values = worksheet.get_all_values()
res = [r for r in values if criteria_re.search(r[searchColumn - 1]) and r[searchColumn - 1][:-5] == search]
print(res)
  • When this script is run, the values are searched from the column "A". When the searched values are found, the row values are retrieved.

Added:

When you want to retrieve the searched row number, how about the following sample script? In this case, the 1st-row number is 1.

Sample script:

searchColumn = 1  # Please set the search column. In this case, it's the column "A".
search = "22/08" # Please set the search value.

criteria_re = re.compile("(?<!\d)((((0?\d)|([12]\d)|(3[01]))/(((0?((1[02]?)|([3578]))))|(1[0-2])))|(((0?\d)|([12]\d))/(0?2))|(((0?\d)|([12]\d)|(30))/((0?[2469])|(11))))/((19)|(20))\d\d") # This is from your script.
values = worksheet.get_all_values()
res = [i + 1 for i, r in enumerate(values) if criteria_re.search(r[searchColumn - 1]) and r[searchColumn - 1][:-5] == search]
print(res)
user_71086530
Related