He estado tratando de hacer esto, sin ningún éxito.
Estos son datos para ser colocados donde no hay fórmula. Entonces, la idea es fusionar estas dos matrices y colocarlas a la vez.
array1 = [ ["item1","","","details1"], ["item2","","","details2"], ]Esta matriz contiene fórmulas extraídas del rango de destino donde se colocarán los datos entrantes, pero como se necesitan las fórmulas,
array2 = [ ["","=iferror(VLOOKUP(A63,'Client List'!$A$1:$S,19,0),"")","=iferror(if(B63="Agency", 'Reference Info'!$C$7, VLOOKUP(A63,'Client List'!$A$1:$T,20,0)),"")",""], ["","=iferror(VLOOKUP(A64,'Client List'!$A$1:$S,19,0),"")","=iferror(if(B64="Agency", 'Reference Info'!$C$7, VLOOKUP(A64,'Client List'!$A$1:$T,20,0)),"")",""] ]Resultado Esperado
array2 = [ ["item1","=iferror(VLOOKUP(A63,'Client List'!$A$1:$S,19,0),"")","=iferror(if(B63="Agency", 'Reference Info'!$C$7, VLOOKUP(A63,'Client List'!$A$1:$T,20,0)),"")","details1"], ["item2","=iferror(VLOOKUP(A64,'Client List'!$A$1:$S,19,0),"")","=iferror(if(B64="Agency", 'Reference Info'!$C$7, VLOOKUP(A64,'Client List'!$A$1:$T,20,0)),"")","details2"] ]Este es mi intento, pero parece que no puedo llegar al fondo:
let finalRowValues = [] for (let a = 0; a < array2.length; a++) { for (let n = 0; n < array1.length; n++) { array2[a].forEach(function(value, j){ if(value == '' && array1[n][j] != ''){ finalRowValues.push(array1[n][j]) } else { finalRowValues.push(value) } }) } }Aquí hay una manera simple de insertar la fórmula si no hay ningún valor presente:
function myFunction() { const formulas = [[]] const values = [[]] return values.map((row, rowIndex) => row.map((col, colIndex) => col || formulas[rowIndex][colIndex])) }Esta es una de las formas de hacerlo.
const array1 = [ ["item1","","","details1"], ["item2","","","details2"], ] const array2 = [ ["",`=iferror(VLOOKUP(A63,'Client List'!$A$1:$S,19,0),"")`,`=iferror(if(B63="Agency", 'Reference Info'!$C$7, VLOOKUP(A63,'Client List'!$A$1:$T,20,0)),"")`,""], ["",`=iferror(VLOOKUP(A64,'Client List'!$A$1:$S,19,0),"")`,`=iferror(if(B64="Agency", 'Reference Info'!$C$7, VLOOKUP(A64,'Client List'!$A$1:$T,20,0)),"")`,""] ] const result = array1.map((arr, i) => { const [place1, place2, place3, place4] = arr if(place2 == ""){ return [place1, array2[i][1], array2[i][2],place4] } else { return arr } }) console.log(result)