Google Sheets execute script onEdit

Question

I'm new to Google Sheets Script and got stuck trying to do the following. I have 2 columns: C containing start timecode and D containing end timecode. A third column (F) contains the length of a film cue resulting from start timecode and end timecode. Since the timecode results from a script, the cells containing the result cannot be exported to another format. So I am trying to create a 4th column that will print the results from column F as values. I managed to write a little script that can do it. It looks like this:

function getRangeValuesEP01() {
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var range = ss.getSheetByName("EP01").getRange('F3:F100').activate();
 var values = range.getValues();
 return values;
};

The thing is, it does not execute automatically, when I edit any of the timecodes in C or D.

So I tried this:

function onEdit(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet()
  var sheetName = sheet.getName();
  if ( sheetName == "EP02" || sheetName == "EP01" ) {
    var range = sheetName.getRange('F3:F100').activate();
    var r = sheetName.getActiveRange();
  if (r.getColumn() == 4 ) {
      var col = r.getColumn();
      if ( col >=  3 && col <= 100 ) {
        sheetName.getRange(r.getRow(),5);
        var values = range.getValues();
      }
    }
  }
 }

It does not give me any errors, but it does not do anything either. It probably does not really make sense. As I said, I am new to those scripts. And it does not take into account that is has to look for editing in both columns C and D. Oh, or would it be enough to look at F, since that already updates its results when C or D are changed?

Maybe someone can push me into the right direction here?

Cooper Cooper · Accepted Answer · 2017-09-09T17:12:27

Read comments:

function myonEdit(e) 
{
  var sheet=e.source.getActiveSpreadsheet();
  var sheetName=sheet.getName();
  if(sheetName=="EP02" || sheetName=="EP01") 
  {
    var r=sheet.getRange('F3:F100');//I made changes up to this point but the next line makes no sense so rethink it in light of changes
    if (r.getColumn()==4)//This line doesn't make any sense because you set range to F3:F100 F is column 6 so it will never be 4 
    {
      var col= r.getColumn();
      if ( col >=  3 && col <= 100 ) 
      {
        sheetName.getRange(r.getRow(),5);
        var values = range.getValues();
      }
    }
  }
}

Google Sheets execute script onEdit

1 Answers