Float API to google app script export - created by Diego Castro

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

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