用另一个单元格的时间戳填充一个单元格，仅当另一个单元格不为空时才进行编辑 [英] Fill a cell with timestamp of another cell edit ONLY if another cell is not blank

问题描述

我需要用另一个单元格版本的时间戳填充google spreedsheet中的一个单元格.但是，只有在使用某些内容编辑另一个单元格而不是擦除该单元格的情况下，才应显示此时间戳记.

I need fill a cell in google spreedsheet with the timestamp of the another cell's edition. But this timestamp should be show only if the another cell is edited with some content, not with erase the cell.

我已经尝试过使用此代码，但是如果我擦除另一个单元格，也会显示时间戳.

I have been try with this code, but the timestamp is shown also if i erase the another cell.

function onEdit(e) 
{
  if ([8].indexOf(e.range.columnStart) != -1) 
  {
    if ([8].indexOf(e.range.columnStart) === '') 
    {
      e.range.offset(0, -7).setValue('0');
    }
    else
     e.range.offset(0, -7).setValue(new Date()).setNumberFormat("yyMMdd HH:mm:ss");
  }      
}

推荐答案

function onEdit(e) {
  // Make sure our edit is a single cell in the H column
  // and that our edit has a value
  if(e.range.columnStart === 8 && e.value.length) {
    e.range.offset(0, -7).setValue(new Date()).setNumberFormat("yyMMdd HH:mm:ss");
  } else {
    e.range.offset(0, -7).setValue('0');
  }
}

更新后的条件现在会执行3次检查:

The updated conditional now does 3 checks:

• e.range.columnStart === 8 确保我们从H列开始

• e.value.length 确保

• e.range.columnStart === 8 ensures we are starting in the H column

• e.value.length ensures that

1. 我们的编辑不是空白
2. 修改仅影响单个单元格，因为编辑多个单元格时 e.value 不可用(请参阅:

1. our edit is not blank
2. the edit only affected a single cell as e.value is not available when multiple cells are edited (see: Edit section)

[8] .indexOf(e.range.columnStart)===''我删除了此代码，因为 indexOf 无法返回空字符串.它将始终评估为false，这是所有修改都会更新时间戳的原因.

[8].indexOf(e.range.columnStart) === '' I removed this as indexOf cannot return an empty string. It would always evaluate to false and was the reason for all edits updating the timestamp.

文档为columnStart ，仍在寻找Google的官方来源. e.range.getHeight()=== 1&&e.range.getWidth()=== 1&&e.range.getColumn()=== 8 是一种经过验证的替代方案.

Documentation for columnStart, still looking for an official google source. e.range.getHeight() === 1 && e.range.getWidth() === 1 && e.range.getColumn() === 8 is a verified alternative.

这篇关于用另一个单元格的时间戳填充一个单元格，仅当另一个单元格不为空时才进行编辑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！