google sheets appscript to retrieve invoices from fattureincloud and times from toggl

google_sheet_invoices_and_times_appscript.js

/*
 references
 
 fattureincloud
 https://developers.fattureincloud.it/api-reference/#get-/c/-company_id-/issued_documents
 
 toggl
 https://developers.track.toggl.com/docs/reports/detailed_reports

*/

// set google sheets custom menu items
function onOpen() {
    var ui = SpreadsheetApp.getUi();
    ui.createMenu('Utils')
        .addItem('Get Invoices', 'getInvoices')
        .addItem('Get Times', 'getTimes')
        .addToUi();
}


// count working days in a specific timeframe
function workingDaysCount(start_date="2023-01-01", end_date="2023-12-31") {
    const properties = PropertiesService.getScriptProperties();
    const api_key = properties.getProperty('workingdays_api_key');
    var response = UrlFetchApp.fetch(`https://api.workingdays.org/1.3/analyse?key=${api_key}&country_code=CA&start_date=${start_date}&end_date=${end_date}`);
    var json = JSON.parse(response.getContentText());
    console.log(json);
    return json.working_days.total;
}


// helper function to add multiple rows to a sheet in one shot
function addRowsToSheet(sheet, rows) {
  var row = rows.length;
  var column = rows[0].length;
  sheet.getRange(sheet.getLastRow()+1, 1, row, column).setValues(rows);
}


// get invoices from FattureInCloud, an italian invoicing platform
function getInvoices() {
    const properties = PropertiesService.getScriptProperties();
    var sheet = SpreadsheetApp.getActiveSheet();
    sheet.clear();
    sheet.appendRow(
        [
            "number",
            "date",
            "customer",
            "amount_net",
            "description",
            "qty",
            "net_price",
            "item_total",
        ]
    );

    var url = "https://api-v2.fattureincloud.it";
    var endpoint = "/c/36/issued_documents";
    var headers = {
        "Content-Type": "application/json",
        "Authorization": `Bearer ${properties.getProperty('fattureincloud_access_token')}`
    };

    var options = {
        "method": "get",
        "headers": headers, 
    };

    var queryParams = [
        "type=" + "invoice",
        "per_page="+ 100,
        "fieldset="+ "detailed",
        "sort=-number,-date",
        "q="+encodeURIComponent("date>'2023-01-01'")
    ];
    var pageKey = "page";
    var pageNum = 1;
    var nextPageUrl;
    var data = {},
        output = [];
    do {
      var currentUrl = url + endpoint + "?" + queryParams.join("&");
      currentUrl += "&" + pageKey + "=" + pageNum;

      var response = UrlFetchApp.fetch(currentUrl, options);
      data = JSON.parse(response.getContentText());
      var invoices = data.data;
      var rows = [];
      for (const index in invoices) {
        const invoice = invoices[index];
        const items_attributes = invoice.items_list.map( item => ({name, quantity, net_price} = item, {name, quantity, net_price}) )
        const items = invoice.items_list.map( item => {
            return {
                number: invoice.number,
                date: invoice.date,
                customer: invoice.entity.name,
                amount_net: invoice.amount_net,
                description: item.name,
                qty: item.qty,
                net_price: item.net_price,
            }
        })
        items.forEach( item => {
          rows.push(
            [
              item.number,
              item.date,
              item.customer,
              item.amount_net,
              item.description,
              item.qty,
              item.net_price,
              (item.qty*item.net_price)
            ]
          )
        })
      }

      var row = rows.length;
      var column = rows[0].length;
      sheet.getRange(sheet.getLastRow()+1, 1, row, column).setValues(rows);

      pageNum++;
      nextPageUrl = data.next_page_url;
    } while (nextPageUrl);
}


// get tracked times from toggl, a time-tracking platform, grouping them by project's name and user
function getTimes() {
  const properties = PropertiesService.getScriptProperties();
  const projects = {}
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.clear();
  sheet.appendRow(
      [
        "project",
        "user",
        "duration",
      ]
  ); 
  console.log(Utilities.base64Encode(properties.getProperty("toggl_username")))
  const headers = { 
    'Content-Type': 'application/json', 
    'Authorization': `Basic ${Utilities.base64Encode(properties.getProperty("toggl_username")+":"+properties.getProperty("toggl_password"))}`
  }

  JSON.parse(UrlFetchApp.fetch(
    'https://api.track.toggl.com/reports/api/v3/workspace/132819/search/time_entries', 
    {
      "method": "post",
      "contentType" : "application/json",
      "headers": headers, 
      "payload" : JSON.stringify({
        "grouped": true,
        "start_date": "2023-01-01",
        "end_date": "2023-12-31",
        "page_size": 100000
      })
    }
  ).getContentText()).forEach((item) => {
    if(!projects[item.project_id]) projects[item.project_id] = {};
    const project = projects[item.project_id]

    if(!project[item.username]) project[item.username] = 0
    const seconds = item.time_entries.reduce((acc, entry) => acc + entry.seconds, 0)
    project[item.username] += seconds/3600;
  })

  JSON.parse(UrlFetchApp.fetch(
    ('https://api.track.toggl.com/api/v9/workspaces/132819/projects'+"?" + [
      "active=true",
      "per_page=200"
    ].join("&")), 
    {
      "method": "get",
      "contentType" : "application/json",
      "headers": headers, 
    }
  ).getContentText()).forEach((project) => {
    projects[project.name] = projects[`${project.id}`];
    delete(projects[project.id])
  })

  //console.log(projects)
  const rows = [];
  Object.entries(projects).forEach(([project, users]) => {
    if (users) {
      Object.entries(users).forEach(([user, hours]) => {
        console.log(`${project}, ${user}, ${hours}`)
        rows.push(
          [
            project,
            user,
            hours
          ]
        )
      });
    }
  });
  addRowsToSheet(sheet, rows);
}