Skip to content

Instantly share code, notes, and snippets.

@dergachev

dergachev/main.gs

Last active May 14, 2020 19:22
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save dergachev/15bbfdd51cf9bb9ca508474708458107 to your computer and use it in GitHub Desktop.
Save dergachev/15bbfdd51cf9bb9ca508474708458107 to your computer and use it in GitHub Desktop.
Download ZIP
Float API to google app script export - created by Diego Castro
Raw
main.gs
// Compiled using ts2gas 3.6.1 (TypeScript 3.8.3)
const MAIN_MENU = 'Float Reports';
const MENU_GENERATE = '📗 Generate';
const MENU_CLEAR = '🗑️ Clear report';
/**
* Makes a menu in the Google SpreadSheet
*/
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu(MAIN_MENU)
.addItem(MENU_GENERATE, 'generateReport')
.addSeparator()
.addItem(MENU_CLEAR, 'clearReport')
.addToUi();
}
/**
* Application constants
*/
const REPORT_SHEET_NAME = 'Report';
const FLOAT_URL = 'https://api.float.com/v3';
const FLOAT_TEST_API_KEY = '26a7ad64e6c8f5faH6eemiCEUVuC9ow4tz3J6yKdm7eldcTtMaTtiNIwNG0=';
const FLOAT_URL_PEOPLE = `${FLOAT_URL}/people`;
const FLOAT_URL_TASKS = `${FLOAT_URL}/tasks`;
const FLOAT_URL_PROJECTS = `${FLOAT_URL}/projects`;
const REPORT_RANGE = {
startColumn: 'D',
endColumn: 'I',
startRow: 3
};
/**
* Default configuration for requests to Float API
*/
const REQUEST_OPTIONS = {
method: 'get',
headers: {
'Authorization': `Bearer ${FLOAT_TEST_API_KEY}`,
'Content-Type': 'application/json',
}
};
/**
* Get the current SpreadSheet
*
* @returns GoogleAppsScript.Spreadsheet.Sheet
*/
function getCurrentSheet() {
const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
return spreadSheet && spreadSheet.getSheetByName(REPORT_SHEET_NAME);
}
/**
* Get the form data. Includes startDate, endDate and apiKey
*
* @see filters object
* @returns filter object
*/
function getFilters() {
const currentSheet = getCurrentSheet();
const date2String = (string) => {
const date = new Date(string);
if (date) {
const month = date.getMonth() + 1;
const day = date.getDate();
const monthString = month < 10 ? `0${month}` : month;
const dayString = day < 10 ? `0${day}` : day;
return `${date.getFullYear()}-${monthString}-${dayString}`;
}
return '';
};
const filters = {
startDate: { range: 'B2', value: '', },
endDate: { range: 'B3', value: '', },
status: { range: 'B4', value: '', setStatus: null },
apiKey: { range: 'B5', value: '', },
toStringParams: function () {
let stringParams = '';
if (this.startDate.value) {
stringParams += `start_date=${date2String(this.startDate.value)}&`;
}
if (this.endDate.value) {
stringParams += `end_date=${date2String(this.endDate.value)}&`;
}
return stringParams;
}
};
Object.keys(filters).forEach(filterKey => {
const filter = filters[filterKey];
if (!filter.range)
return;
const range = currentSheet.getRange(filter.range);
filter.value = range.getValue();
});
filters.status.setStatus = (newStatus) => {
currentSheet.getRange(filters.status.range).setValue(newStatus);
};
const noDates = !filters.startDate.value || !filters.endDate.value;
// const sameDates = date2String(filters.startDate.value) === date2String(filters.endDate.value);
const startDate = new Date(filters.startDate.value).getTime();
const endDate = new Date(filters.endDate.value).getTime();
const dateError = endDate < startDate;
if (noDates || /* sameDates || */ dateError) {
return false;
}
return filters;
}
/**
* Get tasks from the Float API
*
* @param filters string current date filter
*/
function getTasks(filters) {
const taskUrl = `${FLOAT_URL_TASKS}?per-page=200&fields=repeat_end_date,repeat_state,task_id,start_date,end_date,people_id,name,notes,project_id,hours&${filters}`;
const response = UrlFetchApp.fetch(taskUrl, REQUEST_OPTIONS);
const responseText = response.getContentText();
return (responseText && JSON.parse(responseText)) || [];
}
/**
* Utility to load data from Float API and map the loaded data
*
* @param floatURL string Float API url
* @param ids number[] Ids to be loaded
* @param fields string Fields to be loaded
* @param key string Key to map the data into an object
*/
function fetchData(floatURL, ids, fields, key) {
const filtered = Array.from(new Set(ids));
const requests = [];
filtered.forEach(id => {
const url = `${floatURL}/${id}?fields=${fields}`;
const requestOptions = {
...REQUEST_OPTIONS,
url,
};
requests.push(requestOptions);
});
const response = UrlFetchApp.fetchAll(requests);
const data = {};
response.forEach(response => {
const text = response.getContentText();
const json = text && JSON.parse(text);
const keyValue = json[key];
data[keyValue] = json.name;
});
return data;
}
/**
* Get the name for some people_ids from Float API
*
* @param ids number[] Array of people ids
* @returns obeject Name and people_id for each people_id
*/
function getPeople(ids) {
return fetchData(FLOAT_URL_PEOPLE, ids, 'name,people_id', 'people_id');
}
/**
* Get the name for some projects_ids from Float API
*
* @param ids number[] Array of projects ids
* @returns obeject Name and projects_id for each projects_id
*/
function getProjects(ids) {
return fetchData(FLOAT_URL_PROJECTS, ids, 'name,project_id', 'project_id');
}
/**
* Clean the report section in the Report's sheet
*/
function clearReport() {
const currentSheet = getCurrentSheet();
const allReportRangeString = `${REPORT_RANGE.startColumn}${REPORT_RANGE.startRow}:${REPORT_RANGE.endColumn}`;
const allRange = currentSheet.getRange(allReportRangeString);
allRange.clearContent();
}
/**
* Refresh the default API Key with the API Key in the spreadsheet
*
* @param apiKey string New API Key
* @returns boolean True or false if the user entered an API key
*/
function updateApiKey(apiKey) {
if (!apiKey) {
return false;
}
REQUEST_OPTIONS.headers.Authorization = `Bearer ${apiKey}`;
return true;
}
// function
/**
* Loads information from Float API and put it into the spreadsheet
*/
function generateReport() {
const filters = getFilters();
if (!filters) {
Browser.msgBox('Please verify the start and the end date in the form filters');
return;
}
const stringParams = filters.toStringParams();
const updated = updateApiKey(filters.apiKey.value);
if (updated) {
filters.status.setStatus('Loading tasks...');
const tasks = getTasks(stringParams);
if (tasks && tasks.length) {
const peopleIds = tasks.map(task => task && task.people_id);
filters.status.setStatus('Loading People...');
const people = getPeople(peopleIds);
const projectsIds = tasks.map(task => task && task.project_id);
filters.status.setStatus('Loading projects...');
const projects = getProjects(projectsIds);
filters.status.setStatus('Drawing report...');
const repeatingTasks = [];
const values = [];
tasks.forEach(task => {
const user = people[task.people_id];
const project = projects[task.project_id];
const data = [
task.start_date,
user,
task.name,
task.notes,
task.hours,
project
];
const startDate = task.start_date;
const endDate = task.end_date;
values.push(data);
if (startDate !== endDate) {
const startDate2 = new Date(startDate);
const offset = startDate2.getTimezoneOffset();
const offsetTime = offset * 60 * 1000;
const startDateTime = startDate2.getTime();
const endDateTime = new Date(endDate).getTime();
if (!task.repeat_end_date) {
const diff = endDateTime - startDateTime;
const howManyDays = Math.ceil(diff / 1000 / 3600 / 24);
for (let i = 0; i < howManyDays; i++) {
const dayTime = (3600 * 1000 * 24) * (i + 1);
const offsetTime = offset * 60 * 1000;
const newStartDate = new Date(startDateTime + dayTime + offsetTime);
const newDateString = `${newStartDate.getFullYear()}-${newStartDate.getMonth() + 1}-${newStartDate.getDate()}`;
const newData = [...data];
newData[0] = newDateString;
values.push(newData);
}
}
else {
const repeatEndDateUTC = new Date(task.repeat_end_date);
const repeatEndDate = new Date(repeatEndDateUTC.getTime() + offsetTime);
const repeatEndDateTime = repeatEndDate.getTime();
const diff = endDateTime - startDateTime;
let index = 0;
let howManyDays = Math.ceil(diff / 1000 / 3600 / 24);
const repeatModeDays = 7;
let currentDate = new Date(startDate2.getTime() + offsetTime);
while (currentDate.getTime() <= repeatEndDateTime) {
for (let i = 0; i < howManyDays; i++) {
const dayTime = (3600 * 1000 * 24) * (i + (!index ? 1 : 0));
const newStartDate = new Date(currentDate.getTime() + dayTime);
const newDateString = `${newStartDate.getFullYear()}-${newStartDate.getMonth() + 1}-${newStartDate.getDate()}`;
const newData = [...data];
newData[0] = newDateString;
values.push(newData);
}
index++;
howManyDays += 1;
currentDate.setDate(currentDate.getDate() + repeatModeDays);
}
}
}
});
const endRow = REPORT_RANGE.startRow + values.length - 1;
const allReportRangeString = `${REPORT_RANGE.startColumn}${REPORT_RANGE.startRow}:${REPORT_RANGE.endColumn}`;
const reportRangeString = `${allReportRangeString}${endRow}`;
const currentSheet = getCurrentSheet();
clearReport();
const reportRange = currentSheet.getRange(reportRangeString);
reportRange.setValues(values);
}
filters.status.setStatus('');
}
else {
Browser.msgBox('No API Key provided, please type the ApiKey');
}
}
Raw
main.ts
//NOTE: This typescript file needs to be compiled with clasp, see https://github.com/google/clasp/blob/master/docs/typescript.md
const MAIN_MENU = 'Float Reports';
const MENU_GENERATE = '📗 Generate';
const MENU_CLEAR = '🗑️ Clear report';
/**
* Makes a menu in the Google SpreadSheet
*/
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu(MAIN_MENU)
.addItem(MENU_GENERATE, 'generateReport')
.addSeparator()
.addItem(MENU_CLEAR, 'clearReport')
.addToUi();
}
/**
* Application constants
*/
const REPORT_SHEET_NAME = 'Report';
const FLOAT_URL = 'https://api.float.com/v3';
const FLOAT_TEST_API_KEY ='26a7ad64e6c8f5faH6eemiCEUVuC9ow4tz3J6yKdm7eldcTtMaTtiNIwNG0=';
const FLOAT_URL_PEOPLE = `${FLOAT_URL}/people`;
const FLOAT_URL_TASKS = `${FLOAT_URL}/tasks`;
const FLOAT_URL_PROJECTS = `${FLOAT_URL}/projects`;
const REPORT_RANGE = {
startColumn: 'D',
endColumn: 'I',
startRow: 3
};
/**
* Default configuration for requests to Float API
*/
const REQUEST_OPTIONS: GoogleAppsScript.URL_Fetch.URLFetchRequestOptions = {
method: 'get',
headers: {
'Authorization': `Bearer ${FLOAT_TEST_API_KEY}`,
'Content-Type': 'application/json',
}
};
/**
* Get the current SpreadSheet
*
* @returns GoogleAppsScript.Spreadsheet.Sheet
*/
function getCurrentSheet (): GoogleAppsScript.Spreadsheet.Sheet {
const spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
return spreadSheet && spreadSheet.getSheetByName(REPORT_SHEET_NAME);
}
/**
* Get the form data. Includes startDate, endDate and apiKey
*
* @see filters object
* @returns filter object
*/
function getFilters (): any {
const currentSheet = getCurrentSheet();
const date2String = (string): string => {
const date = new Date(string);
if(date) {
const month = date.getMonth() + 1;
const day = date.getDate();
const monthString = month < 10 ? `0${month}` : month;
const dayString = day < 10 ? `0${day}` : day;
return `${date.getFullYear()}-${monthString}-${dayString}`;
}
return '';
};
const filters = {
startDate: { range: 'B2', value : '', },
endDate: { range: 'B3', value : '', },
status: { range: 'B4', value : '', setStatus: null },
apiKey: { range: 'B5', value : '', },
toStringParams: function (): string {
let stringParams = '';
if (this.startDate.value) {
stringParams += `start_date=${date2String(this.startDate.value)}&`;
}
if (this.endDate.value) {
stringParams += `end_date=${date2String(this.endDate.value)}&`;
}
return stringParams;
}
};
Object.keys(filters).forEach(filterKey => {
const filter = filters[filterKey];
if (!filter.range) return;
const range = currentSheet.getRange(filter.range);
filter.value = range.getValue();
});
filters.status.setStatus = (newStatus: string): void => {
currentSheet.getRange(filters.status.range).setValue(newStatus);
};
const noDates = !filters.startDate.value || !filters.endDate.value;
// const sameDates = date2String(filters.startDate.value) === date2String(filters.endDate.value);
const startDate = new Date(filters.startDate.value).getTime();
const endDate = new Date(filters.endDate.value).getTime();
const dateError = endDate < startDate;
if(noDates || /* sameDates || */ dateError) {
return false;
}
return filters;
}
/**
* Get tasks from the Float API
*
* @param filters string current date filter
*/
function getTasks (filters: string): any {
const taskUrl = `${FLOAT_URL_TASKS}?per-page=200&fields=repeat_end_date,repeat_state,task_id,start_date,end_date,people_id,name,notes,project_id,hours&${filters}`;
const response = UrlFetchApp.fetch(taskUrl, REQUEST_OPTIONS);
const responseText = response.getContentText();
return (responseText && JSON.parse(responseText)) || [];
}
/**
* Utility to load data from Float API and map the loaded data
*
* @param floatURL string Float API url
* @param ids number[] Ids to be loaded
* @param fields string Fields to be loaded
* @param key string Key to map the data into an object
*/
function fetchData (floatURL, ids, fields, key) {
const filtered = Array.from(new Set(ids));
const requests = [];
filtered.forEach(id => {
const url = `${floatURL}/${id}?fields=${fields}`;
const requestOptions = {
...REQUEST_OPTIONS,
url,
};
requests.push(requestOptions);
});
const response = UrlFetchApp.fetchAll(requests);
const data = {};
response.forEach(response => {
const text = response.getContentText();
const json = text && JSON.parse(text);
const keyValue = json[key];
data[keyValue] = json.name;
});
return data;
}
/**
* Get the name for some people_ids from Float API
*
* @param ids number[] Array of people ids
* @returns obeject Name and people_id for each people_id
*/
function getPeople (ids: number[]) {
return fetchData(FLOAT_URL_PEOPLE, ids, 'name,people_id', 'people_id');
}
/**
* Get the name for some projects_ids from Float API
*
* @param ids number[] Array of projects ids
* @returns obeject Name and projects_id for each projects_id
*/
function getProjects (ids: number[]) {
return fetchData(FLOAT_URL_PROJECTS, ids, 'name,project_id', 'project_id');
}
/**
* Clean the report section in the Report's sheet
*/
function clearReport () {
const currentSheet = getCurrentSheet();
const allReportRangeString = `${REPORT_RANGE.startColumn}${REPORT_RANGE.startRow}:${REPORT_RANGE.endColumn}`
const allRange = currentSheet.getRange(allReportRangeString);
allRange.clearContent();
}
/**
* Refresh the default API Key with the API Key in the spreadsheet
*
* @param apiKey string New API Key
* @returns boolean True or false if the user entered an API key
*/
function updateApiKey (apiKey: string): boolean {
if (!apiKey) {
return false;
}
REQUEST_OPTIONS.headers.Authorization = `Bearer ${apiKey}`;
return true;
}
// function
/**
* Loads information from Float API and put it into the spreadsheet
*/
function generateReport () {
const filters = getFilters();
if (!filters) {
Browser.msgBox('Please verify the start and the end date in the form filters');
return;
}
const stringParams = filters.toStringParams();
const updated = updateApiKey(filters.apiKey.value);
if(updated) {
filters.status.setStatus('Loading tasks...');
const tasks = getTasks(stringParams);
if (tasks && tasks.length) {
const peopleIds = tasks.map(task => task && task.people_id);
filters.status.setStatus('Loading People...');
const people = getPeople(peopleIds);
const projectsIds = tasks.map(task => task && task.project_id);
filters.status.setStatus('Loading projects...');
const projects = getProjects(projectsIds);
filters.status.setStatus('Drawing report...');
const repeatingTasks = [];
const values = [];
tasks.forEach(task => {
const user = people[task.people_id];
const project = projects[task.project_id];
const data = [
task.start_date,
user,
task.name,
task.notes,
task.hours,
project
];
const startDate = task.start_date;
const endDate = task.end_date;
values.push(data);
if (startDate !== endDate) {
const startDate2 = new Date(startDate);
const offset = startDate2.getTimezoneOffset();
const offsetTime = offset * 60 * 1000;
const startDateTime = startDate2.getTime();
const endDateTime = new Date(endDate).getTime();
if(!task.repeat_end_date) {
const diff = endDateTime - startDateTime;
const howManyDays = Math.ceil(diff / 1000 / 3600 / 24);
for (let i = 0;i < howManyDays; i++) {
const dayTime = (3600 * 1000 * 24) * (i + 1);
const offsetTime = offset * 60 * 1000;
const newStartDate = new Date(startDateTime + dayTime + offsetTime);
const newDateString = `${newStartDate.getFullYear()}-${newStartDate.getMonth() + 1}-${newStartDate.getDate()}`;
const newData = [...data];
newData[0] = newDateString;
values.push(newData);
}
} else {
const repeatEndDateUTC = new Date(task.repeat_end_date);
const repeatEndDate = new Date(repeatEndDateUTC.getTime() + offsetTime);
const repeatEndDateTime = repeatEndDate.getTime();
const diff = endDateTime - startDateTime;
let index = 0;
let howManyDays = Math.ceil(diff / 1000 / 3600 / 24);
const repeatModeDays = 7;
let currentDate = new Date(startDate2.getTime() + offsetTime);
while(currentDate.getTime() <= repeatEndDateTime) {
for (let i = 0; i < howManyDays; i++) {
const dayTime = (3600 * 1000 * 24) * (i + (!index ? 1 :0) );
const newStartDate = new Date(currentDate.getTime() + dayTime);
const newDateString = `${newStartDate.getFullYear()}-${newStartDate.getMonth() + 1}-${newStartDate.getDate()}`;
const newData = [...data];
newData[0] = newDateString;
values.push(newData);
}
index++;
howManyDays += 1;
currentDate.setDate(currentDate.getDate() + repeatModeDays);
}
}
}
});
const endRow = REPORT_RANGE.startRow + values.length - 1;
const allReportRangeString = `${REPORT_RANGE.startColumn}${REPORT_RANGE.startRow}:${REPORT_RANGE.endColumn}`
const reportRangeString = `${allReportRangeString}${endRow}`;
const currentSheet = getCurrentSheet();
clearReport();
const reportRange = currentSheet.getRange(reportRangeString);
reportRange.setValues(values);
}
filters.status.setStatus('');
} else {
Browser.msgBox('No API Key provided, please type the ApiKey');
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment