In the event object for an onEdit trigger how is it that range get's defined as a real range object and a property identifier at the same time?

Viewed 350

I have a little code sample to highlight the difference between an object that I created and the trigger event object. In the trigger event object I can use the term e.range along with the standard range class functions and it works fine. However, I don't really know how to create a psuedo event object so that the range term is more than just the name of a property.

   function onEdit(e) {
      console.log(JSON.stringify(e));
      var sh=e.range.getSheet();//e.range is a real range object
      if(sh.getName()=='Sheet2' && e.range.columnStart>1) {
        sh.getRange(1,1).setValue(JSON.stringify(e)+ '\n' + e.range.getA1Notation());
        var rg=sh.getRange(e.range.getA1Notation());//e.range is a real range object
        var myObj={value:rg.getValue,range:{columnStart:e.range.columnStart,rowStart:e.range.rowStart,columnEnd:e.range.columnEnd,rowEnd:e.range.rowEnd}};
        console.log(JSON.stringify(myObj));
        try{
          sh.getRange(2,1).setValue(JSON.stringify(myObj)+ '\n' + myObj.range.getA1Notation());//myObj.range is not a real range object
        }
        catch(m) {
          console.log('Error: ' + m);
        }
        try{
          sh.getRange(3,1).setValue(e.range.getValue());
        }
        catch(n) {
          console.log('Error: ' + n);
        }
      }
    }

If you edit Sheet2 appropriately you will get the following Stack Driver Logs:

Stackdriver logs

May 29, 2020, 1:21:30 PM    Debug   {"source":{},"value":"55","user":{"email":"","nickname":""},"range":{"columnEnd":5,"columnStart":5,"rowEnd":3,"rowStart":3},"authMode":"LIMITED"}
May 29, 2020, 1:21:30 PM    Debug   {"range":{"columnStart":5,"rowStart":3,"columnEnd":5,"rowEnd":3}}
May 29, 2020, 1:21:30 PM    Debug   Error: TypeError: myObj.range.getA1Notation is not a function

