⚡️ Automate reporting with Amalia API and Gsheet

Updated 3 months 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

// Google Apps Script - Amalia API Integration
// This script retrieves data from the Amalia API and writes it to a Google Sheets spreadsheet.

////////////////////////////////
////////////////////////////////
////////////////////////////////
const AMALIA_API_KEY = 'YOUR_AMALIA_API_KEY'
const CUSTOM_REPORT_ID = 'YOUR_CUSTOM_REPORT_ID'
const SHEET_NAME = 'NAME_OF_THE_SHEET_WHERE_YOU_WANT_THE_DATA_RETRIEVED'
const ROWS_PER_PAGE = 500; // Default value is 100, up to 500
/////////////////////////////////
////////////////////////////////
////////////////////////////////

function retrieveAmaliaData() {
    // Set the active spreadsheet and retrieve the sheet by name
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);

    // Clear the sheet to remove previous data
    const lastRow = sheet.getLastRow() + 1;
    const lastColumn = sheet.getLastColumn() + 1;
    sheet.getRange(1, 1, lastRow, lastColumn).clearContent();

    // Initialize pagination variables
    let pageNumber = 0;
    let paginationContinue = true;
    let retrievedRows = 0;

    do {
        // Retrieve data from the API
        const response = UrlFetchApp.fetch(
            `https://api.amalia.io/v1/custom-reports/${CUSTOM_REPORT_ID}?page=${pageNumber}&limit=${ROWS_PER_PAGE}`,
            {
                headers: {
                    ['X-API-KEY']: AMALIA_API_KEY,
                },
            }
        );

        // Parse the JSON response
        const parsedResponse = JSON.parse(response.getContentText());

        // Write the headers only once, after the first query, no need to repeat this for each page
        if (pageNumber === 0) {
            const headersRange = sheet.getRange(1, 1, 1, parsedResponse.columns.length);
            headersRange.setValues([parsedResponse.columns.map(col => col.label)]);
        }

        // Write the data rows in batches of "pageSize"
        const firstRowIndex = pageNumber * ROWS_PER_PAGE + 2;
        const rowsLength = parsedResponse.records.items.length;

        const firstColumnIndex = 1;
        const lastColumnIndex = parsedResponse.columns.length;

        // Get the range for the batch of data
        const batchRange = sheet.getRange(firstRowIndex, firstColumnIndex, rowsLength, lastColumnIndex);

        const rows = parsedResponse.records.items.map((row) => [
            // Map the other columns, converting currency values to their numeric representation
            ...Object.values(row).map(value => value?.symbol ? value.value : value)
        ]);

        // Set the values for the batch of data
        batchRange.setValues(rows);

        // Increment the number of rows retrieved
        retrievedRows += rowsLength;
        console.log(`${pageNumber * ROWS_PER_PAGE + rowsLength} rows added`);

        // Check if there are more pages to retrieve
        // If the number of rows retrieved is less than the limit, stop pagination
        paginationContinue = rowsLength === ROWS_PER_PAGE;
        pageNumber++;
    } while (paginationContinue);

    return retrievedRows;
}

/**
 * Creates a custom menu in the Google Sheets UI.
 * This menu allows the user to trigger the data retrieval function.
 */
function onOpen() {
    SpreadsheetApp.getUi()
        .createMenu('Amalia')
        .addItem('Sync data', 'syncData')
        .addToUi();
}

function syncData() {
    const startTime = Date.now();
    const retrievedRows = retrieveAmaliaData();
    const endTime = Date.now();

    SpreadsheetApp.getUi().alert(`Retrieved ${retrievedRows} rows in ${(endTime - startTime) / 1000}s`);
}

1. In the top of the script, set your values (sheet name, API key, ...)

3. Then follow this tutorial video

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

The script will also add a button in your GSheet navigation bar:

That's it ✨

How did we do?

Related Articles

• Get started with Amalia API
• ⚡️ Automate reporting with Amalia API and Zapier