Pull Toggl entries through the Reports API, into an incremental Sheet tab.

auto-project-manager.gs

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);
}