Help needed with Google Sheets custom function using cell references

Other Questions Google Sheets
1

Hey everyone! I’m stuck on a Google Sheets problem. I’m trying to make a custom function that takes two cell references as input and does some calculations on a range in another sheet. Here’s what I’ve got so far:

function calculateStuff(cell1, cell2) {
  // Do math stuff here
  return result
}

The idea is that cell1 and cell2 would be like ‘A1’ and ‘A2’. I want to use these to grab a range from a different sheet in the same spreadsheet.

I thought I could do something like this:

let cellRange = cell1 + ':' + cell2
let dataRange = otherSheet.getRange(cellRange)

But it’s not working. I think I’m messing up the syntax somewhere. Can anyone point me in the right direction? I’d really appreciate any tips or resources on how to make this work. Thanks!

2

As someone who’s worked extensively with Google Sheets custom functions, I can tell you that handling cell references can be tricky. Here’s a workaround I’ve found effective:

Instead of passing cell references directly, use named ranges in your spreadsheet. Define named ranges for the areas you want to work with, then pass those names to your function as strings.

Your function could look like this:

function calculateStuff(rangeName) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getRangeByName(rangeName);
  // Do your calculations here
  return result;
}

In your sheet, you’d call it like:

=calculateStuff(“MyNamedRange”)

This approach is more flexible and easier to maintain. It also allows you to work across sheets without extra complexity. Just make sure to set up your named ranges correctly in the spreadsheet settings.

3

hey aroberts, ive had similar issues before. try using getRange() with separate arguments instead of concatenating. like this:

let dataRange = otherSheet.getRange(cell1.getRow(), cell1.getColumn(), cell2.getRow() - cell1.getRow() + 1, cell2.getColumn() - cell1.getColumn() + 1);

hope that helps!

4

I’ve encountered this problem before. The issue is that custom functions receive the cell values, not the cell references. To work with ranges, you need to use the INDIRECT function in your sheet.

Try modifying your function like this:

function calculateStuff(range) {
  var dataRange = SpreadsheetApp.getActiveSpreadsheet().getRange(range);
  // Perform calculations on dataRange
  return result;
}

Then in your sheet, use it like:

=calculateStuff(INDIRECT(“Sheet2!A1:B10”))

This approach allows you to reference ranges across different sheets. Remember to adjust the sheet name and range as needed for your specific use case.