Connecting Rillet to Google Sheets

Rillet's Reporting API allows you to pull financial data directly into Google Sheets, giving your team a live, customizable view of your financials without any manual exports. This guide walks through how to connect Rillet to Google Sheets using Google Apps Script - no coding experience required beyond copying and pasting a script. Once set up, the sheet can be refreshed on demand or scheduled to update automatically, keeping your data current with minimal ongoing effort.

Step 1 - Generate an API Key

1. Log into Rillet and go to Settings > API Access

2. Click "Create API Key" and give it a name

3. Copy the key from the pop-up and save it in a safe place

Note: The key will not be shown again once generated

Step 2 - Set Up Google Sheets

1. Open a new Google Sheet

2. Go to Extensions > Apps Script

1. Delete any existing code and paste the script provided below

   1. Note: This is an example of how the data can be displayed. Some customization may be desired on a case by case basis.

function importIncomeStatement() {
  var apiKey = "YOUR_API_KEY_HERE";
  var year = 2025;
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.clearContents();

  var months = ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"];
  var monthlyData = [];

  for (var m = 0; m < 12; m++) {
    var fromDate = year + "-" + String(m + 1).padStart(2, "0") + "-01";
    var lastDay = new Date(year, m + 1, 0).getDate();
    var toDate = year + "-" + String(m + 1).padStart(2, "0") + "-" + lastDay;

    var url = "https://api.rillet.com/reports/income-statement"
      + "?from_date=" + fromDate + "&to_date=" + toDate;

    var response = UrlFetchApp.fetch(url, {
      headers: {
        "Authorization": "Bearer " + apiKey,
        "X-Rillet-API-Version": "3",
        "accept": "application/json"
      }
    });
    monthlyData.push(JSON.parse(response.getContentText()));
  }

  var fullYear = JSON.parse(UrlFetchApp.fetch(
    "https://api.rillet.com/reports/income-statement?from_date=" + year + "-01-01&to_date=" + year + "-12-31",
    { headers: { "Authorization": "Bearer " + apiKey, "X-Rillet-API-Version": "3", "accept": "application/json" }}
  ).getContentText());

  var row = 1;

  sheet.getRange(row, 1).setValue("Income Statement " + year);
  row++;
  var headerRow = ["Account", "Code", ""].concat(months).concat(["Period Total"]);
  sheet.getRange(row, 1, 1, headerRow.length).setValues([headerRow]);
  row++;

  function getAmountById(data, accountId) {
    for (var s = 0; s < data.sections.length; s++) {
      var section = data.sections[s];
      if (section.accounts) {
        for (var a = 0; a < section.accounts.length; a++) {
          if (section.accounts[a].id === accountId) {
            return parseFloat(section.accounts[a].balances[0].amount.amount) || 0;
          }
        }
      }
      if (section.groups) {
        for (var g = 0; g < section.groups.length; g++) {
          var group = section.groups[g];
          if (group.accounts) {
            for (var a2 = 0; a2 < group.accounts.length; a2++) {
              if (group.accounts[a2].id === accountId) {
                return parseFloat(group.accounts[a2].balances[0].amount.amount) || 0;
              }
            }
          }
        }
      }
    }
    return 0;
  }

  function writeAccountRow(account, indent) {
    var prefix = indent ? "    " : "";
    sheet.getRange(row, 1).setValue(prefix + account.name);
    sheet.getRange(row, 2).setValue(account.code);
    var monthAmounts = monthlyData.map(function(md) {
      return getAmountById(md, account.id);
    });
    var total = parseFloat(account.balances[0].amount.amount) || 0;
    sheet.getRange(row, 4, 1, 13).setValues([monthAmounts.concat([total])]);
    row++;
  }

  function writeTotalRow(label, totalsArray) {
    sheet.getRange(row, 1).setValue(label);
    sheet.getRange(row, 4, 1, totalsArray.length).setValues([totalsArray]);
    row++;
  }

  fullYear.sections.forEach(function(section) {
    sheet.getRange(row, 1).setValue(section.name);
    row++;

    if (section.groups && section.groups.length > 0) {
      section.groups.forEach(function(group) {
        sheet.getRange(row, 1).setValue("  " + group.name);
        row++;

        if (group.accounts) {
          group.accounts.forEach(function(account) {
            writeAccountRow(account, true);
          });
        }

        if (group.totals) {
          var groupMonthTotals = monthlyData.map(function(md) {
            for (var s = 0; s < md.sections.length; s++) {
              if (md.sections[s].groups) {
                for (var g = 0; g < md.sections[s].groups.length; g++) {
                  if (md.sections[s].groups[g].name === group.name && md.sections[s].groups[g].totals) {
                    return parseFloat(md.sections[s].groups[g].totals[0].amount.amount) || 0;
                  }
                }
              }
            }
            return 0;
          });
          var groupFullTotal = parseFloat(group.totals[0].amount.amount) || 0;
          writeTotalRow("  Total " + group.name, groupMonthTotals.concat([groupFullTotal]));
        }
        row++;
      });
    }

    if (section.accounts) {
      section.accounts.forEach(function(account) {
        writeAccountRow(account, false);
      });
    }

    if (section.totals) {
      var sectionMonthTotals = monthlyData.map(function(md) {
        for (var s = 0; s < md.sections.length; s++) {
          if (md.sections[s].name === section.name && md.sections[s].totals) {
            return parseFloat(md.sections[s].totals[0].amount.amount) || 0;
          }
        }
        return 0;
      });
      var sectionFullTotal = parseFloat(section.totals[0].amount.amount) || 0;
      writeTotalRow("Total " + section.name, sectionMonthTotals.concat([sectionFullTotal]));
    }
    row++;
  });

  if (fullYear.summaries) {
    fullYear.summaries.forEach(function(summary) {
      sheet.getRange(row, 1).setValue(summary.name);
      var summaryMonths = monthlyData.map(function(md) {
        for (var i = 0; i < md.summaries.length; i++) {
          if (md.summaries[i].name === summary.name) {
            return parseFloat(md.summaries[i].amounts[0].amount.amount) || 0;
          }
        }
        return 0;
      });
      var summaryTotal = parseFloat(summary.amounts[0].amount.amount) || 0;
      sheet.getRange(row, 4, 1, 13).setValues([summaryMonths.concat([summaryTotal])]);
      row++;
    });
  }
}

1. Replace YOUR_API_KEY_HERE with the API key generated in step 1 above

2. Update year to the fiscal year you want to pull

Step 3 - Run the Script

1. Click Run at the top of the AppScript page

1. Google will ask you to authorize the script - click through the permissions prompt

2. Wait ~15-20 seconds for all API calls to complete

3. Switch back to your Google Sheet tab - data should be populated

Step 4 - Validate the Data

1. Check that all data has populated from Rillet

2. Verify the Period Total column matches what you see in Rillet's Income Statement report for the same date range

3. Spot check a few monthly figures against Rillet directly

Step 5 - Set Up Automatic Refresh (Optional)

1. In Apps Script, click the clock icon in the left sidebar (Triggers)

1. Click Add Trigger in the bottom right

1. Set function to importIncomeStatement, event type to "Time-driven", and pick your desired frequency (daily, weekly, monthly)

2. The sheet will now refresh automatically on that schedule

Notes

• If you see a 401 error, your API key is likely incorrect - regenerate and re-paste it

• The sheet is overwritten every time the script runs

• Feel free to have Claude adjust some of the code below to adjust formatting!