设置单元格中包含特定文本的日期 [英] Set date when a cell has specific text

问题描述

我有一个脚本，它将在第4列中的单元格具有文本"Assign"时将日期和时间写入第14列.我现在面临的问题是，当我不直接在第4列中输入分配"时，第14列就不会更改.

I have a script that will write the date and time to column 14 the moment a cell in column 4 has the text "Assign". The problem I'm facing now is that column 14 won't change when I don't directly put "Assign" in column 4.

例如:我告诉电子表格第4列的单元格4 = I4，并且在I4中有文本分配"，第14列将不显示当前日期时间.

For example: I tell spreadsheets column 4 cell 4 =I4 and I have the text "Assign" in I4, column 14 won't display the current date time.

当我手动将第4列的第4单元格设置为分配"时，第14列将显示日期时间.

The moment I manually make column 4 cell 4 "Assign" Column 14 will display the date time.

脚本在onEdit上有效，这可能是问题吗?我是Google Apps脚本的新手.

The script works onEdit, may this be the problem? I am new to Google Apps Script.

function onEdit(e) {
  var sh =  e.source.getActiveSheet();
  var col = e.range.getColumn();
  var val = e.range.getValue();

  //check if sheet is 'Blad1', column edited is 'C' & value is 'assign'
  if (sh.getSheetName() === 'Blad1' && col === 4 && val === 'Assign') {
    var row = e.range.getRow();
    var tz = e.source.getSpreadsheetTimeZone();
    var date = Utilities.formatDate(new Date(), tz, 'dd-MM-yyyy     hh:mm:ss');

    //set date in column 14 for same row
    sh.getRange(row, 14).setValue(date);

  }
}

仅当我将文本直接放在第4列中时，脚本才起作用.我希望每当第4列中的单元格更改为分配"时，脚本就起作用.

The script only works when I directly put the text in column 4. I want the script to work whenever a cell in column 4 changes to "Assign".

P.S.电子表格将从名为应用程序表"的程序中获取新数据.当应用表格将第4列更改为分配"时，我需要显示当前时间.

P.S. Spreadsheet will get new data from a program called app sheet. When app sheet changes column 4 to "Assign" I need to show current time.

推荐答案

如果正在更新第4列，则此脚本将仅执行 setValue()，这是 if语句专门检查:

This script will only perform setValue() if it is column 4 being updated, that's one of the things the if statement checks specifically:

if (sh.getSheetName() === 'Blad1' && col === 4 && val === 'Assign') {

如果您希望每次编辑工作表并且 val ==='Assign'时都运行此操作，那么您要做的就是从 if 语句并使用 getRange()而不是事件对象 e.range 来定义 val .

If you want this to run every time the sheet is edited and val === 'Assign', all you have to do is remove the column check from the if statement and define val using getRange() rather than the event object e.range.

function onEdit(e) {
  var sh = e.source.getActiveSheet();
  var row = e.range.getRow();
  var col = e.range.getColumn();
  var val = sh.getRange(row, 4).getValue();

  //check if sheet is 'Blad1' & value is 'Assign'
  if (sh.getSheetName() === 'Blad1' && val === 'Assign') {
    var tz = e.source.getSpreadsheetTimeZone();
    var date = Utilities.formatDate(new Date(), tz, 'dd-MM-yyyy     hh:mm:ss');

    //set date in column 14 for same row
    sh.getRange(row, 14).setValue(date);
  }
} 

这篇关于设置单元格中包含特定文本的日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！