Podcast #128: We chat with Kent C Dodds about why he loves React and discuss what life was like in the dark days before Git. Listen now.
4 of 4 Removed edit notice. Applied code formating to cell references and function names.

Running a script from inside a sheet cell

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