Read and write cell formulas from Google Apps Script custom function

Question

I am trying to write a GAS spreadsheet custom function that copies cell content to other cells. And I need to fill the target cells not only with the data of the source cell, but with its formula content (if it has any).

Now, I already know that this is basically impossible through custom functions as they always receive the result of cell calculations but not the cell formulas themselves, and they also cannot return formulas for their target cells.

On the other hand there are functions to read and write cell formulas, e.g. Range.getFormula() and Range.setFormula() which seem to make my endeavor possible. I simply have to find another way of calling them. UPDATE: Meanwhile I found that custom formulas in fact can read formulas using getFormula(), but they definitely don't have permission to write formulas into cells using setFormula().

My question is...

What would be the most elegant method to create something equivalent to a custom function that reads and writes formula content of cells? I think I could use an onEdit function that updates my target cells after each spreadsheet edit, but that would mean that I have to hard code the coordinates of the target cell range, which seems very hacky and would require code changes every time the target range is moved (e.g. when rows are inserted above it).

UPDATE: Example

An example would be a custom function that is able to read multiple ranges of cells (each range given as a distinct function parameter) and returns a joined range of cells.

=rangeJoin(A1:B10;D1:E15)

...would read the two ranges of size 2x10 and 2x15 and would fill a target range of size 2x25 with the subsequent cell contents of both ranges. The target range would start at the cell that contains rangeJoin and would spread 2 cells to the right and 25 cells down (as usual for a custom function). The custom function (or similar mechanism) should be able to copy formulas, so a cell containing =hyperlink("http://www.google.com";"Google") should appear in the target range as a hyperlink and not as a text cell with the naked word 'Google'.

How about a small pop up UI that would ask you to select the destination range? Would that be useable in your case? The function could be called by a button or a menu or even on each edit. Let us know what you think.:-) — Serge insas
Hi @Sergeinsas, well to be honest, I was hoping that there would be a more streamlined solution that would still be able to determine the origin and target ranges through some kind of marker in a cell. — Jpsy
There is certainly! But it's not clear in your question what this relationship between source and target actually is. Could you explain exactly? You also mentioned that it could change...please clarify — Serge insas
Hi @Sergeinsas, I have added an example to my OP that should explain what I try to achieve. — Jpsy
Very similar to Google Apps Script to VMerge tables WITH FORMATTING. About the best you could do would be a timed function to review merged ranges, looking for and copying over formulas. Ick. — Mogsdad

eddyparkinson eddyparkinson · Accepted Answer · 2013-12-03T05:53:36

Agree with "Mogsdad"

ie. this custom function works:

function myGrid() {
  return [[1,2],[3,"http://www.google.com"]];
}

but, custom functions can't write formulas to a sheet. See https://developers.google.com/apps-script/execution_custom_functions#permissions

As a workaround, you could use a "Trigger", such as a time based trigger, as "Mogsdad" suggests.

Read and write cell formulas from Google Apps Script custom function

1 Answers