(Help) How To: Insert a HyperLink depending on how

2019-08-27 01:12发布

问题:

I have the Column "A" and I want to insert a Hyperlink to the data in the cell bases on depending on how many characters have the cell starting from 2nd row. For Example:

  • If the Cell A2 have the data (1-2345678901) = 12 characteres insert the link www.mysite.com/ + value of the cell A2

Result should be www.mysite.com/1-2345678901

  • If the Cell A2 have the data (1-2345678) = 9 characteres insert the link www.mysite2.com/ + value of the cell A2.

Result should be Hyperlink www.mysite.com/1-2345678

I needs to know if this script can be tunned or this script is very Ok. If anyone have a new idea please feel free to let me know.

SCRIPT:

    function onEdit(e)
    {
      var activeCell = SpreadsheetApp.getActiveSpreadsheet().getActiveCell();
      var activeCellValue = activeCell.getValue();


      var row = activeCell.getRow();
      var column = activeCell.getColumn();

      if(row < 2)   return; //If header row then return
      var colNums  = [1]; //Coulmns, whose edit is considered
      if(colNums.indexOf(column) == -1) return; //If column other than considered then return

      var length = String(activeCellValue).length;

      if(length == 12)
      {
        activeCell.setValue('=HYPERLINK' + '("www.mysite.com='+activeCellValue+'";"'+activeCellValue+'")')
      }
      else if(length == 9)
      {
        activeCell.setValue('=HYPERLINK' + '("www.mysite2.com='+activeCellValue+'";"'+activeCellValue+'")');
      }
    }

回答1:

Your script can be tweaked for better performance, by making use of the event data delivered to it instead of resorting to Service calls. You have done the right thing by testing for bail-out conditions early, but even those can be reordered to increase the chance of exiting early.

function onEdit(e)
{
  var activeCell = e.range;
  var activeCellValue = e.value;

  if (!e.value) return;    // do nothing if value is 'undefined'

  var column = activeCell.getColumn();

  var colNums  = [1]; //Columns, whose edit is considered
  if(colNums.indexOf(column) == -1) return; //If column other than considered then return

  var row = activeCell.getRow();
  if(row < 2)   return; //If header row then return

  var length = String(activeCellValue).length;

  if(length == 12)
  {
    activeCell.setValue('=HYPERLINK' + '("www.mysite.com/'+activeCellValue+'";"'+activeCellValue+'")')
  }
  else if(length == 9)
  {
    activeCell.setValue('=HYPERLINK' + '("www.mysite2.com/'+activeCellValue+'";"'+activeCellValue+'")');
  }
}