Google sheets - Search for a date in a range and return value underneath?-CodePudding

I'm trying to search for a specific date and use the whole sheet as a range, and return the value underneath (the checkbox), which is either True (if ticked) or False (if not ticked). The numbers on the sheet are actually dates, i've just formatted them to look this way.

So for example, let's say that today i want to search for the date 2022-03-30 (the marked number on the picture below) and retrieve the value underneath to see if the checkbox is ticked or not, how would I go about this?

Picture below:

Google sheet url that you guys can view/edit: https://docs.google.com/spreadsheets/d/1poFFukwrPZFLynt1SmDaNcELZlbPFfEibOWAwRNsafI/edit?usp=sharing

If anyone has any idea on how to make this work, maybe we can insert the formula inside "Sheet1"?

Thanks alot in advance, any tips are appreciated.

CodePudding user response：

I believe your goal is as follows.

In your showing sample Spreadsheet, you want to know whether the checkbox is checked or unchecked.
From let's say that today i want to search for the date 2022-03-30 (the marked number on the picture below) and retrieve the value underneath to see if the checkbox is ticked or not,, in this case, you want to check whether the checkbox of "T12" is checked.
You want to achieve this using Google Apps Script.

In this case, how about the following sample script?

Sample script:

Please copy and paste the following script to the script editor of Spreadsheet. And, please set the sheet name, and save the script.

function myFunction() {
  const sheetName = "Sheet1"; // Please set your sheet name.
  const oneMonth = { cols: 7, rows: 14 }; // Number of columns and rows of 1 month.
  const calendar = { cols: 4, rows: 3 }; // Order of months in your calendar.
  const spaceCol = 1; // I found an empty column between each month.
  const today = new Date(); // This is today date.

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const months = [...Array(calendar.rows)].flatMap((_, i) => [...Array(calendar.cols)].map((_, j) => sheet.getRange(i * oneMonth.rows   1, j * oneMonth.cols   1   j * spaceCol, oneMonth.rows, oneMonth.cols)));
  const res = months[today.getMonth()].createTextFinder(today.getDate()).findNext().offset(1, 0).isChecked();
  console.log(res) // You can see "true" or "false" in the log.
}

When this script is run, in the case of today (2022-07-05), the checkbox of "S20" is retrieved.
In order to search the day in a month, I used TextFinder.

Note:

This sample script is for your provided Spreadsheet. So, when you change your Spreadsheet, this script might not be able to be used. Please be careful about this.

Reference:

Class TextFinder