Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

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,

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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

>Solution :

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)
Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading