First, the error: shs
is an array, since you populate it with ss.getSheets()
, so it doesn't have a method getActiveCell()
. However, the elements of the array has that method.
You are also referencing a variable sheet
, which you haven't defined anywhere.
Currently, your code is more suitable for going through a single sheet, you need to iterate through the shs
array to have it operate on all sheets.
The following function will go through all cells in all sheets:
function onEdit(event){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var shs = ss.getSheets(), num = shs.length;
var sheetName = []
for (var sheetIdx = 0; sheetIdx < shs.length; sheetIdx++) {
var sheet = shs[sheetIdx];
for(var i = 1; i<num; i++) {
...
}
}
But you wanted to skip the first sheet, so let's start with element 1
in the array (since the array is 0
-based, element 1
is the second sheet):
function onEdit(event){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var shs = ss.getSheets(), num = shs.length;
var sheetName = []
for (var sheetIdx = 1; sheetIdx < shs.length; sheetIdx++) {
var sheet = shs[sheetIdx];
for(var i = 1; i<num; i++) {
var editedCell = shs.getActiveCell();
var columnToSortBy = 1;
var tableRange = "A2:G99"; // What to sort.
if(editedCell.getColumn() == columnToSortBy){
var range = sheet.getRange(tableRange);
range.sort( { column : columnToSortBy, ascending: false });
}
}
}
}
As for your comment, your code
function onEdit(event) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
for (var n in ss.getSheets()) {
var sheet = ss.getSheets()[n];
var name = sheet.getName();
if (name == 'Dashboard')
return;
var editedCell = ss.getActiveCell();
var columnToSortBy = 1;
var tableRange = "A2:G99";
if (editedCell.getColumn() == columnToSortBy) {
var range = sheet.getRange(tableRange);
range.sort({column: columnToSortBy, ascending: false});
}
}
}
could work, if you replace return
with continue
. You don't want return
, it ends the script. continue
skips to the next element in the for
loop.
UNTESTED. Assuming the year is always 2015
something like the following in K2 copied down to K13 might suit:
=IF(J2="December",SUMIF(A:A,"<"&DATEVALUE("12/1/2015"),E:E),SUMIF(A:A,"<"&DATEVALUE(ROW()-1&"/1/2015"),E:E))
Best Answer
You can't use regex that way -
getSheetByName
does not accept a regex as its parameter, you need to have the full name (a string).That being said, there's no reason why you need regex to do this.
Simply iterate over the sheets, and examine their name to see if they match the given date:
This function returns the matching sheet's name (if any), or throws an exception if there's no such sheet.
The
format2digit
function is defined asI have set up an example spreadsheet that you can look at.