Search code examples
google-sheetsgoogle-sheets-formula

How can I add a column at the start of a Query/ImportRange function on Google Sheets?


I have a function on a Google Sheet that combines 3 different ImportRange tables from 3 different sheets, and queries them so that any missing data/empty lines are cut out. The problem I'm having is that I want to add a column at the start of the list that specifies which sheet each row originated from, but I'm not sure how to do this, as I am unable to edit each source sheet.

This is my function so far:

={QUERY(IMPORTRANGE("url1", "Education!A3:E30"),"SELECT Col1,Col2,Col3,Col4,Col5 where Col1 != ''");QUERY(IMPORTRANGE("url2", "Education!A3:E30"),"SELECT Col1,Col2,Col3,Col4,Col5 where Col1 != ''");QUERY(IMPORTRANGE("url3", "Education!A3:E30"),"SELECT Col1,Col2,Col3,Col4,Col5 where Col1 != ''")}

Solution

  • try:

    ={QUERY(IMPORTRANGE("url1", "Education!A3:E30"),
     "select 'source1',Col1,Col2,Col3,Col4,Col5 where Col1 != '' label 'source1'''");
      QUERY(IMPORTRANGE("url2", "Education!A3:E30"),
     "select 'source2',Col1,Col2,Col3,Col4,Col5 where Col1 != '' label 'source2'''");
      QUERY(IMPORTRANGE("url3", "Education!A3:E30"),
     "select 'source3',Col1,Col2,Col3,Col4,Col5 where Col1 != '' label 'source3'''")}