Database lookup timesheet to Excel

Question

Hi,

I'm running some time reports in projects where I manually get weekly time reports from Harvest time sheet per project and add them in a specific Excel-report I need to send to our customer.

I'm curious if it is possible to extract data from Timesheet based on some variables so I can automate the process.

My sheet has the task names added, and next to it there is a cell where I want the current total time for each task within a certain time frame.

I dunno if this is possible, but I would like to see any examples where it might be used.  I have date range in my sheet, and can add the project number for reference - but I need to know if I'm able to look up the needed data based on my Project number, task name and date range.

Cheers,

Martin

Aron Gombas _Midori_ · Answer

OK, let me be more specific.

As I wrote before, you can use specially-prepared Excel files as export templates, and those can execute code written in the Groovy language. Therefore you can:

* Build the Excel template that produces the final output you look for. The issues can be fed to issue by using a saved filter. Follow this documentation page.
* Write a Groovy script that is executed while rendering the final XLSX. See this tutorial. The script should connect to the Harvest REST API (probably to the time entries end-point ) and get the time from that. Among the scripts shipped with the app, you can find several examples for REST API calls, you can start working from those.
* At this point, you already the desired output but the report needs to be ran manually. (We are working on a new app that will be able to run this automatically using a pre-defined schedule.)

I hope this helps. If it is too technical, then I can recommend you a partner of ours who is experienced with this type of customization.

Martin Bergholtz · Answer

Hi and thank you for answering my question.  I've added the trial, however I do not see how this might do what what I'm after.

I have an existing excel sheet, and directly in that that sheet I would like to do a "lookup" directly to Harvest on the task name in my report to see how many hours has been logged for that task within a certain time frame.l  Thus getting the info I need to update my reports I weekly report to my customer.

I think I'm mixing up the internal apps here as I thought Harvest was part of Jira from Atlassian but it seems I'm mixing up the systems here, meaning I can't get the needed info throiugh Atlassian.  Sorry for that.

Aron Gombas _Midori_ · Answer

@Martin Bergholtz Because your target format is Excel, I suggest you that you try if you can create the Excel report with the Better Excel Exporter app!

This app is designed for ultimate customization, you can use an Excel file as template, can run custom code,  use all the Excel features from pivot charts to VBA macros. It is relatively safe that you will be able to solve the problem in a way which final requires only a single click to get the required Excel report.

Then, you can take the next step and make it 100% automatic. Using the Better Excel Automation app, which is a companion of the exporter app, you can schedule re-creating the report and emailing it! And then you can forget about it. (Or send a BCC to yourself.)

Note that this second app is being developed for the cloud right now, but it has been popular on Server/Data Center for several years.

Here is a timesheet pivot table report created with one click using the app, as an example:

(Discl. these apps are supported by our team. The exporter is paid, while the automation is free.)