⚡️ Automate reporting with Amalia API and Gsheet

Updated 1 year ago by CSM Team

This guide helps you set up Amalia API on Google Sheets. You will be able to export Amalia calculations such as statement results, KPIs, payments, etc. on any trigger to any spreadsheet.

1. Get your API key and report url ready

Please refer to this help doc if this is your first time using Amalia API.

You will need:

1. Report url, a link that helps us identify the report you want to access
2. API key, a private token that helps us authenticate your identity

2. Prepare the Script

1. Here is the template script

function callAmaliaReport() {
  // Get the active spreadsheet
  const sheet = SpreadsheetApp.getActiveSheet();

  // Set initial pagination variables
  let paginationContinue = true;
  let pageNumber = 0;

  // Define the API endpoint and headers
  const reportUrl = "https://api.amalia.io/v1/custom-reports/4399a63c-d7ba-4544-b6f7-1e98935a38bb";
  const apiKey = "k1rMQewi!CzO*A12a4nE%SYPekf*8e-!0E2Sh_zPmY!_icZM1KJOBc8P31G%VQTe";

  do {
    // Make a request to the Amalia API
    const response = UrlFetchApp.fetch(`${reportUrl}?page=${pageNumber}`, {headers:{['X-API-KEY']:  apiKey }});
    
    // Parse the JSON response
    const parsedResponse = JSON.parse(response.getContentText());

    // Set column labels in the spreadsheet
    parsedResponse.columns.forEach((column, colIndex) => {
      sheet.getRange(1, colIndex + 1).setValue(column.label);
    });

    // Populate data in the spreadsheet
    parsedResponse.records.items.forEach((line, rowIndex) => {
      const absoluteRowIndex = 100 * pageNumber + rowIndex + 2;
      Object.values(line).forEach((value, colIndex) => {
        sheet.getRange(absoluteRowIndex, colIndex + 1).setValue(value?.symbol ? value.value : value);
      });
    });

    // Check if there are more items to fetch
    paginationContinue = parsedResponse.records.items.length === 100;
    pageNumber++;
  } while (paginationContinue);
}

1. You just need to replace with your Report Urland API-KEY

3. Then follow this tutorial video

https://video.drift.com/v/ab2XCZ4Ln8gcbHQ0JzOOKCXrOzogRjUfcXX6ZTq4w4gw/

That's it ✨

How did we do?

Related Articles

• ⚡️ Automate reporting with Amalia API and Zapier
• Get started with Amalia API
• ➕👤 Add a new rep and calculate his statement