Skip to content

Instantly share code, notes, and snippets.

@woganmay
Created September 7, 2018 09:40
Show Gist options
  • Save woganmay/fc2ce452fbb66a2e260ac05344dbaa53 to your computer and use it in GitHub Desktop.
Save woganmay/fc2ce452fbb66a2e260ac05344dbaa53 to your computer and use it in GitHub Desktop.
Pull Toggl entries through the Reports API, into an incremental Sheet tab.
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('My Business')
.addItem('Refresh Timesheets', 'getTimesheets')
.addToUi();
}
function getTimesheets() {
// Read API token and Workspace ID from Configuration tab
var report = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Timesheets");
var TogglApiToken = "your_api_token_here";
var TogglWorkspace = 123456;
var DefaultStartDate = "2018-04-01";
var TogglOptions = {
headers: {
"Authorization": "Basic " + Utilities.base64Encode(TogglApiToken+":api_token")
}
}
Logger.log("Connecting to Toggl Workspace ID " + TogglWorkspace);
// 1. Find the start date
var today = new Date();
var fetchFrom = findTimesheetStartDate(DefaultStartDate);
var fetchTo = findTimesheetStartDate(DefaultStartDate);
fetchTo.setDate(fetchTo.getDate()+5);
var keepDownloading = true;
while(keepDownloading)
{
// keepDownloading = false;
keepDownloading = fetchTo < today;
var strSince = Utilities.formatDate(fetchFrom, "SAST", "yyyy-MM-dd");
var strUntil = Utilities.formatDate(fetchTo, "SAST", "yyyy-MM-dd");
var timesheetReportUrl = "https://toggl.com/reports/api/v2/details?workspace_id="+TogglWorkspace+"&since="+strSince+"&until="+strUntil+"&user_agent=amberstone_sheet";
Logger.log("Pulling since "+strSince+" until " + strUntil);
var entries = UrlFetchApp.fetch(timesheetReportUrl, TogglOptions);
populateTimesheetEntries(report, entries);
fetchFrom.setDate(fetchFrom.getDate()+5);
fetchTo.setDate(fetchTo.getDate()+5);
}
}
function calculateBillingPeriod()
{
var report = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Timesheets");
var range = report.getRange("E1:E").getValues().filter(String);
Logger.log(range);
for(var i = 0; i < range.length; i++)
{
report.getRange(i+1, 8).setValue(range[i]);
}
}
function populateTimesheetEntries(report, entries)
{
var parsedEntries = JSON.parse(entries);
var emptyRow = findNextEmptyRow() + 1;
if (parsedEntries.data.length > 0) {
for(var i = 0; i < parsedEntries.data.length; i++) {
// If this ID already exists, don't write it
var timesheetId = parsedEntries.data[i].id;
var billable = parsedEntries.data[i].is_billable;
var exists = (billable) ? report.getRange("A1:A").getValues().filter(String).filter(function(tid){ return timesheetId == tid; }).length > 0 : false;
if (!exists) {
// Parse out the date
var stopDate = new Date(parsedEntries.data[i].end);
var pid = parsedEntries.data[i].pid;
var client = parsedEntries.data[i].client;
var project = parsedEntries.data[i].project;
if (pid == null) pid = "No Project ID";
if (client == null) client = "No Client";
if (project == null) project = "No Project";
var duration = parsedEntries.data[i].dur / 3600000;
report.getRange(emptyRow, 1, 1, 7).setValues([[
timesheetId,
pid,
client,
project,
Utilities.formatDate(stopDate, "SAST", "yyyy-MM-dd"),
duration,
parsedEntries.data[i].description]]);
emptyRow++;
}
}
}
}
/**
* Find the next empty row
*/
function findNextEmptyRow() {
var report = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Timesheets");
var existingTimestamps = report.getRange("A1:A").getValues();
return existingTimestamps.filter(String).length;
}
/**
* Figure out what the next empty row is
*/
function findTimesheetStartDate(DefaultStartDate) {
var report = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Timesheets");
var lastRow = findNextEmptyRow();
var dateValue = (lastRow == 1) ? DefaultStartDate : report.getRange(lastRow, 5).getValue();
return new Date(dateValue);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment