Search code examples
google-sheetsgoogle-drive-apispreadsheet

Import data from a Google Sheet to other Google Sheet, based on column name

I export a CSV file from an APP in a Google Drive folder, the exported CSV have more columns that I dont need the data...and I only need to get the data from the COLUMNS that have a certain name (that I need) and imported to another sheet.

I let here a link with an exemple: https://docs.google.com/spreadsheets/d/1pnsqFsU9j_f2OrB8ojhQL4IVLvM-zOCkfI_21OVz4IM/edit#gid=0

I want in SHEET2 to import from SHEET1 only the column that starts with COL_NAME2, COL_NAME3 and COL_NAME5...but their position on the SHEET1 may change after each export..so it's posible that COL_NAME2..on the next import to be on position C1, not like not B1.

Need some help please.

I try this, but not seems to do what I want.

={ "COL_NAME2"; importrange("1pnsqFsU9j_f2OrB8ojhQL4IVLvM-zOCkfI_21OVz4IM", "Sheet1!A1:H11") }

Solution

  • You can FILTER according to if the headers match with your desired values. Note that the second IMPORTRANGE only involves the first row:

    =FILTER(importrange("1pnsqFsU9j_f2OrB8ojhQL4IVLvM-zOCkfI_21OVz4IM", "Sheet1!A1:H11"),IFNA(XMATCH(importrange("1pnsqFsU9j_f2OrB8ojhQL4IVLvM-zOCkfI_21OVz4IM", "Sheet1!A1:H1"),{"COL_NAME1","COL_NAME2","COL_NAME5"})))

    Or, the same with LET to use only one IMPORTRANGE:

    =LET(data,importrange("1pnsqFsU9j_f2OrB8ojhQL4IVLvM-zOCkfI_21OVz4IM", "Sheet1!A1:H11"),
FILTER(data,IFNA(XMATCH(INDEX(data,1),{"COL_NAME1","COL_NAME2","COL_NAME5"}))))