英文:
In Google Sheets, how can I set up an alert if a cell has NOT been updated?
问题
我希望在Google Sheets中，如果一个单元格中的日期已经过去，另一个单元格仍然为空，我能够收到警报。换句话说，如果到了特定日期时没有人填写一个单元格，我希望能够收到警报。我认为这必须与相关表格分开，因为我正在寻找表格中的活动缺失。我希望我表达清楚。
我可以根据单元格数据从电子表格中创建警报、日历条目等等。但只有在打开表格时才能实现。我需要的是一种能够告诉我没有人打开表格并输入数据的方式。类似于：“嘿，团队，今天是xx/xx/xxxx，没有人更新表格y。”
英文:
I want to get an alert from Google Sheets if, once a date in a cell has passed, another cell is still blank. In other words, if no one has filled out a cell by a certain date, I want to get an alert. I believe this would have to be separate from the sheet in question, since I'm looking for a lack of activity in the sheet. I hope I'm making sense.
I can create alerts, calendar entries, etc., from a spreadsheet based on cell data. But that only works if the sheet is open. I need something that will basically tell me that no one has opened the sheet and entered data. Along the lines of, "Hey, team, it's xx/xx/xxxx and nobody has updated sheet y."
答案1
得分: 0
以下是翻译好的内容：
这不清楚您是否想要获取工作表上次修改的时间，还是只需要检查特定单元格。如果是整个电子表格，您可以尝试以下方法。根据您的需要调整容忍度：
function lastupdate(){
tolerance_in_hours = 24
const current = new Date()
id = "put_your_id"
var last_update = DriveApp.getFileById(id).getLastUpdated()
var difference = (current - last_update)
var difference_in_hours = difference/1000/60/60
Logger.log(difference_in_hours)
if(difference_in_hours > tolerance_in_hours){
//放置您的代码在这里
}
}
如果您只需要检查特定单元格是否为空，可以尝试以下方法：
function checkcell(){
if(SpreadsheetApp.openById(put_your_id).getSheetByName(put_your_name).getRange(put_your_range) == "")
{ 插入您的代码 }
}
请注意，这些代码片段中的 "put_your_id"、"put_your_name" 和 "put_your_range" 部分应根据您的实际需求进行替换。
英文:
It's not clear if you want to get the last time the sheet was modified or only a certain cell. If it's the full spreadsheet, you can try with an approach like this. Adapt the tolerance as you need:
function lastupdate(){
tolerance_in_hours = 24
const current = new Date()
id = "put_your_id"
var last_update = DriveApp.getFileById(id).getLastUpdated()
var difference = (current - last_update)
var difference_in_hours = difference/1000/60/60
Logger.log(difference_in_hours)
if(difference_in_hours > tolerance_in_hours){
//place your code here
}
}
If you just need to check if certain cell is empty, you can try with:
function checkcell(){
if(SpreadsheetApp.openById(put_your_id).getSheetByName(put_your_name).getRange(put_your_range) == "")
{ insert your code}
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论