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];
shimi.getRange("E5").copyTo(shimi.getRange("B3:B100"));
}
Formula to copy:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE((A:A),CHAR(34)&"novedadesmusi@daeva.com.ar"&CHAR(34)&" <novedadesmusi@daeva.com.ar","MUSIMUNDO"),CHAR(34)&"novedadesdtv@daeva.com.ar"&CHAR(34)&" <novedadesdtv@daeva.com.ar","DIRECTV"),CHAR(34)&"novedadestlc@daeva.com.ar"&CHAR(34)&" <novedadestlc@daeva.com.ar","TLC"),CHAR(34)&"novedadessony@daeva.com.ar"&CHAR(34)&" <novedadessony@daeva.com.ar","SONY")
Formula Copied :
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(({}),CHAR(34)&"novedadesmusi@daeva.com.ar"&CHAR(34)&" <novedadesmusi@daeva.com.ar","MUSIMUNDO"),CHAR(34)&"novedadesdtv@daeva.com.ar"&CHAR(34)&" <novedadesdtv@daeva.com.ar","DIRECTV"),CHAR(34)&"novedadestlc@daeva.com.ar"&CHAR(34)&" <novedadestlc@daeva.com.ar","TLC"),CHAR(34)&"novedadessony@daeva.com.ar"&CHAR(34)&" <novedadessony@daeva.com.ar","SONY")
Best Answer
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.