I would like to create a function in script editor that would allow me to input data from a cell on another sheet without having to put 'sheet1!A1' in the formula.

Say I have my sheet name in A1 and want to return the number in the first row and first col (i.e. A1) of that sheet. I would like to show that value with a formula as simple as getcell(A1,1,1)

Here's what I've got but I get an error saying "Exception: Range not found (Line 4)"

function getcell(sheetName,row, col) {
  var ss = SpreadsheetApp.getActive()
  var range = ss.getSheetByName(sheetName).getRange(row, col)
 ss.getRange(range).getValue();
}

CodePudding user response：

I believe your goal is as follows.

• You want to return A1 as A1Notation by putting =getcell(A1,1,1) to a cell.
• You want to achieve this using the custom function.

In this case, how about the following modification?

Modified script:

function getcell(sheetName, row, col) {
  var ss = SpreadsheetApp.getActive()
  var range = ss.getSheetByName(sheetName).getRange(row, col);
  var res = range.getA1Notation();
  return res;
}

• In your script, range is used in getRange(range). By this, such an error occurs.
• In order to return the A1Notation, getA1Notation() is used.
• If you want to return the cell value, please modify getA1Notation() to getValue().

Reference:

• getA1Notation()

CodePudding user response：

Three small corrections:

1. You need to pass the sheet name in quotes

2. You are trying to get the range twice

3. For a custom funciton, you need to return the value

Sample code:

function getcell(sheetName,row, col) {
  var ss = SpreadsheetApp.getActive()
  var range = ss.getSheetByName(sheetName).getRange(row, col)
 return range.getValue();
}

Sample call:

=getcell("Sheet1",1,1)