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

   Timesheet (Drive Sheet)
   Timesheet (Drive Sheet)  
   Timesheet (Drive Sheet)  
   Timesheet (Drive Sheet)
   Timesheet (Drive Sheet)  
   Timesheet (Drive Sheet)  
   Timesheet (Drive Sheet)  
   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.

  • 1
    Start by using IMPORTRANGE to get the external data into your "Timesheet Tracking sheet", then use arrays to put all imported data together in a single array. Jun 27, 2016 at 16:39

1 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") 
  • Thanks Bookend. Having a problem with importrange if you're online.
    – Steve
    Jun 25, 2016 at 4:16
  • Hmmm, it didn't prompt me for anything, it immediately presented #REF! as the cell value after confirming the formula.
    – Steve
    Jun 25, 2016 at 8:48
  • Great. Thanks. Not very user friendly, Google.
    – Steve
    Jun 25, 2016 at 9:48
  • I've reached up to Query data okay. When I enter that last query in a cell in the Summary sheet below the rest of the data rows, I receive Circular dependency detected.
    – Steve
    Jun 25, 2016 at 9:58
  • Let us continue this discussion in chat.
    – Steve
    Jun 25, 2016 at 10:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.