您的位置: 首页  >  技术问答  >  用于编辑单元格的脚本，从今天开始每周设置到期日期

用于编辑单元格的脚本，从今天开始每周设置到期日期

分类: 技术问答 • 2022-06-02 18:01:55
问题描述：

正如标题所述，当两个相邻单元格之一被编辑时，我要在今天之后的一周内将一个截止日期添加到该单元格中.第一个单元是贡献者，因此当有人被分配到任务时，将设置截止日期.第二个单元格是完成状态，因此当任务设置为已完成"时，它将把截止日期单元格更改为完成".

As the title says, I am adding a due date one week from today to a cell when one of two adjacent cells are edited. The first cell is the contributor, so when someone is assigned to the task the due date is set. The second cell is completion status, so when a task is set to "Completed" it will change the due date cell to say "done".

我不希望在当前日期更改时更改此设置.另外，如果稍后对conributor进行了编辑，则希望它保持不变.

I don't want this to change when the current date changes. Also want it to stay the same if the conributor is edited later.

我的第一次尝试是将脚本放在单个单元格上，并且在编辑任何参数时都会自动调用该脚本.当前未使用onEdit()触发器.

My first attempt had the script on individual cells and it would be automatically called when any of the parameters were edited. Not currently using the onEdit() trigger.

function setDate(contrib, progress) {
 //contrib: the current assigned contributor
 //progress: cell showing progress state (Not started, begun, completed)

  var range = SpreadsheetApp.getActiveRange();
  var d = range.getValue(); 

  if(progress == 'Completed') { return 'done'; } 
  if(d instanceof Date) { return; }
  if(contrib == '') { return; }
//if progress is completed, it should always return done
//if there is already a date there, it should not change it
//if contrib is blank, as long as there wasn't already a date, we don't want to add one

  today = new Date();
  due = new Date();
  due.setDate(today.getDate() + 7); //one week from today
  return due;
}

问题是，当您编辑贡献者或进度状态时，它将截止日期单元格更改为正在加载..."，因此我实际上无法检索原始内容.

The issue is, when you edit the contributor or progress status, it changes the due date cell to "Loading..." so I can't actually retrieve the original content.

我的第二个尝试是创建一个可以贯穿整列的函数.

My second attempt was to make a function that would run through the whole column.

  function setDueDate(contribR, dueR, progressR) {
    today = new Date();
    due = new Date();
    due.setDate(today.getDate() + 7); //one week from today
    dates = ["Due Date"];
    //the first value in array has to be "Due Date" because the script is placed in the title cell for that column, the cell above the due date column I want to edit

    for(var i = 0; i < dueR.length; i++) {
        if(progressR[i] == 'Completed') {
            dates.push('done');
        }
        else if(dueR[i] instanceof Date) {
            dates.push(dueR[i]);
        }
        else if(contribR[i] != '') {
            dates.push(dueR[i]);
        }
      else {
        dates.push('');
      }
    }

    return dates;

   }

尽管如此，这里的问题最终还是相似的.调用此脚本时，它将首先清除整个截止日期列，因此原始日期将丢失.

The issue here ended up being similar, though. When this script is called it first clears the whole due date column, so the original date will be lost.

任何想法，技巧和建议都值得赞赏.

Any thoughts, tips, suggestions appreciated.

答

尝试一下:

您可能希望将工作表名称更改为所需的名称

You may wish to change the sheet name to what ever you want

function onEdit(e) {
  var sh=e.range.getSheet();
  if(sh.getName()!="Due Date")return;//limits actions to a sheet named Due Date
  if(e.range.columnStart==1 && e.range.rowStart>1 && e.value && e.value.length>0) {
    var td=new Date();
    var dd=new Date(td.getFullYear(),td.getMonth(),td.getDate()+7);    
    e.range.offset(0,2).setValue(Utilities.formatDate(dd, Session.getScriptTimeZone(), "MM/dd/yyyy"));
  }
  if(e.range.columnStart==1 && e.range.rowStart>1 && !e.value) { 
    e.range.offset(0,2).setValue('');
  }
  if(e.range.columnStart==2 && e.range.rowStart>1 && e.value=="Completed") {
    e.range.offset(0,1).setValue("Done");
  }
  if(e.range.columnStart==2 && e.range.rowStart>1 && e.value!="Completed") {
     var td=new Date();
    var dd=new Date(td.getFullYear(),td.getMonth(),td.getDate()+7);    
    e.range.offset(0,1).setValue(Utilities.formatDate(dd, Session.getScriptTimeZone(), "MM/dd/yyyy"));
  }
}

这是我的电子表格的外观:

This is what my spreadsheet looks like:

我还在第二列中包含了用于数据验证的对话框.

I also included the dialog for the data validation on the second column.

相关推荐