Creating a Custom Function in Google Sheets

Other Questions Google Sheets
1

I am attempting to develop a custom function similar to the following:

function calculateRange(startCell, endCell) {
   // Perform calculations based on the range defined by the provided arguments
   return calculatedResult;
}

The variable startCell refers to a specific cell like B1, and endCell is another cell reference like B2. I want these variables to reference a range in a different worksheet within the same Google Sheets document, such as B1:B2 in Sheet2.

When I leverage the getRange() method, using the argument "B1:B2" works fine for accessing the range. However, things break down in this scenario:

var combinedRange = startCell + ':' + endCell;
var targetRange = sheet2.getRange(combinedRange);

I believe there is a syntax issue that is causing the function to fail in recognizing combinedRange as a valid range. I’m feeling quite lost on this issue. Any assistance, suggestions for good syntax reference resources, or tutorials would be greatly appreciated, including solutions to my challenge.

2

You might be facing issues because the getRange() method typically expects a string with a correct range format. When combining using “+”, ensure that startCell and endCell are actually parsed as strings representing cell addresses. Also, make sure you have properly referenced the sheet, as the method fetches range relative to the sheet object you’re invoking it on. Try considering:

var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
var combinedRange = startCell + ':' + endCell;
var targetRange = sheet2.getRange(combinedRange);

Double-check if startCell and endCell values align with what you’re aiming for. Debugging or logging these values can pinpoint issues in range formation.