Using onEdit in Google Spreadsheets to edit cell c

问题:

I'm having the hardest time getting my onEdit script to work. My work has a Phone Log sheet that we use to track phone calls (Date, Time, Assigned Tech, etc). We have another sheet set up as a pivot table report called Phone Calls by Tech. This sheet is supposed to automatically count the number of calls per day per tech. It works, but the problem is any time extra whitespace gets added to a cell in the Assigned Tech column in the Phone Log sheet, the Phone Calls by Tech sheet treats that as a whole other person, so it appears as though there's 4 different "Bob"s, etc.

I've already successfully written a script to clean this up by trimming the whitespace in each cell of the Assigned Tech column, and I'm trying to translate this into an onEdit script to accomplish the same thing live.

function onEdit(e) 
{ 
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var r = ss.getActiveRange();
}

function myOnEdit() 
{
  function replacer( str, p1, p2, offset, s ) {
    return p1 
      }

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = ss.getActiveCell().getA1Notation();
  var tsheet = "Phone Log";
  var sname = ss.getName();

  if (sname = tsheet) //Are we in the Phone Log sheet?
  {

    var r = ss.getActiveRange();
    var column = r.getColumn();

    if (column == 4) //Are we editing a cell in the "Assigned Tech" column?
    {

      var value = cell.getValue();
      if ( value == String )
      {
        value = value.replace( /^\s*(.*?)\s*$/gm , replacer );
        cell.setValue( value) ;
      } 
    }
  }

}

So basically, I'm trying to get the cell value being edited, i.e. " Bob ", remove the whitespace and replace the inputted value with the edited one, "Bob". I get no errors when I run the above script in script editor, but it just doesn't work. I did install onMyEdit() as an onEdit trigger.

Any thoughts?

回答1:

I'm surprised this works for you. Because of the getA1Notation() call, cell contains a String, so which won't support the .getValue() or .setValue() methods.

The concept of "Active" Sheets & Cells isn't really applicable to onEdit() trigger functions; they are invoked in their own execution instance, and an event is passed to them to provide context. See Understanding Events.

You are also making a simple-but-catastrophic mistake with =. A single = is an assignment operation, and there are two flavors of comparison, == and ===. (The first compares the content of two objects, the second also compares the type of those objects.) When you have if (sname = tsheet), it is not comparing the names of the sheets, it's assigning the content of tsheet to sname, then doing a boolean test of sname. (This type of error is not caught by the interpreter, because it's perfectly valid... it's just not what you wanted.)

When you are checking if ( value == String ), do you mean to check if the TYPE of the value is a string? If so, that would be done like this: if (typeof value === 'string')

Updated script

function myOnEdit(e) 
{
  function replacer( str, p1, p2, offset, s ) {
    return p1 
      }

  var ss = e.source;
  var cell = e.range;
  var tsheet = "Phone Log";
  var sname = ss.getName();

  if (sname === tsheet) //Are we in the Phone Log sheet?
  {

    var column = cell.getColumn();

    if (column == 4) //Are we editing a cell in the "Assigned Tech" column?
    {

      var value = cell.getValue();
      if (typeof value === 'string')
      {
        value = value.replace( /^\s*(.*?)\s*$/gm , replacer );
        cell.setValue( value );
      } 
    }
  }
}