我对脚本编程还很陌生,所以如果有人知道如何做到这一点,那将是很大的帮助!
I'm very new to scripting so if anyone knows how to do this it'd be great help!
我有一个用户在工作日上午9点登录,并且必须在上午9.30之前执行某些操作.为了提醒并坚持该操作已完成,如果某个单元格不为空,我想发出一条警告说要执行此操作.理想情况下,它将在9、9.10、9.20和9.30处进行检查,并每次弹出直到完成为止.
I have a user that logs in on business days at ~9 am, and has to do something before 9.30 am. To remind and insist that it's done I want to have an alert that says to do it, if a certain cell is not empty. Ideally it would check at 9, 9.10, 9.20 and 9.30, and pop up each time until it's done.
我尝试每10分钟设置一个触发器,但我读到您不能在UI中使用触发器.
I tried to set up a trigger every 10 minutes, but I read that you can't use triggers with UI.
function startCustomTrigger() { ScriptApp.newTrigger('alerta').timeBased().everyMinutes(10).create(); }所以我想到了当 new date().getMinutes()为0/10/20/30时将其设为onEdit脚本,并且该脚本可以正常工作,但是当您继续操作时,它会不断弹出在整个工作表上工作一分钟.添加 new date().getSeconds()不起作用,因为它的时间窗口太短,容易错过.
So I thought of making it an onEdit script when new date().getMinutes() is 0/10/20/30, and it worked, but then it keeps popping up when you continue working on the sheet for the whole minute. Adding new date().getSeconds() doesn't work because it's too short of a time window and easy to miss.
这是我当前的代码:
function alerta() { var ss = SpreadsheetApp.getActiveSpreadsheet() var s = ss.getActiveSheet(); var motosS = ss.getSheetByName("Motos - mañana"); var motosRange = motosS.getRange(3, 1) var ui = SpreadsheetApp.getUi(); var date = new Date(); var day = date.getDay(); var hrs = date.getHours(); var min = date.getMinutes() var sec = date.getSeconds() Logger.log(date) Logger.log(day) Logger.log(hrs) Logger.log(min) Logger.log(sec) if (motosRange == "" && (day >= 1) && (day <= 5) && (hrs = 9)){ if (min == 0 || min == 14 || min == 20 || min == 30){ var response = ui.alert('IMPRIMIR MOTOS PARA LA MAÑANA', "Hay que anotar el sector de las ventas de ayer e imprimir las motos para la mañana.", ui.ButtonSet.OK_CANCEL); } } } 推荐答案使用脚本属性,用于存储函数调用之间的警报状态
说明
Use Script properties to store the alert status between function calls
Explanation
- 脚本属性允许您缓存可由后续脚本运行访问的变量
- 如果已显示警报,则创建一个设置为 true 的属性
- 每隔一分钟(或您希望的任何其他时间间隔)将属性设置回 false
- 后者可以通过时间驱动的触发器来完成,或者更容易-通过 Utilities.sleep().
- 想法是您在 if 语句中检查布尔值的状态,并且在变量为true时不再显示警报
- Script properties allow you to cache variables which are accessible by the subsequent script run
- Create a property that is set to true if the alert has been shown already
- Set the property back to false once one minute (or any other timespan you desire) has passed
- The latter can be done with a time-driven trigger, or - easier - with Utilities.sleep().
- The idea is that you check within the if statement the status of your boolean and do not show the alert again while the variable is true
示例:
function alerta() { //initial setup if(!PropertiesService.getScriptProperties().getProperty("alreadyNotified")){ PropertiesService.getScriptProperties().setProperty("alreadyNotified", false) } var ss = SpreadsheetApp.getActiveSpreadsheet() var s = ss.getActiveSheet(); var motosS = ss.getSheetByName("Motos - mañana"); var motosRange = motosS.getRange(3, 1).getValue() var ui = SpreadsheetApp.getUi(); var date = new Date(); var day = date.getDay(); var hrs = date.getHours(); var min = date.getMinutes() var sec = date.getSeconds() Logger.log(date) Logger.log(day) Logger.log(hrs) Logger.log(min) Logger.log(sec) var alertStatus = PropertiesService.getScriptProperties().getProperty("alreadyNotified"); //put "false" in quotes because Scriptproperties store the value as a string if (alertStatus == "false" && motosRange == "" && (day >= 1) && (day <= 5) && (hrs = 9)){ if (min == 0 || min == 14 || min == 20 || min == 30){ PropertiesService.getScriptProperties().setProperty("alreadyNotified", true); var response = ui.alert('IMPRIMIR MOTOS PARA LA MAÑANA', "Hay que anotar el sector de las ventas de ayer e imprimir las motos para la mañana.", ui.ButtonSet.OK_CANCEL); Utilities.sleep(60*1000); PropertiesService.getScriptProperties().setProperty("alreadyNotified", false); } } }更多推荐
在特定时间在Google脚本上触发警报
发布评论