谷歌表格 - 在多个表格中使用脚本

Question

我无法让此脚本在工作表中的其他选项卡/工作表中工作。它适用于当前工作表，但我需要在工作表中的其他工作表中复制它。

function onEdit(e) {
  if(e.range.columnStart != 5 || e.value != "TRUE") return;
  var date = new Date();
  var t = date.getTime();
  var r = e.range.getA1Notation();
  PropertiesService.getScriptProperties().setProperty(t,r);
}
function unCheck() {
  var d = new Date();
  var props = PropertiesService.getScriptProperties();
  var day = 1000; //milliseconds for 1 full day 86400000
  var keys = props.getKeys();
  for(var i in keys){
 Script    var t = parseInt(keys[i]);
    if(d.getTime() - t > day) {
      SpreadsheetApp.getActiveSheet().getRange(props.getProperty(keys[i])).setValue("FALSE");
      props.deleteProperty(keys[i]);
    }
  }
}

Answer 1

尝试下面的代码。

function onEdit(e) {
  if (e.range.columnStart != 5 || e.value != 'TRUE') return;
  var date = new Date();
  var t = date.getTime();
  var r = e.range.getA1Notation();
  var n = e.range.getSheet().getName(); // get sheet name
  PropertiesService.getScriptProperties().setProperty(t, r + '__|__' + n); // include sheet name
}
function unCheck() {
  var d = new Date();
  var props = PropertiesService.getScriptProperties();
  var day = 1000; //milliseconds for 1 full day 86400000
  var keys = props.getKeys();
  for (var i in keys) {
    var t = parseInt(keys[i]);
    if (d.getTime() - t > day) {
      var values = props
        .getProperty(keys[i])
        .toString()
        .split('__|__'); // split to get range and sheet name
      var a1 = values[0];
      var n = values[1];
      SpreadsheetApp.getSheetByName(n)
        .getRange(a1)
        .setValue('FALSE');
      props.deleteProperty(keys[i]);
    }
  }
}