Edit script to include function to change cell color when text changes

Question

I am managing a master document for team rosters. Currently I have this script that adds changes from a different roster document to the master roster document. I would like to note any changes to the master doc with colored cells.

The master sheet runs the script to sync the data from the source doc (other roster doc) to the target doc (master roster doc). I want to make it so that any changes to the master doc get highlighted (changed cell background color).

For this specific project, change is defined by additions to the doc (new rows of text) AND changes to existing data (existing rows changing text).

I'm not entirely sure how to implement onEdit triggers. I have read solutions to other problems (i.e. changing the color when the value changes to another specific value) but I haven't found a script that adds the color in when a change is made in general (any change at all, doesn't have to match a specific value).

    // create menu buttons
    function onOpen() {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var menuEntries = [{
            name: "Sync Spreadsheet Data",
            functionName: "copyDataToWorkingSpreadsheet"
        }];
        ss.addMenu("Data Update Functions", menuEntries);
    };

    // copy data from form sheet to live mapping sheet
    function copyDataToWorkingSpreadsheet() {

      // source doc
      var sss = SpreadsheetApp.openById('Source Doc');

      // source sheet
      var ss = sss.getSheetByName('teams');

      // source sheet
      var ss = sss.getSheetByName('players');

      // Get full range of data
      var SRange = ss.getDataRange();

      // get A1 notation identifying the range
      var A1Range = SRange.getA1Notation();

      // get the data values in range
      var SData = SRange.getValues();

      // target spreadsheet
      var tss = SpreadsheetApp.openById('Target Doc');

      // target sheet
      var ts = tss.getSheetByName('teams');

      // target sheet
      var ts = tss.getSheetByName('players'); 

      // set the target range to the values of the source data
      ts.getRange(A1Range).setValues(SData);

    };

Right now the script works just like I need it to. Any help implementing the color change is much appreciate!

Cooper Cooper · Accepted Answer · 2019-01-11T22:19:25

Something like this would probably work for you.

function trackChanges(e) {
  var rg=e.range;
  var sh=rg.getSheet();
  if(sh.getName()=='players'){
    var row=e.range.rowStart;
    var col=e.range.columnStart;
    sh.getRange(row,col).setBackground('#ffff00');//yellow highlight changes
  }
}

Please note: You cannot run a function like this from the script editor or menu (without supplying the event obj as described here).

Edit script to include function to change cell color when text changes

1 Answers