1

Here is the code I'm attempting:

var day=new Date().getDay()-1;
var month=new Date().getMonth();
var sheet=ss.getSheetByName((/^.\s/+month+'/'+day));

I'm new to regex, but essentially I'm writing a script for work that selects sheet by name, to be copied to another sheet based on certain criteria.

The spreadsheet contains a multitude of sheets, with old sheets being deleted and new ones added on a regular basis. Each sheet ends with a suffix in the format mm-dd. How can I select a sheet based only off of the suffix, which is consistent?

The above, when parsed with the rest of my code, returns:

TypeError: cannot call method "getDataRange" of null.

1

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:

function getSheetByMonthAndDay(month, day) {
  var dateStr = format2digit(month) + "" + format2digit(day); // Formats the parameters into a 4-length string, e.g. '1231'
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for (var i = 0; i < sheets.length; i++) { // Iterate over sheets
    var sheet = sheets[i];
    var name = sheet.getName(); 
    if (name.indexOf(dateStr) == name.length - dateStr.length) { // Check if the sheet name ends with the date (e.g. '1231');
      return name; // You might want to return sheet here, instead of name
    }
  }
  throw Error("No sheet for month " + month + ", day " + day);
}

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 as

function format2digit(integer) {
  if (integer < 10) {
    return "0" + integer;
  } else {
    return integer;
  }
}

I have set up an example spreadsheet that you can look at.

  • 1
    Thank you very much, your script works very well, with the exception(at no fault of yours) that the date suffix is not in the format to mm-dd as I specified(sorry) specifically, but as mm-dd when each > 10, otherwise m-d or m-dd or mm-d, etc. I reused your code, but dropped the formatting section. Thanks again. +1. – SonOfEntropy Jun 5 '14 at 15:49
1

The following code uses the regexp and match.

Code

function mySheet(str) {
  var ss = SpreadsheetApp.getActive(), numSheets = ss.getNumSheets();
  var reg = new RegExp(str);
  for(var i=0; i<numSheets; i++) {
    if(ss.getSheets()[i].getName().match(reg) == str) {
      return 'Sheet number ' + ss.getSheets()[i].getIndex() + ' gives a match';
    }
  }
  return 'No matching sheet found !';
}

Usage

enter image description here
enter image description here

  • can I approve both answers? Is that allowed? They both achieve the desired affect, though I admit yours is closer to my original intention. Either way, all's well that ends ok. – SonOfEntropy Jun 6 '14 at 15:23
  • @SonOfEntropy No, that's not possible. Just wanted to post a different technique. You can upvote or leave it as it is. – Jacob Jan Tuinstra Jun 6 '14 at 15:39

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.