Link TimeTrex to Google Sheets

Frequently Asked Questions
Locked
mikeb
Posts: 391
Joined: Thu Jul 27, 2006 11:58 am

Link TimeTrex to Google Sheets

Post by mikeb » Fri Mar 16, 2018 9:54 am

TimeTrex Professional, Corporate or Enterprise Editions can be a data source for Crystal Reports, Jasper Reports and other applications like Google Sheets.

Here is an example of how to retrieve reports from TimeTrex in Google Sheets.
  1. Login to TimeTrex and create a saved report containing the data that you wish to view in Google Sheets. For simplicity sake the report name should be a single word without any special characters or spaces, ie:

    Code: Select all

    MyReportForGoogleSheets
  2. Because Google Sheets doesn't support loading data from secure authenticated sources out-of-the-box, you will need to add a custom script that supports this functionality. Open ImportJSON custom script in your browser, then select all and copy its entire contents to your clip board.

    *IMPORTANT* DO NOT share this spreadsheet or allow anyone else access to view the code or modify it, as they will be able to see your password and use it to login to your TimeTrex account.
  3. Open Google Sheets and create a new sheet.
  4. Go to Tools -> Script Editor, clear out any default code that you may see then paste the custom script copied from step #2.
  5. Click File -> Save, enter a new project name if asked.
  6. Go back to your new spreadsheet and click on cell A1, then enter the following line. Be sure to replace the variables like <URL to TimeTrex>, <username>, <password> and <saved report name> with your specific information.

    Code: Select all

    =ImportJSONBasicAuth("<URL to TimeTrex>/api/report/api.php?format=json&name=<saved report name>", "<username>", "<password>")
    For example:

    Code: Select all

    =ImportJSONBasicAuth("https://demo.timetrex.com/api/report/api.php?format=json&name=MyReportForGoogleSheets", "demoadmin1", "demo")
    
    The data from the report should then appear on the screen.

Locked