8

I have a spreadsheet with two sheets called Robin and Lucy. I've made/found/mangled a script to sort the data on the sheet each time I add some data to column A:

function onEdit(event){

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

  var columnToSortBy = 1;
  var tableRange = "a2:I30";
    if(editedCell.getColumn() == columnToSortBy){   
   var range = sheet.getRange(tableRange);
   range.sort( { column : columnToSortBy } );
  }
}

This script works well but I only want it it be applied on the first sheet, Robin. The data in the second sheet, Lucy, isn't the same so I'm going to create another script for a different range for that one, once I get my head around this problem.

I think I need to use the getSheetByName("Robin") but I can't seem to get it to work.

11

There are a couple of ways to do that. First, you need to declare the spreadsheet:

var ss = SpreadsheetApp.getActiveSpreadsheet();  

Secondly, declare the first sheet like this:

var sh0 = ss.getSheets()[0];

or like this:

var sh0 = ss.getSheetByName("Robin");

Then set the active cell and retrieve the column index:

var editedCell = sh0.getActiveRange().getColumnIndex();

And do the logic for the sort:

var columnToSortBy = 1;
var tableRange = "a2:I30";

if(editedCell == columnToSortBy){   
  var range = sheet.getRange(tableRange);
  range.sort( { column : columnToSortBy } );
}

Complete code, a bit shortened:

function onEdit(){
  var sh0 = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var editedCell = sh0.getActiveRange().getColumnIndex();

  if(editedCell == 1) { 
    var range = sh0.getRange("A2:I30");
    range.sort({column: 1});
  }
}
  • @oohrogerplamer You can accept the answer by marking it, see this faq area. You're welcome. – Jacob Jan Tuinstra Feb 13 '13 at 12:07
  • How can I do this to sort by a column, but so it also sort the other columns too so all the data still matches in the rows? – moobot May 29 '15 at 5:15
1

Get the associated sheet (spreadsheet) and indicate which tab (sheet) you want to have focus:

var theSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Robin");
// do the thing
var theSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Lucy"); 
// do the other thing

Reference: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#getSheetByName(String)

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.