Check if column A is equal today's date and get the value of column B

Question

I have a Google Sheet with a table like this:

Data	On Duty	Support
15/02/2023	Name1	Name4
16/02/2023	Name2	Name5
17/02/2023	Name3	Name6

I need to check the column A if the date is equal with today's date and get the value of column B and C and send the values to a slack channel.

I tried this but isn't working:

function sendSlackMessage() {
  const onduty = QUERY("SELECT B WHERE todate(A)=date'" & text(today(), "dd/MM/yyyy") &"'");
  const support = QUERY("SELECT C WHERE todate(A)=date'" & text(today(), "dd/MM/yyyy") &"'");
  const url = "https://hooks.slack.com.services/xxxxxx/xxxxxxx/xxxxxxxxxxxxxxxxxxxxxx";
  const params = {
     method: "post",
     contentType: "application/json",
     payload: JSON.stringfy({
       "text" : "Analyst on duty today: " + onduty + "\n" + "Support analyst: " + support
     })
  }
  const sendMsg = UrlFetchApp.fetch(url, params);
  var respCode = sendMsg.getResponseCode();
  Logger.log(sendMsg);
  Logger.log(respCode);
}

Answer 1

Here's how you can get the values:

  const dt = new Date();
  const dtv = new Date(dt.getFullYear(),dt.getMonth(),dt.getDate()).valueOf();
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(2,1,sh.getLastRow() - 1, 2).getValues();
  let vo = vs.map(r => {
    let d = new Date(r[0]);
    let dv = new Date(d.getFullYear(),d.getMonth(),d.getDate()).valueOf();
    if(dtv == dv) {
      return r[1];
    } else {return null;}
  }).filter(e => e);