I have a script that pastes a formula from one cell into a range in a column. When pasted the range just disappears. I mean the whole formula is OK except the pointer. Any ideas how to deal with this?

function formulas() {
  var planil = SpreadsheetApp.getActiveSpreadsheet();
  var shimi = planil.getSheets()[0];

Formula to copy:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE((A:A),CHAR(34)&"[email protected]"&CHAR(34)&" <[email protected]","MUSIMUNDO"),CHAR(34)&"[email protected]"&CHAR(34)&" <[email protected]","DIRECTV"),CHAR(34)&"[email protected]"&CHAR(34)&" <[email protected]","TLC"),CHAR(34)&"[email protected]"&CHAR(34)&" <[email protected]","SONY")

Formula Copied :

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(({}),CHAR(34)&"[email protected]"&CHAR(34)&" <[email protected]","MUSIMUNDO"),CHAR(34)&"[email protected]"&CHAR(34)&" <[email protected]","DIRECTV"),CHAR(34)&"[email protected]"&CHAR(34)&" <[email protected]","TLC"),CHAR(34)&"[email protected]"&CHAR(34)&" <[email protected]","SONY")
  • 1
    omg , believe me i tried this 1 hour , but NOW is workingg !!! loooll thank you very muchhh :D
    – user67277
    Apr 29, 2014 at 1:40

1 Answer 1


It seems that when copying from ColumnE into ColumnB without the $s the pasted formulae were trying to automatically adjust but since A:A is the first column they were unable to adjust to a column further to the left, and that the solution is merely to fix the column reference in the source formula.

  • Exactly that was the issue thank you very much pnuts :D !
    – user67277
    Apr 29, 2014 at 1:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.