Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Passing cell references to spreadsheet functions

Tags:

People also ask

How do you copy and paste formulas in Excel with changing cell references?

Select the cell that contains the formula you want to move. Click Home > Cut (or press Ctrl + X). Select the cell you want the formula to be in, and then click Paste (or press Ctrl + V). Verify that the cell references are still what you want.

How do you copy a formula and keep a cell reference?

Press F2 (or double-click the cell) to enter the editing mode. Select the formula in the cell using the mouse, and press Ctrl + C to copy it. Select the destination cell, and press Ctl+V. This will paste the formula exactly, without changing the cell references, because the formula was copied as text.


When I call a spreadsheet function, say int(f2), the function operates on the value in the cell. If cell("F2") contains 3.14159, the result would be 3. But when I call a different type of function — for example: row(f8) — the function takes the cell reference, and not the value, in this case, returning 8.

How do I get my custom function to work with the reference, rather than the value?

I can pass a string, and use getRange(), but, if I move or update the cells on the sheet, the strings won't change.

Really simple example:

function GetFormula(cellname) {
  return SpreadsheetApp.getActiveSheet().getRange(cellname).getFormula();
}

With this in my sheet's code, I can retrieve the formula in C4 like this: =GetFormula("C4")

But, this argument is a string, and I would rather pass a cell reference. A somewhat more complicated issue requires the calling cells to update when copied and pasted.

Any ideas?