Combine data from multiple google sheets and get tab name
Combining data from multiple Google Sheets be achieved through the script but you can also hard code with QUERY and IMPORTRANGE function in Google Sheets and assign the sheet/tab name within each formula set to get the sheet/tab name along with the data set. As a result, you will one extra column as an assigned tab (Column J in the below sheets example). And also excluded the headers to make the formula simpler.
Combine data from multiple Google Sheets tabs and get sheet/tab name
How to merge several Google sheets tabs into one and get the tab name?COMBINE DATA FROM MULTIPLE GOOGLE SHEETS TABS — GOOGLE SHEETS FORMULA AND EXAMPLE
=ARRAYFORMULA(QUERY({{IMPORTRANGE("https://docs.google.com/spreadsheets/d/12udQU06rlX7FKPguMHeKf-h6A4Y9dhpS0lqCaD4QcbY/edit","Facebook_Ads!A2:I100"),IMPORTRANGE("https://docs.google.com/spreadsheets/d/12udQU06rlX7FKPguMHeKf-h6A4Y9dhpS0lqCaD4QcbY/edit","Facebook_Ads!Z2:Z100")&"Facebook_Ads"};{IMPORTRANGE("https://docs.google.com/spreadsheets/d/12udQU06rlX7FKPguMHeKf-h6A4Y9dhpS0lqCaD4QcbY/edit","Google_Ads!A2:I100"),IMPORTRANGE("https://docs.google.com/spreadsheets/d/12udQU06rlX7FKPguMHeKf-h6A4Y9dhpS0lqCaD4QcbY/edit","Google_Ads!Z2:Z100")&"Google_Ads"};{IMPORTRANGE("https://docs.google.com/spreadsheets/d/12udQU06rlX7FKPguMHeKf-h6A4Y9dhpS0lqCaD4QcbY/edit","Bing_Ads!A2:I100"),IMPORTRANGE("https://docs.google.com/spreadsheets/d/12udQU06rlX7FKPguMHeKf-h6A4Y9dhpS0lqCaD4QcbY/edit","Bing_Ads!Z2:Z100")&"Bing_Ads"}},"SELECT * WHERE Col1 IS NOT NULL"))
Colour: data range to combine
Colour: empty referring columns
Colour: Tab Name or Label // you can change it to your desired label
Notes:
IMPORTRANGE will return the #REF error using first time in each new spreadsheet and will ask for access to it. Click the cell with the error and press that blue Allow access prompt. Once permission is granted, you can import data from other sheets of that file.
If you have big data set may take a few minutes to pull the data from all sheets
Each formula set should be separated by a semicolon (;)
All the IMPORTRANGE function should be wrapped within curly brackets { }
"SELECT * WHERE Col1 IS NOT NULL" = will get the non-empty columns from each tab
The number of columns (A2:I100) in all formulas must be the same
Common Error: In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.
Empty Referring columns (Z2:Z100) should be available in all data Sheets (Ex, Facebook_Ads)
Still getting an error, copy the formula and test result in a new tab