CODEWITHSUNDEEP
google-apps-script
Lance Lamberton
Lance Lamberton

Reputation: 29

Call cell data from different spreadsheets WITHOUT having to go to the sheet and select the cell

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();
}

Upvotes: 0

Views: 60

Answers (2)

ziganotschka
ziganotschka

Reputation: 26796

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)

Upvotes: 2

Tanaike
Tanaike

Reputation: 201378

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:

Upvotes: 2

Related Questions