Return the Sheet Name on a Query Formula

Question

My company got the timesheet of all employees in a google sheet where each employee registers their time. Each employee has his separated sheet with the same columns (Date, Time, Project and Notes) and the name of the sheet is the name of the employee.

I did a query function of all sheet to return all hours register to a project in a time period and the query returns all hours (lines) reported by the employees.

=Query({employee1!A2:F;employee2!A2:F;employee3!A2:F;employee4!A2:F};"select * where Col2 Matches '"&I1&"' and Col1>=date '" & Text (L1;"yyyy-mm-dd")&"'and Col1<=date '" & Text (M1;"yyyy-mm-dd")&"'")

But I need to also know the employee that register that time that is the sheet name. So, my question: how I add the sheet name in the query, so it returns the name of the employee that reported the time on that project? For each row I must add the employee.

Here is an example: https://docs.google.com/spreadsheets/d/1rgA1aygOhaLQ7WP8wUjr5YCEENDQqJpFR3cSXhLSvJw/edit?usp=sharing

Thanks in advance for your time and be safe.

please edit to show what you have so far, to enable readers to suggest ways to integrate the new feature with the existing query. — underscore_d
I think it's quite difficult to grab a sheet name without using scripts. What I suggest is rather add employee name to each dataset. In form of new column. — Krzysztof Dołęgowski
Thank you for the reply's. Yes, that solution will work but first I Want to try to find a way that prevents that. About the use of scripts, I can use those but I'm know alot of scripts. I can edit some but doing from scrapts is hard to me. — Hugo Cristina
Why is using script not an option? You could get the sheet name with this method getSheetName() and then use it as a custom function. Also, could you share a sheet (avoid providing personal data) to reproduce the case properly? — Kessy
Thank you. I know using formulas but using script is a bit hard to me understand. I put a google sheet example. — Hugo Cristina

player0 player0 · Accepted Answer · 2021-01-27T11:13:31

try:

=ARRAYFORMULA(QUERY({
 {Employee1!A2:F, "employee1"&Employee1!Z2:Z}; 
 {Employee2!A2:F, "employee2"&Employee2!Z2:Z};
 {Employee3!A2:F, "employee3"&Employee3!Z2:Z};
 {Employee4!A2:F, "employee4"&Employee4!Z2:Z}},
 "where Col2 matches '"&I1&"' 
    and Col1 >= date '"&TEXT(L1, "yyyy-mm-dd")&"'
    and Col1 <= date '"&TEXT(M1, "yyyy-mm-dd")&"'"))

Return the Sheet Name on a Query Formula

1 Answers