0

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;
}
Rubén
  • 34,714
  • 9
  • 70
  • 166
Jon Waller
  • 1
  • if you log `piNameCell`, what do you get? – Liora Haydont Jun 19 '18 at 21:14
  • The contents of the referenced cell, not the formula. – Jon Waller Jun 19 '18 at 23:33
  • Possible duplicate of [passing cell references to spreadsheet functions](https://stackoverflow.com/questions/12206399/passing-cell-references-to-spreadsheet-functions) – Rubén Jun 20 '18 at 00:14
  • 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 Jun 20 '18 at 00:34
  • 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 Jun 20 '18 at 00:36

1 Answers1

0

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))));
Liora Haydont
  • 1,252
  • 1
  • 12
  • 25
  • And this messy thing is the actual solution I contrived while trying to find something more elegant. `INDIRECT(fetchPrice(row(A4), CHAR(COLUMN(A4)+64)))` Thank you for your input and support. – Jon Waller Jun 20 '18 at 16:49