This Add-In is used to produce a timesheet file with functionality to import your Google Timeline. The standard timesheet has options for start and end dates, day of week and default start, end and break times. The Google timeline options are start and end dates, UTC selection, daylight savings time parameters and title filter for timeline entries. There is also a right click shortcut to open your Google timeline to the date of the row that is currently selected. I used Terence Eden's blog post as a reference to write the procedure for downloading the Google Timeline files. Code review suggestions have been applied from this article.

Table of Contents

• Install
• Dependencies
• Glossary of Terms
• Functionality
  • Insert Data
  • Date Options
  • Day Options
  • Time Options
  • Help
  • Admin
  • About

Install

Instructions for installation of VBA version

VBA

How to install the VBA version

1. Download the VBA Add-In file .

Dependencies

Glossary of Terms

Functionality

This Excel ribbon is inserted after the “Home” tab when Excel opens. Listed below is the detailed functionality of this application and its components.

Insert Data (Group)

Insert rows of data into the active table with the following controls.

Add Days (Button)

• Add days from the textbox values in the start and end dates in the "Date Options" group.
• Optional filter for day of week in "Day Options" group.

Add Timeline (Button)

• Add timeline events filter from the start and end dates in the "Date Options" group.
• Downloads the .kml files per day from Google Timeline
• Imports each .kml file with optional filters for Title (using a delimited list in the settings) and day of week in "Day Options" group.

UTC (Dropdown)

• This is the timezone offset for the stored date & time values in your Google Timeline.
• Uses a hidden sheet for the list of Timezones

Apply DST (Toggle Button)

• This will apply the daylight savings time to the Google Timeline import.

Filter Title (Toggle Button)

• This will apply the title filter from the settings to the Google Timeline import.

Date Options (Group)

These date options will filter the selected table.

Start Date (Textbox)

• The value that is used to filter inserts.

End Date (Textbox)

• The value that is used to filter inserts.

Column (Combobox)

• This refers to the column name that date values get added to.

Calendar (Buttons)

• These buttons allow the user to select a date from a calendar.
• Note: The calendar has a reference to Common Controls

Refresh (Button)

• This button will refresh the list of column names in the combobox.

Current Week (Button)

• This button will set the start and end dates in the textboxes to the current week.

Next 7 Days (Button)

• This button will set the start and end dates in the textboxes to the next 7 days using the maximum date from the selected table.

Filter Table (Button)

• This button will filter the selected table with the start and end dates from the textboxes.

Day Options (Group)

Sun, Mon, Tue, Wed, Thu, Fri, Sat (Checkboxes)

• These checkboxes allow the user to limit the records inserted into the table by selected days

Time Options (Group)

Start Time (Combobox)

• The column name of the start time

End Time (Combobox)

• The column name of the end time

Break Time (Combobox)

• The column name of the break time

Refresh (Buttons)

• Refreshes the list of column name from the selected table

Start Time (Textbox)

• The default value for the start time when inserted into the selected table

End Time (Textbox)

• The default value for the end time when inserted into the selected table

Break Time (Textbox)

• The default value for the break time when inserted into the selected table

Help (Group)

How To… (Button)

• Opens a webpage of the read me documentation

Report Issue (Button)

• Opens a page to create a new issue for the product

Settings (Button)

• Settings represent user preferences or valuable information the application needs to use.
• This opens the settings form. The settings are stored as custom properties and can be changed by double-clicking the item or using the update button.

VBA

• VBA Settings
  • To add a new setting

    ThisWorkbook.CustomDocumentProperties.Add _
    Name:="App_ReleaseDate" _
    , LinkToContent:=False _
    , Type:=msoPropertyTypeDate _
    , Value:="31-Jul-2017 1:05pm"

  • To update a setting

    ThisWorkbook.CustomDocumentProperties.Item("App_ReleaseDate").Value = "31-Jul-2017 1:05pm"

  • To delete a setting

    ThisWorkbook.CustomDocumentProperties.Item("App_ReleaseDate").Delete

Admin (Group)

This group's visibility is toggled with the F12 key

View Errors (Toggle Button)

• View a list of errors in a hidden sheet

Visual Basic (Button)

• Opens the Visual Basic editor

Export Code (Button)

• Export the VBA and XML code for this file to the current directory

About (Group)

Add-in Name (Label)

• The application name with the version

Release Date (Label)

• The release date of the application

Copyright (Label)

• The author’s name