检查列A是否等于今天的日期,然后获取列B的值。

huangapple go评论60阅读模式
英文:

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

问题

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.stringify({
       "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);
}

(Note: I have removed HTML encoding and converted the code to regular text as per your request.)

英文:

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);
}

答案1

得分: 1

这里是如何获取数值的方式:

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);

请注意,上述代码部分不需要翻译。

英文:

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);

huangapple
  • 本文由 发表于 2023年2月16日 09:21:36
  • 转载请务必保留本文链接:https://go.coder-hub.com/75466949.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定