How to access domonda from Google Spreadsheet

This article describes how to call the domonda API within a google spreadsheet script.

The following prior knowledge is expected from the user:

An example of using Domonda's GraphQL API in your spreadsheet. In your work spreadsheet open Extensions -> Apps Script and paste the following code snippet:

JS

function myFunction() {
  var data = {
    query:
      '{ allOpHealthChecks { nodes { clientCompanyByClientCompanyRowId { companyByCompanyRowId { name } } uploadedDocumentsTotal uploadedDocumentsCurrentMonth numberOfActiveUsers restructureGroupOpenSince numberOfActiveBankConnections lastBankUpdateDate lastPaymentDate } } }',
  };

  var options = {
    method: 'POST',
    headers: { Authorization: 'Bearer <TOKEN>' }, // Replace <TOKEN> with your own access token acquired from domonda.
    contentType: 'application/json',
    payload: JSON.stringify(data),
  };

  var response = UrlFetchApp.fetch('https://domonda.app/api/public/graphql', options);
  Logger.log(response.getContentText());
}

This will retrieve some health check data about your company. To see it, open the "View" -> "Logs" to see the result. An example of a result:

CODE

{
  "data": {
    "allOpHealthChecks": {
      "nodes": [
        {
          "clientCompanyByClientCompanyRowId": {
            "companyByCompanyRowId": {
              "name": "Testumgebung"
            }
          },
          "uploadedDocumentsTotal": "27",
          "uploadedDocumentsCurrentMonth": "17",
          "numberOfActiveUsers": "3",
          "restructureGroupOpenSince": null,
          "numberOfActiveBankConnections": "0",
          "lastBankUpdateDate": null,
          "lastPaymentDate": null
        }
      ]
    }
  }
}

An example how to populate some of the entities from the response into the current spreadsheet:

JS

function myFunction() {  
  // ... previous code ...
  Logger.log(response.getContentText());

  var rows = [];
  var json = JSON.parse(response.getContentText());
  var dataSet = json.data.allOpHealthChecks.nodes;
  for (i = 0; i < dataSet.length; i++) {
    var data = dataSet[i];
    rows.push([data.clientCompanyByClientCompanyRowId.companyByCompanyRowId.name, data.uploadedDocumentsTotal, data.restructureGroupOpenSince]); // Your JSON entities here.
  }

  dataRange = SpreadsheetApp.getActiveSheet().getRange(1, 1, rows.length, 3); // 3 denotes total number of entites.
  dataRange.setValues(rows);
}

For troubleshooting, please read the following documentation: https://developers.google.com/apps-script/guides/support/troubleshooting.

Another example shows how to access all invoices of your Domonda account imported in current month and store them into the sheet named Sheet1:

JS

function currentMonthImportedInvoices() {
  var now = new Date();
  var beginningOfMonth = new Date(now.getFullYear(), now.getMonth(), 1);

  var options = {
    method: 'POST',
    headers : {'Authorization' : 'Bearer <TOKEN>'}, // Replace <TOKEN> with your own access token acquired from domonda.
    contentType: 'application/json',
    payload: JSON.stringify({
      query:
        'query allInvoicesInRange($from: Date, $until: Date) { filterDocuments(dateFilterType: INVOICE_DATE, fromDate: $from, untilDate: $until) { nodes { invoiceByDocumentRowId { invoiceDate invoiceDateConfirmedBy discountPercent currency iban bic dueDate createdAt } } } }',
      variables: {
        from: beginningOfMonth.toISOString(),
        until: now.toISOString(),
      },
    }),
  };

  var response = UrlFetchApp.fetch('https://domonda.app/api/public/graphql', options);
  Logger.log(response.getContentText());

  var rows = [];
  var json = JSON.parse(response.getContentText());
  var dataSet = json.data.filterDocuments.nodes;
  for (i = 0; i < dataSet.length; i++) {
    var invoice = dataSet[i].invoiceByDocumentRowId;
    if (invoice) {
      rows.push([
        // Your JSON entities here.
        invoice.invoiceDate,
        invoice.discountPercent,
        invoice.currency,
        invoice.iban,
        invoice.bic,
        invoice.dueDate,
        invoice.createdAt,
      ]);
    }
  }

  if (rows.length) {
    dataRange = SpreadsheetApp.getActive().getSheetByName('Sheet1').getRange(1, 1, rows.length, 7); // 7 denotes total number of entites.
    dataRange.setValues(rows);
  }
}

The selected invoice properties ("Your JSON entities here.") to show in the sheet are not complete enumeration of invoice properties, just few are selected for this showcase. The complete list of invoice properties can be found in the GraphQL schema.