google-sheetsgoogle-sheets-formula

Why is this ARRAYFORMULA + VLOOKUP() + REGEXEXTRACT() with multiple conditions not working?


In a sheet, I have public images whose links are like this https://drive.google.com/file/d/1-gnl6ODS5sinOPsd0fcvszbA-bduNE2I/view?usp=sharing and the result should be: https://drive.google.com/uc?export=view&id=1-gnl6ODS5sinOPsd0fcvszbA-bduNE2I.

So, I'm working on this formula to have it the expected yield: https://drive.google.com/uc?export=view&id=1-gnl6ODS5sinOPsd0fcvszbA-bduNE2I

The formula:

=ARRAYFORMULA(IF(A3:A="",,Image("https://drive.google.com/uc?export=view&id="&regexextract(VLOOKUP($J$3:$J&" "&$N1:$N$1,
 QUERY({
 IMPORTRANGE("1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU","Image Link!A1:A")&" "&
 IMPORTRANGE("1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU","Image Link!C1:C"),
 IMPORTRANGE("1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU","Image Link!F1:F")}, 
 "select Col1,Col3"), 2, 0)),"d/(.+)/view")))

This is the file, in case you feel like giving it a shot.


Solution

  • Try:

    =ARRAYFORMULA(IF(A3:A="",,Image("https://drive.google.com/uc?export=view&id="&regexextract(VLOOKUP($J$3:$J&" "&$N1:$N$1,
     QUERY({
     IMPORTRANGE("1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU","Image Link!A1:A")&" "&
     IMPORTRANGE("1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU","Image Link!C1:C"),
     IMPORTRANGE("1gh5w0czg2JuoA3i5wPu8_eOpC4Q4TXIRhmUrg53nKMU","Image Link!F1:F")}, 
     "select Col1,Col2"), 2, 0),"d/(.+)/view"))))
    

    Result: enter image description here