Google Sheets - use script across multiple sheets

I can't get this script to work across the other tabs/sheets within my worksheet. It works on the current sheet, but I need to duplicate that across the other sheets within my worksheet.

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]);
    }
  }
}

1 Answer

  1. Gavin- Reply

    2019-11-13

    Try below code.

    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]);
        }
      }
    }
    

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>