I'm trying to get the contents of the active sheet. In trying to accomplish that, I am trying to use getRangeByName(). If you log the value of sheetName, you will see that it does return the name of the active sheet. But when I use sheetName as the argument for getRangeByName, range is null.

function TestMacro()
{
  var cellVal = SpreadsheetApp.getActiveSheet().getActiveCell().getValue();
  SpreadsheetApp.getUi().alert(FindIdenticalValue(cellVal));
}

function FindIdenticalValue(cellVal)
{
  var sheetName = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName();
  var range = SpreadsheetApp.getActive().getRangeByName(sheetName);
  var rowCount = range.getNumRows();
  var colCount = range.getNumColumns();
  return true;
}

CodePudding user response：

You could try something like this:

function TestMacro(){
  const cellVal = SpreadsheetApp.getCurrentCell().getDisplayValue();
  SpreadsheetApp.getUi().alert(FindIdenticalValue(cellVal));
}

function FindIdenticalValue(cellVal){
  const sheetName = SpreadsheetApp.getActiveSheet().getName();
  const sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  let tf = sheet.createTextFinder(cellVal).findAll();
  
  //don't where you want to go here
}

CodePudding user response：

Have a look here for what Named Ranges are. They are different that the name of the Sheet. The hierarchy within a Spreadsheet is as follows:

1. Name of the Spreadsheet File, e.g. Budget
2. Name of the Sheet within 1., e.g. 2021 Budget
3. Name of the Range within 2., e.g. line_items (which would be A2:A10 for example)

To see if you actually have any named ranges use this

function logNamedRanges(){
  const namedRanges = SpreadsheetApp.getActive().getNamedRanges()
  if( !ranges.length ) {
    console.log("No ranges available")
    return 
  }
  namedRanges.forEach( namedRange => {
    console.log( namedRange.getName() )
    const range = namedRange.getRange()
  })
}

As I understand you are trying to get contents of the active sheet, for that just do this:

function getActiveSheetData(){
   return SpreadsheetApp.getActiveSheet().getDataRange().getValues()
}