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.
=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