Notice above I cannot use myObj.range with the getA1Notation() method. (note: I didn't actually expect it to work...I'd like to know how to build the object correctly so that it will work and I haven't stumbled on the correct explanation yet.

So the thing I'd like to learn is how to create a psuedo event object so I can use myObj.range in the same way as I can use e.range.

I feel real bad after Tanaike has gone to so much trouble. But my real goal is to be able to create the event object precisely as it is created by the server. So that I could test onEdit()s with a test function calling the onEdit(e) rather than requiring actual edits. And more importantly I'd really just like know how to build such an object.

I think this says what I'm shooting for. I want to know how to create the event object precisely as it is provided to us from the onEdit trigger including having e.range to be an object that is equivalent to Class Range and thus capable of utilizing any of the methods found here. I hope that's possible. I apologize for these late additions but they were prompted from some of Tanaike's questions. I'll probably need to review the question again and make it more clear.

This is some code I'm working on and it's far from complete but this is the sort of thing one might do with the knowledge of the answer to my question.

function onXditTest(row) {
  var row=row||2;
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('OnEditTest');
  const v=sh.getRange(row,1,1,sh.getLastColumn()).getValues()[0];
  const xsh=ss.getSheetByName(v[5]);
  const xrg=xsh.getRange(v[0],v[2],v[1]-v[0]+1,v[3]-v[2]+1);
  const evobj={range:{rowStart:v[0],rowEnd:v[1],columnStart:v[2],columnEnd:v[3]},value:v[4],range:xrg,source:ss};
  onXdit(evobj);
}

The real question is how to make evobj so that evobj.range is an object of Class Range and at the sametime have available evobj.range.columnStart, evobj.range.rowStart, evobj.range.columnEnd and evobj.range.rowEnd so that the code under test behaves the same way in onEdit(e) as it does in onXdit(e)

function onXdit(e) {//changed name so edits dont trigger the function
  e.source.toast('Entry');
  console.log(JSON.stringify(e));
  const sh=e.range.getSheet();
  if(sh.getName()=="Sheet2" && e.range.columnStart==4 && e.value=="TRUE" ) {
    e.source.toast('Past Condition');
    e.range.setValue("FALSE");
  }
}
2 Answers

I believe your goal as follows.

  • You want to create an event object like the event object returned with OnEdit event trigger using Google Apps Script.

For this, how about this answer?

I think that the event object from the OnEdit event trigger is returned from the internal server at Google side. So I'm not sure about the actual script for this. So in this answer, I would like to propose the method for creating the pseudo event object as you say.

In this answer, I used the JavaScript classes which can be used with V8. But I'm not sure whether this is the direction you expect.

Sample script:

In order to use this script, please copy and paste the following script to the script editor, and save it. Then, please edit the cell. By this, onEdit is run by the OnEdit event trigger.

const createEventObject = obj => {
  // Class for creating the range object.
  class createRangeObject {
    constructor(obj) {
      this.columnEnd = obj.columnEnd;
      this.columnStart = obj.columnStart;
      this.rowEnd = obj.rowEnd;
      this.rowStart = obj.rowStart;
    }

    getA1Notation() {
      return SpreadsheetApp
        .getActiveSheet()
        .getRange(this.rowStart, this.columnStart, this.rowEnd - this.rowStart + 1, this.columnEnd - this.columnStart + 1)
        .getA1Notation();
    }

    getSheet() {
      return SpreadsheetApp
        .getActiveSheet();
    }
  }

  // Main script of createEventObject.
  class main {
    constructor(obj) {
      this.obj = obj;
    }

    get range() {
      return new createRangeObject(this.obj);
    }
  }
  return new main(obj);
}

// This function is run by editing cells.
function onEdit(e) {
  // This is your object.
  var myObj={range:{columnStart:e.range.columnStart,rowStart:e.range.rowStart,columnEnd:e.range.columnEnd,rowEnd:e.range.rowEnd}};

  // Create the pseudo event object.
  const obj = createEventObject(myObj.range);

  console.log(obj.range)
  console.log(obj.range.getA1Notation())
  console.log(obj.range.getSheet().getSheetName())
}

Result:

In above sample script, for example, when the cells "B3:C5" are edited, the following result are retrieved.

  • obj.range is { columnEnd: 3, columnStart: 2, rowEnd: 5, rowStart: 3 }.
  • obj.range.getA1Notation() is B3:C5.
  • obj.range.getSheet().getSheetName() is Sheet1 which is the active sheet.

Note:

  • This answer, which uses the JavaScript classes, is one methodology. So I think that there might be more simpler methods.
  • This is a simple script for explaining of this workaround. So when you want to use this for your actual situation, please modify this.
  • Please use this script with V8.

Reference:

The first function here is executed with the RunOnXdit button in the sidebar and it called the onXdit() function passing a psuedo onEdit trigger event object along with a mode property so that I can distinguish between running in test mode and running in live mode.

function onXditTest(row) {
  var row=row||2;
  console.log('row: ' + row);
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('OnEditTest');
  const v=sh.getRange(row,1,1,sh.getLastColumn()).getValues()[0];
  const xsh=ss.getSheetByName(v[5]);
  const xrg=xsh.getRange(v[0],v[2],v[1]-v[0]+1,v[3]-v[2]+1);
  var evobj={value:v[4]?"TRUE":"FALSE",source:ss,mode:true};
  evobj.range=xrg;//assign Class Range Object
  evobj.range.rowStart=v[0];
  evobj.range.rowEnd=v[1];
  evobj.range.columnEnd=v[3];
  evobj.range.columnStart=v[2]
  onXdit(evobj);
}

The second function can be run from the above function or it can be run from the onEdit trigger with the same code. The above function is generating a psuedo onEdit trigger event object. I was totally shocked to realize how easy it was to create it.

function onXdit(e) {
//function onEdit(e) {
  e.source.toast('Entry');
  console.log(JSON.stringify(e));
  const sh=e.range.getSheet();
  if(sh.getName()=="Sheet2" && e.range.columnStart==4 && e.range.rowStart!=5 && e.value=="TRUE") {
    e.source.toast('Section 2');
    sh.getRange(1,1).setValue(JSON.stringify(e));
    e.range.offset(0,1).setValue('CheckBoxIsOff')
    e.range.setValue("FALSE");
  }
  if(sh.getName()=="Sheet2" && e.range.columnStart==4 && e.range.rowStart==5  && e.value=="TRUE") {
    e.source.toast('Section 2');
    sh.getRange(2,4,4,1).setValue(e.mode?"TRUE":"FALSE");
    //sh.getRange(5,4,1,1).setValue("FALSE");
    sh.getRange(2,5,4,1).setValue("");
  }
}

This is Sheet2 which the sheet that is to be connected to the onEdit tester

enter image description here

This is Sheet2 after pressing the Run OnXdit Button

enter image description here

It does exactly what I would have wanted the onEdit() with the same code to have done. I guess my big problem was thinking about it too much.

enter image description here

The image above is just a simple table for providing the appropriate event object for the onXdit() function.

Animation:

enter image description here

Related