Google-apps-script – How to return the range of an edited cell using an onEdit event

google-apps-script

This seems like it should be easy as pie, but I can't seem to get any of my little test implementations to work. The ultimate goal is to create a function that runs only when cells in a certain range are edited. To accomplish this, I am creating a function that runs on every edit but starts by checking to see if the range of the edited cell is within a certain range, and only executing the rest of the code if this is true, and otherwise, it just ends immediately.

The first step in doing this, it seems to me, is to find out how to return the range of the edited cell. I've tried various tests.

Here is the current version, which I test by editing the spreadsheet:

function onEdit(e){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var eRange = e.range;
var message = eRange.getA1Notation();
ss.toast(message);
}

(output: toast does not appear/nothing happens)

I had about 10 other versions posted here, but I realized it was not helpful.

I've managed to return [object Object] and [undefined] in my toast message, so I know I'm not making some silly error with my toast function.

Doesn't e.range return a range? Is that where I go wrong? If it returns a range, shouldn't I be able to use any of the methods in range, just as if I'd used something like "ss.getRange("A1").someMethod()"? Or is there some difference that I don't understand?

Okay, I realize I've asked a couple of different questions here– I'm just trying to give a clearer picture of my thought process in case it helps you to correct me where I'm wrong. The simple question is this: what sort of command or method can I use to return the range of an edited cell?

I'm so sorry to ask such a basic question. I'm really afraid that someone will enter the right combination of search terms and the answer will be right on the top of the results… but I just can't seem to find it on my own, so here I am. I've tried lots of snippets of code from both StackOverflow and the old Google Groups support forum, as well as lots of my own attempts. I see people have asked lots of similar questions regarding my ultimate goal here– only running the onEdit function if cells in a certain range are edited– but the answers are all more complex and seem tailored to each individual situation, and they don't fit mine. I'm certain the answer is out there, but I've been searching for three straight evenings now and found many similar questions asked, and just can't seem to get my own implementation of those suggestions to work. Thanks for being patient with me, and thank you so much for any answers you can offer!

Best Answer

here is the code taken (and slightly modified) from the doc :

function onEdit(event){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = event.source.getActiveSheet();
  var r = event.source.getActiveRange();
  r.setComment("Last modified: " + (new Date())+' by '+Session.getActiveUser());
  ss.toast('Last cell modified = '+r.getA1Notation()+' by '+Session.getActiveUser());
}

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

Google-apps-script – Google Apps Script Active Cell changes in Handler from the onEdit Code that called it

You could handle that in your handler function simply like this :

function onClickHandler(e) {
  var app = UiApp.getActiveApplication();
  var sheet = SpreadsheetApp.getActiveSheet();
  var rowIndex = cell.getRowIndex()-1;
  var colIndex = cell.getColumnIndex();
  sheet.setActiveRange(sheet.getRange(rowIndex,colIndex));
  var cell = sheet.getActiveCell();
  ...

or you can store the coordinates of the cell in your Ui like in the example below (see the hidden widget and the callBackElement)

This has the advantage that it works even if the user uses the TAB key (or a mouseClick on another cell) to validate his entry... so I would recommend this approach even if it's a bit more code to add ;-)

function onEdit(event){
  var sheet = event.source.getActiveSheet();
  var cell = sheet.getActiveCell();
  var cellR = cell.getRow();
  var cellC = cell.getColumn();
  var cellValue = cell.getValue();
  var active_spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  if ((cellR >= 14 && cellR <= 21 && cellC >= 1 && cellC <= 7) ||
           (cellR >= 34 && cellR <= 41 && cellC >= 1 && cellC <= 7)) {

    displayUserOptions(cellR,cellC);
  }
}

function displayUserOptions(cellR,cellC){
  var app = UiApp.createApplication().setTitle('Choose Equipment Class').setHeight(40).setWidth(350);
  var mypanel = app.createHorizontalPanel();
  var hidden = app.createHidden('coord').setValue(cellR+'|'+cellC);
  mypanel.add(hidden);
  var handler = app.createServerHandler('onClickHandler').addCallbackElement(mypanel);
  var button1 = app.createButton('War').setTabIndex(1).setId('war').addClickHandler(handler);
  var button2 = app.createButton('Nature').setTabIndex(2).setId('nature').addClickHandler(handler);
  var button3 = app.createButton('Balance').setTabIndex(3).setId('balance').addClickHandler(handler);
  var button4 = app.createButton('Fortune').setTabIndex(4).setId('fortune').addClickHandler(handler);
  var button5 = app.createButton('Chaos').setTabIndex(5).setId('chaos').addClickHandler(handler);
  var button6 = app.createButton('Generic').setTabIndex(6).setId('generic').addClickHandler(handler);
  var mypanel = app.createHorizontalPanel();
  mypanel.add(button1); mypanel.add(button2); mypanel.add(button3);
  mypanel.add(button4); mypanel.add(button5); mypanel.add(button6);
  app.add(mypanel);

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  spreadsheet.show(app);

}

function onClickHandler(e) {
  Logger.log(Utilities.jsonStringify(e));
  var app = UiApp.getActiveApplication();
  var sheet = SpreadsheetApp.getActiveSheet();
  var rowIndex = Number(e.parameter.coord.split('|')[0]);
  var colIndex = Number(e.parameter.coord.split('|')[1])
  sheet.setActiveRange(sheet.getRange(rowIndex,colIndex));
  var cell = sheet.getActiveCell();
  if (e.parameter.source == 'war') {
  Logger.log("War button pressed");
  Browser.msgBox(cell.getValue()); 
  cell.setValue("War!"); // *This string is committing to the wrong cell.
  }else{
  cell.setBackground('#ffffaa');// just for test to confirm that the right cell has been modified
  }
  app.close();
  return app;
}

NOTE : if you prefer that the "active" cell is not changed but still want that the value comes in the right cell you can do it like this :

(change in the handler function)

  ... 
  var rowIndex = Number(e.parameter.coord.split('|')[0]);
  var colIndex = Number(e.parameter.coord.split('|')[1])
  var cell = sheet.getRange(rowIndex,colIndex);
  ...

Best Answer

Related Solutions

Javascript – Using onEdit in Google Spreadsheets to edit cell contents live

Updated script

Google-apps-script – Google Apps Script Active Cell changes in Handler from the onEdit Code that called it

Related Topic