CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Jason John Abraham
Jason John Abraham

Reputation: 13

Google sheets script doesnt work unless I make a copy of it

As the title says, I have a script that sorts column every time I input a keyword on a cell. But this only works only when I make a copy of the script. When I close the google tab and reopen, it wont work every time. Can someone help me with this.

Code:

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

function SortbyName() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Main");
  var range = sheet.getRange("A3:102");
  range.sort(1);
}

function SortbyClass() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Main");
  var range = sheet.getRange("A3:102");
  range.sort(2);
}

function SortbyContri() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Main");
  var range = sheet.getRange("A3:102");
  range.sort(3);
}

Screenshot

Upvotes: 0

Views: 104

Answers (1)

ale13
ale13

Reputation: 6072

From the error you get it seems like the error you encounter is on the onEdit trigger.

Therefore, you could make the following changes:

var regEx= /^\w+$/;
if (e.value.match(regEx)) {        
      eval(e.value);
      e.range.clear();
}

If you want to use Regular Expressions with Apps Script you have to use the .match() function or you could create a new RegExp object.

But since you are trying to do a simple sorting, you don't necessarily have to use a regular expression, you can try this:

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Main");
  var range = sheet.getRange("A3:17");
  if (e.range.getA1Notation() == 'A20') {
    if (e.value == "SortbyName" ) {        
      e.range.clear();
      range.sort(1);
    }
    else if (e.value == "SortbyClass" ) {        
      e.range.clear();
      range.sort(2);
    }
    else if (e.value == "SortbyContri") {        
      e.range.clear();
      range.sort(3);
    }
  }
}

Upvotes: 1

Related Questions