Addressing individual cells in Google Sheets

0
votes

Not sure it can be done but here is the question. I have a sheets app with multiple sheets. Sheet 1 contains data as follows:

String Descriptor | Value 1 | Value 2

Sheet 2 is working formulas. Cell D37 in this example has a reference to 'Sheet1'!A14

On sheet 2, column E I'd like to reference back to 'Sheet1'!A16

If I pass the string value of "D37" I can find the cell and then manipulate things and return the proper data. I can't however get things to work when I try to get the cell address passed by the function I get "range not found" errors.

Sheet 2 reference is '=INDIRECT(myFunction(D37))

function myFunction(piNameCell) { //piNameCell has the reference to D37

  var ss = SpreadsheetApp;
  var sheet = ss.getActiveSheet().getRange(piNameCell);  // Like this it crashes
  //var sheet = ss.getActiveSheet().getRange("D37");       //This works
  var sheetRef = sheet.getFormula();

  //remove the leading '=' sign
  sheetRef = sheetRef.substring(1);

  //find the !
  var bangIndex = sheetRef.indexOf("!");

  //change the column to 'E'
 sheetRef = sheetRef.substring(0,bangIndex+1) + "E" + sheetRef.substring(bangIndex+2)

  return sheetRef;
}
1
google-apps-scriptgoogle-sheetscellformulacustom-function
if you log piNameCell, what do you get? – Liora Haydont
The contents of the referenced cell, not the formula. – Jon Waller
Possible duplicate of passing cell references to spreadsheet functions – Rubén
Ruben, I read through that and tried many iterations. The proposed is =GetFormula("C4")Which puts a cell to reference as a string passed to the function GetFormula. This won't change the "C4" reference if the cell is copied to other cells. =GetFormula(C4) is what I'm looking for. – Jon Waller
The getRange functions sort of works but only for a single use as you can't pass C4:C4 in one cell and reference C5:C5 in another. – Jon Waller

1 Answers

0
votes

Like you said in your questions, you need to send the adress of the cell, not the content, but you don't want to send it as a string, because you want it to change if you move the formula. You have some options.

Get the cell coordinates

You could simply get the cell coordinates with the ROW and COLUMN functions.
You'd have to change a little your function to use the coordinates instead. Your function call could look like that:

=INDIRECT(myFunction(ROW(D37), COLUMN(D37)));

And in your function you'd change the parameters and the getRange function.

Use the cell coordinates to get the A1 notation

If you really want to send that A1 notation, you can do that with ADDRESS.

=INDIRECT(myFunction(ADRESS(ROW(D37), COLUMN(D37))));