Google Apps Script getSheetByName returns null

user_10002945 0
Viewed 758

Results from getSheetName and getSheetByName are strange

    function BE1() {
  console.log("BE1() triggered");
  var variables = globalVariables(); //load the Global variables 
  console.log("sheetid = ", variables.sheetid);
  var sheetName = 'BE1';
  var ss = SpreadsheetApp.openById(variables.sheetid)
  if (!ss) {
    console.log("spreadsheet not found");
  }  else  {
    var numSheets = ss.getNumSheets();
    console.log("spreadsheet has ", numSheets, " sheets");
    var someSheet = ss.getSheetName();
    console.log("someSheet = ", someSheet);
    if (sheetName === someSheet) {
      console.log("names match");
    }  else  {
      console.log("names do not match");
    }  
    var sheet = ss.getSheetByName(sheetName);
    if (!sheet) {
      console.log("sheet BE1 not found");
    }  else  {
      console.log("sheet = ", sheet);
      var lastrow = sheet.getLastRow(); 
      console.log("lastrow = ", lastrow);
      console.log("question column = ", variables.question);
      var nextQuestion = sheet.getRange(lastrow, variables.question).getValue(nextQuestion);
      SlidesApp.getUi().alert(nextQuestion);
    }
  }

The console log:

> Stackdriver logs
Oct 12, 2020, 2:40:11 PM    Debug   BE1() triggered
Oct 12, 2020, 2:40:11 PM    Debug   sheetid =  1QiuqmfF1z4Fe_RVD5BmPhvxq5sloHZs8ANFQ8gRLabA
Oct 12, 2020, 2:40:11 PM    Debug   spreadsheet has  2  sheets
Oct 12, 2020, 2:40:11 PM    Debug   someSheet =  BE1
Oct 12, 2020, 2:40:11 PM    Debug   names match
Oct 12, 2020, 2:40:11 PM    Debug   sheet =  {}
Oct 12, 2020, 2:40:11 PM    Debug   lastrow =  2
Oct 12, 2020, 2:40:11 PM    Debug   question column =  1
Oct 12, 2020, 2:40:11 PM    Error   Exception: The parameters (null) don't match the method signature for SpreadsheetApp.Range.getValue.
    at BE1(Code:45:70)

Note the string matches whatever sheet is retrieved by ss.getSheetName() but does not work with getSheetByName. What stupid error am I making? I have broken all the code down to separate lines in order to display to console.

google-apps-script google-sheets
1 Answers

Solution:

Replace:

var nextQuestion = sheet.getRange(lastrow, variables.question).getValue(nextQuestion);

with

var nextQuestion = sheet.getRange(lastrow, variables.question).getValue();.

Issues:

  • Also nextQuestion is not defined in the code snippet you shared; before it is used. Therefore, nextQuestion is null and this is why you get:

    The parameters (null) don't match the method signature for SpreadsheetApp.Range.getValue.

Minimal Reproducible Example:

function myFunction() {

var ss = SpreadsheetApp.getActive();
var someSheet = ss.getActiveSheet();
var nextQuestion;
someSheet.getRange(1,1).getValue(nextQuestion);
}

nextQuestion does not have a value.

This will give you the same error:

The parameters (null) don't match the method signature for SpreadsheetApp.Range.getValue.

Bonus Information:

getSheetName() is supposed to be used with a sheet object and not with a spreadsheet object.

Therefore, instead of using:

var ss = SpreadsheetApp.openById(variables.sheetid)
var someSheet = ss.getSheetName();

it is preferable to use this logic instead:

var ss = SpreadsheetApp.getActive();
var someSheet = ss.getActiveSheet().getSheetName();

if you want to get the active (selected) sheet.

user_112252910
Related