英文:
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);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论