Skip to content

Instantly share code, notes, and snippets.

@woganmay
Created August 24, 2019 17:22
Show Gist options
  • Save woganmay/94b772994f4a80de252f26e701b20afc to your computer and use it in GitHub Desktop.
Save woganmay/94b772994f4a80de252f26e701b20afc to your computer and use it in GitHub Desktop.
Read Harvest timesheet data into a Google Sheet
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Integrations')
.addItem('Fetch Harvest', 'fetchHarvestData')
.addToUi();
}
function fetchHarvestData() {
// Update monthly, or read from another sheet
var start_date = '2019-08-01';
var end_date = '2019-08-31';
// Create a sheet called HARVEST_DATA if it doesn't exist
var data = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("HARVEST_DATA");
var strFrom = Utilities.formatDate(start_date, "Africa/Johannesburg", "yyyy-MM-dd");
var strTo = Utilities.formatDate(end_date, "Africa/Johannesburg", "yyyy-MM-dd");
var entries = UrlFetchApp.fetch("https://api.harvestapp.com/v2/time_entries?from="+strFrom+"&to="+strTo, {
headers: {
"Harvest-Account-ID": "[account id]",
"Authorization": "Bearer [full bearer token]",
"User-Agent": "[custom user agent]"
}});
var records = JSON.parse(entries);
// Overwrite everything
var startingRow = 1;
data.getRange(startingRow, 1, 1, 4).setValues([[
"ID",
"Date",
"Client",
"Hours"
]]);
startingRow++;
for(var i = 0; i < records.time_entries.length; i++)
{
var id = records.time_entries[i].id;
var date = records.time_entries[i].spent_date;
var client = records.time_entries[i].client.name;
var hours = records.time_entries[i].hours;
data.getRange(startingRow, 1, 1, 4).setValues([[
id,
date,
client,
hours
]]);
startingRow++;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment