CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
Orcinus
Orcinus

Reputation: 21

Same script 2 Google Sheets tabs

I have a script that I want to make work for 2 tabs of a Google Spreadsheet. I get the error

"The coordinates of the range are outside the dimensions of the sheet."

For one of them, but the other works just fine with the same script. Below is the script, trying to filter for only the 2 sheets.

function onEdit(event) {
  var sheet = event.source.getActiveSheet();
  var editedCell = sheet.getActiveCell();

  if (sheet.getName() === "Successful Guide Searches") {
    if (editedCell.getColumn() == 2) {
      var range = sheet.getRange("A1:C1000");
      range.sort([{ column: 2, ascending: false }, { column: 3, ascending: true }]);
    }
  }
  if (sheet.getName() === "Failed Guide Searches") {
    if (editedCell.getColumn() == 2) {
      var range = sheet.getRange("A1:C1000");
      range.sort([{ column: 2, ascending: false }, { column: 3, ascending: true }]);
    }
  }
}

Upvotes: 0

Views: 70

Answers (2)

pgSystemTester
pgSystemTester

Reputation: 9907

I think you're either calling your trigger wrong or you aren't scoping your ranges correctly. Using the below modified code on this sample sheet, I was able to get it to run as I would expect.

/**
 * @OnlyCurrentDoc
 */

function onEdit(e) {
  var theRange = e.range;
  var sheet = theRange.getSheet();
  var theSheetName = sheet.getName();
  if (theSheetName === "Successful Guide Searches" ||
    theSheetName === "Failed Guide Searches" &&
    theRange.getColumn() == 2) {
      var range = sheet.getRange("A1:C");
      range.sort([{ column: 2, ascending: false }, { column: 3, ascending: true }]);
    }
}

Upvotes: 0

Cooper
Cooper

Reputation: 64040

function onEdit(e) {
  const shts = ["Successful Guide Searches", "Failed Guide Searches"];
  const sh = e.range.getSheet();
  const rg = sh.getRange(1, 1, sh.getLastRow(), 3);
  if (~shts.indexOf(sh.getName()) && e.range.columnStart == 2) {
    rg.sort([{ column: 2, ascending: false }, { column: 3, ascending: true }]);
  }
}

Upvotes: 1

Related Questions