Search code examples
google-sheetsgoogle-apps-script

Update IMPORTRANGE formula on column change in the source sheet

When in the source sheet, a column is moved, the "IMPORTRANGE" formula does not update accordingly. Example- Suppose in my source sheet, I have a column with header named "Value" in column "B".

enter image description here

And I use the following formula in destination sheet.

enter image description here

=ARRAYFORMULA(XLOOKUP($A2:$A, IMPORTRANGE(source_sheet_id, "source!$A$2:A"), IMPORTRANGE(source_sheet_id, "source!$B$2:B"), , 0, 1))

Now, in source sheet, if I move the "Value" column from "B" to "C", I want the above formula to update accordingly as follows-

=ARRAYFORMULA(XLOOKUP($A2:$A, IMPORTRANGE(source_sheet_id, "source!$A$2:A"), IMPORTRANGE(source_sheet_id, "source!$C$2:C"), , 0, 1))

Is there a way to achieve this?

Solution

  • One approach is to dynamically search for the column(in source sheet) with header Value. So now moving this specific column would not break this formula:

    =let(Σ,source_sheet_id,
     arrayformula(xlookup(A2:A,importrange(Σ,"source!A2:A"),choosecols(importrange(Σ,"source!A2:"&rows(importrange(Σ,"source!A:A"))),xmatch("Value",importrange(Σ,"source!1:1"))),)))