How to combine 2 multidimensional arrays replacing some of array1's element with the one from arrray2 using Google Apps Script?

Question

I've been trying to get this one done, without any success.

This is data to be placed where there is no formula. So, the idea is to merge these two arrays and place it at once.

array1 = 
[
 ["item1","","","details1"], 
 ["item2","","","details2"], 
]

This array contains formulas grabbed from the destination range where the incoming data will be placed, but since the formulas are needed,

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)),"")",""]
]

Expected Result

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"]
]

This is my attempt, but I can't seem to get to the bottom of it:

 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)
        }
      })
    }
  }

NEWAZA · Accepted Answer · 2022-06-16 19:46:15Z

1

Here's a simple way to insert the formula if no value is present:

function myFunction() {

  const formulas = [[]]
  const values = [[]]

  return values.map((row, rowIndex) => row.map((col, colIndex) => col || formulas[rowIndex][colIndex]))

}

answered Jun 16, 2022 at 19:46

NEWAZA

1,6202 gold badges6 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

onit Over a year ago

Nice website, by the way! This is exactly the same story...from creating formula based solutions on Excel and Google Sheets to scripting...I'm in my first year, though! Lots to learn! Cheers

RemcoE33 · Accepted Answer · 2022-06-16 19:39:53Z

0

This is one of the way's to do it.

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)

answered Jun 16, 2022 at 19:39

RemcoE33

1,6111 gold badge7 silver badges13 bronze badges

Collectives™ on Stack Overflow

How to combine 2 multidimensional arrays replacing some of array1's element with the one from arrray2 using Google Apps Script?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related