Javascript Question posted in
A very good W3school tutorial can be found here.

Javascript – Same code behaving differently inside and outside function

I have a google sheet with car names in one column. My goal is to write a script that will go to an API and pull prices for those cars and put them in another column when you click a button from a custom menu. Right now I am struggling to run a basic function to find the proper column in my sheet.

I have one code block that works as expected, but when I put it into a function it fails.

function fetchPrices(sheetName) {
  let sheet;
  sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  let data = sheet.getDataRange().getValues();
  let col = data[0].indexOf('Model');
}

const sheetName = 'Cars'
let sheet;
sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
let data = sheet.getDataRange().getValues();
let col = data[0].indexOf('Model');

Logger.log(col);
Logger.log(fetchPrices('Cars'))

These are, as far as I can tell, the exact same four lines of code to find the index of the column called ‘Model,’ but when I run this I get

Info 0.0 (the expected answer)
Info null
TypeError: Cannot read properties of null (reading ‘getDataRange’)

What is going on and why is the code in the function not running properly?

Update

Here is a link to a demo sheet that is exhibiting the same behavior as my actual data.

Tags:

2

Answers


  1. 0

    The indexOf() method returns the first index at which a given element can be found in the array, or -1 if it is not present. So, it is working correctly as Model is the 1st element in the array and arrays are zero based.

    =fetchPrices("Cars")

    return 0 as expected

    function fetchPrices(sheetName) {
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  var data = sheet.getDataRange().getValues();
  var colnum = data[0].indexOf('Model');
  return colnum;
}
    Login or Signup to reply.
  2. 0

    Recommendation:

    Logger log shows the intended output. The reason why you are still getting an error is due to the sheetName parameter being empty when the function is called. Google Apps Script is a procedural programming as it requires a selected function to run from top to bottom, which in your case is the fetchPrices. Since the sheetName doesn’t have a value yet, the SpreadsheetApp.getActive().getSheetByName() returns null. The preceding lines of code will also be affected.

    It would be best if you place the Logger.log inside a function to achieve modularity. It is a best practice to place any methods inside a function as much as possible and only place variable declarations outside any function if you intend them to be use as global variables (as seen below).

    function fetchPrices(sheetName) {
let sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
let data = sheet.getDataRange().getValues();
return data[0].indexOf('Model')

}


function test(){
Logger.log(fetchPrices('Cars'))
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search