在Google表格脚本中设置活动单元格的值 [英] Set value of active cell in Google sheets script

问题描述

我想创建一个公式，该公式在特定的单元格更改时创建一个时间戳.下面的代码可以做到这一点.我现在想做的是通过将公式转换为纯文本将该时间戳记固定在工作表上.

I want to create a formula which creates a timestamp on a certain cell change. The code below is okay for that. What I want to do now is anchor that timestamp to the sheet by converting the formula to plain text.

如果您要手动执行操作，则可以选择时间戳记，将其复制并粘贴为值.

If you would do it manually you would select the timestamps, copy them and paste them as values.

我不想手动执行操作，因此我在下面创建了脚本.它试图覆盖当前的活动单元.有人知道如何使它正常工作吗?

I don't want to do it manually so I created the script below. It tries to overwrite the currenct active cell. Does anybody have an idea how to get this working?

谢谢

function cvTimestamp(value) {
  var timezone = "GMT+1";
  var timestamp_format = "dd-MM-yyyy HH:mm:ss"; // Timestamp Format. 


  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];


  var date = Utilities.formatDate(new Date(), timezone, timestamp_format);

  return date

  // Returns the active cell
  var cell = sheet.getActiveCell();
  // try to write date to the active cell as a value.
  cell.setValue(date);

}

推荐答案

下面的脚本怎么样?当函数名称为"onEdit()"时，将执行该函数.并将电子表格的已编辑单元格更改为日期".

How about following script? When the name of function is "onEdit()", this is executed. And an edited cell of spreadsheet is changed to "date".

function onEdit(e){
  var row = e.range.getRow();
  var col = e.range.getColumn();
  if (row == 3 && col == 3){ // You can freely define the range for putting DATE. 
    var timezone = "GMT+1";
    var timestamp_format = "dd-MM-yyyy HH:mm:ss";
    var date = Utilities.formatDate(new Date(), timezone, timestamp_format);
    var col = 5; // If you want to put a value to colmun 'E' of editing cell, col is 5.
    var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    ss.getRange(e.range.getRow(), col).setValue(date);
  }
}

这篇关于在Google表格脚本中设置活动单元格的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！