英文:
How to automatically set a value in drop-down list based on a value from another cell in row - apps script
问题
以下是代码部分的翻译:
function onEdit(event) //
{
var ColK = 11; // 列号为“K”
var now = new Date();
var changedRange = event.source.getActiveRange();
if (changedRange.getColumn() == ColK)
{
// 在列K中发生了编辑
var state = changedRange.getValue();
var Group = event.source.getActiveSheet().getRange(changedRange.getRow(), ColK - 5);
switch (state)
{
case "now":
// 从下拉列表中选择“TODAY”
Group.setValue("TODAY");
break;
}
}
}
请注意,上面的代码是JavaScript代码,用于在编辑表格时自动更新时间轴下拉菜单。如果您有任何其他需要翻译的内容,请提供相关文本。
英文:
In my task list each row includes a timeline dropdown menu (F) and a do date cell (K). I want the Timeline dropdown to automatically change based on the date in the do date column.
For example I would like the timeline dropdown to change to Today if the date in the do date column is today's date. I would also like the timeline dropdown to change to Tomorrow if the date in the do date column is tomorrow's date.
I've tried to create the code based on the responses in a few other different forums. However, I have not been able to successfully create the function.
<!-- begin snippet: js hide: false console: true babel: false -->
<!-- language: lang-html -->
function onEdit(event) //
{
var ColK = 11; // Column Number of "K"
var now = new Date();
var changedRange = event.source.getActiveRange();
if (changedRange.getColumn() == ColK)
{
// An edit has occurred in Column K
var state = changedRange.getValue();
var Group = event.source.getActiveSheet().getRange(changedRange.getRow(),ColK-5);
switch (state)
{
case "now”:
// Select TODAY from dropdown list
Group.setValue("TODAY");
break
}
}
}
<!-- end snippet -->
答案1
得分: 1
以下是代码部分的翻译:
尝试:
----
尝试类似以下的内容,您可以添加其他条件。
function onEdit(event){
var colK = 11; // “K”列的列号
var changedRange = event.source.getActiveRange();
if (changedRange.getColumn() == colK) {
// 在K列中发生了编辑
// 获取Do日期和当前日期,然后设置为相同时间。
var doDate = changedRange.getValue().setHours(12, 0, 0);
let today = new Date().setHours(12, 0, 0)
// 获取两个日期的差异
var dateDifference = Math.round((doDate - today) / 8.64e7)
// 根据差异设置下拉列表的值
var group = event.source.getActiveSheet().getRange(changedRange.getRow(), colK - 5);
if (dateDifference == 0) {
group.setValue("今天");
} else if (dateDifference == 1) {
group.setValue("明天");
}
}
}
希望对您有所帮助!
英文:
Try:
Try something like this, you may add your other conditions.
function onEdit(event){
var colK = 11; // Column Number of "K"
var changedRange = event.source.getActiveRange();
if (changedRange.getColumn() == colK) {
// An edit has occurred in Column K
//Get the Do date and current date then set as same time.
var doDate = changedRange.getValue().setHours(12, 0, 0);
let today = new Date().setHours(12, 0, 0)
//Get the difference of the 2 dates
var dateDifference = Math.round((doDate - today) / 8.64e7)
//Set value to dropdown depending on difference
var group = event.source.getActiveSheet().getRange(changedRange.getRow(), colK - 5);
if (dateDifference == 0) {
group.setValue("Today");
} else if (dateDifference == 1) {
group.setValue("Tomorrow");
}
}
}
Result:
Let me know if this helps!
答案2
得分: 0
以下是翻译好的代码部分:
function onEdit(e) {
const sh = e.range.getSheet();
if(sh.getName() == "Your Sheet Name" && e.range.columnStart == 11 && e.range.rowStart > 3 && e.value) {
// 这将检测到在K列且行号大于3的用户编辑,您可以自行决定要执行的操作
}
}
英文:
A start:
function onEdit(e) {
const sh = e.range.getSheet();
if(sh.getName() == "Your Sheet Name" && e.range.columnStart == 11 && e.range.rowStart > 3 && e.value) {
//this will detect a user edit in column K and row > 3 what you wish to do with it is upto you
}
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论