0

I have a simple gsheet I want to run a script inside so it can be run from Android mobile. Buttons and menus do not work on Android mobile.

  • C3 says choose your sort.
  • D3 has a data validation list of my sort scripts. Only 1 there right now. NormalSort.

I want D3 to be blank and then I choose NormalSort from the drop down. Then OnEdit calls NormalSort. But it's not working.

In the script editor, NormalSort works fine on its own, but when in the sheet itself, the onEdit is not executing the NormalSort function when I clear D3 and then choose NormalSort from my dropdown. I'm sure the code in the onEdit function is missing something. I get no errors. It just doesn't get to running NormalSort.

function onEdit(e) {
  if (e.range.getC3Notation() == 'D3') {
    if (/^\w+$/.test(e.value)) {        
      eval(e.value)();
      e.range.clear();
    }
  }
}

function NormalSort() {
var activeSheet = SpreadsheetApp.getActiveSheet();
  var sheetName = activeSheet.getSheetName(); //name of sheet to be sorted
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  var headerRows = 3; 
  var range = sheet.getRange(headerRows+1, 1, sheet.getMaxRows()-headerRows, sheet.getLastColumn());
  range.sort([{column: 1, ascending: sortFirstAsc=true}, {column: 13, ascending: sortSecondAsc=false}]);
}
1

Unless I am missing something, on top of the getA1Notation error from the answer by @Rubén your onEdit function never calls NormalSort(). Change it to:

function onEdit(e) {
  if (e.range.getA1Notation() == 'D3') {
    NormalSort();
  }
}

For completeness, @Rubén stated in his answer that there is a "typo" in the OP's code. That we need to use getA1Notation and not getC3Notation, which is not a valid Google Apps Script property of the range class.

Also note that the OP's use of the eval could work provided the item avulauted is the full ine needed to call the function, or NormalSort(); which would probably entail adding "();" to the passed value.

  • 1
    Good catch! By the way you are missing the "typo", the OP used getC3Notation instead of getA1Notation. – Rubén Mar 20 '17 at 19:41
  • @Rubén I called it a "getA1Notation error" and didn't know how to reference your post, which was already showing. Good Team effort! Note that the event() function COULD possibly work IF the cell contains the full line to parse function properly OR it is edited to add "();" to the end, making it "NormalSort();" – Karl_S Mar 20 '17 at 20:28
  • Ups, I think that I skipped the "on top...", I'm sorry. Regarding how to reference another post the best is to get the link through the "share" link just below the corresponding post (: Anyway, try to make your answers self-contained, in other words, include al the necessary information just in case the other post is deleted or unreachable for any other reason. – Rubén Mar 20 '17 at 20:46
0

Partial answer to Revision 4

The code has a "typo" maybe caused by a missconception about how Google Apps Script/JavaScript works:

There isn't a property named getC3Notation for the range class. The property name is getA1Notation


Partial answer to Revision 1

Looks that you are running onEdit from the run command on the Google Apps Script editor because in such cases the event object, e, is not defined.

In order to run onEdit from the Google Apps Editor for debbuging purposes, you have to create a an auxiliary function to create the event object and assign the required properties. For a broader explanation see the answer from Mogsdad to How can I test a trigger function in GAS?

  • Yes you are right. When running it within the sheet it's not doing the sort it should or clearing D3. So need to figure out why. – Randy Starkey Mar 17 '17 at 4:09
  • Please update your question. I think that you should remove the error message and describe your test scenarios. – Rubén Mar 17 '17 at 4:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.