Tally duration, client, client category in each Google sheets Timesheet

Question

I use Google Sheets for my time sheets, using the following folder structure:

Timesheets
 Corporate
  CorpClient1
   Timesheet (Drive Sheet)
  CorpClient2
   Timesheet (Drive Sheet)  
 Freelance
  FreelanceClient1
   Timesheet (Drive Sheet)  
  FreelanceClient2
   Timesheet (Drive Sheet)
 Projects
  ProjectName1
   Timesheet (Drive Sheet)  
  ProjectName2
   Timesheet (Drive Sheet)  
 Volunteer
  VolunteerOrg1
   Timesheet (Drive Sheet)  
  VolunteerOrg2
   Timesheet (Drive Sheet)

In each Timesheet, I have data in the following columns: Date, Start Time, End Time, Duration, Task

I'd like to create a sheet under Timesheets (call it Timesheet Tracking) with the following columns: Date, Hours, Client, Category

Where Category is one of: Corporate, Freelance, Projects, Volunteer.

Somehow, I need the Timesheet Tracking sheet to do something like:

• take the date in cell A2 of Timesheet Tracking, search for this date in each time sheet in turn, and for each time sheet it finds a matching date, total the duration entries that match that date, then enter this total in cell B2, enter the Client name in cell C2, and the category in cell D2.
• it needs to add a separate line for each date/duration/client/category result

I don't know where to start.

Answer 1

I'll outline the strategy in three steps. Some of them may be combined, but doing things step by step makes the process more manageable.

Import ranges

Start by pulling the data from assorted spreadsheets into one summary spreadsheet. For example, create a separate sheet/tab for each source, enter =importrange(..., "A:E") in A1, and something like

=arrayformula(if(A:A <> "", {"This_Client", "This_Category"}, ))

to mark the client and category for the source.

Combine data

In a summary sheet, enter

={filter(Sheet1!A:G, len(Sheet1!A:A)); filter(Sheet2!A:G, len(Sheet2!A:A)) } 

to combine all the sources, excluding empty rows.

Query data

Use a query to summarize data and present selected columns.

=query(Summary!A:G, "select A, sum(B), F, G group by A, F, G")