Hoe kan ik openstaande posten en bankmutaties downloaden in Google Sheets vanuit Exact Online?

We gebruiken Google Sheets om financiële rapportages op te stellen. Momenteel download ik regelmatig alle openstaande posten en bankmutaties uit Exact Online. Vervolgens verwerk ik die met wat correcties qua opmaak en structuur in Google Sheets.

Dit is een handmatig proces; ik zou liever de Google Spreadsheets automatisch vullen met real-time informatie.

Ik heb kennisgenomen van Download Exact Online into Google Sheets, maar ik begrijp dat Data Access Point voor Exact Online niet meer beschikbaar is via gebruikersnaam/wachtwoord. Ik vind Get My Report niet handig, dan moet ik nog steeds een handmatige actie doen.

Is het niet mogelijk om vanuit Invantive Cloud somehow alle recente bankmutaties en huidige openstaande posten (verkoopfacturen en inkoopfacturen) real-time in te lezen in Google Sheets via IMPORTDATA? Zo ja, hoe doe ik dat?

Liefst niet alleen voor 1 Exact Online administratie importeren, maar gewoon alle in 1x?

Deze uitleg zal zich nog ontwikkelen. In ieder geval komt een uitleg er bij over de for csv clausule en hoe je willekeurige welke tabel kunt downloaden.

De gegevens vanuit Exact Online importeer je in Google Sheets via de volgende stappen:

  • Registreer een account op Invantive Cloud.
  • Maak een virtuele database op Exact Online.
  • Voeg een applicatie toe met een module.
  • Definieer de query in de module.
  • Kopieer de hyperlink voor de module.
  • Plak de code in Google Sheets.

Onderstaand worden de stappen verder uitgewerkt:

Registreer account en maak virtuele Exact Online database

De stappen voor registratie en een virtuele Exact Online database definieren lees je terug op Verbind Power BI met Exact Online. Volg deze stappen tot “Power BI connector instellen voor Exact Online”.

Voeg applicatie en module toe

Doorloop de volgende stappen:

  • Ga naar “Applicaties” (1).
  • En kies “Nieuw” (2).
    Voeg applicatie toe
  • Voer de applicatienaam “Download Google Sheets” in (3).
  • Kies “Opslaan” (4).
    Applicatie Download Google Sheets
  • Kies “Modules” (5).
    Ga naar Modules
  • Voeg een module toe via “Nieuw” (6).
    Nieuwe module

Definieer query in module

Door de volgende stappen:

De code is:

declare
  g_crlf   varchar2 := chr(13) || chr(10);
  --
  l_output varchar2;
begin
  --
  -- Create CSV with all AR outstanding invoices.
  --
  select listagg
         ( concat
           ( to_char(number_attr)
           , ','
           , to_char(invoicedate, 'DD-MM-YYYY')
           , ','
           , to_char(outstandingamtdc)
           )
	 , g_crlf   
	 )
	 csv
  into   l_output
  from   aroutstandingitems
  ;
  --
  -- Add header.
  --
  l_output := 'Number,Date,Amount (EUR)' || g_crlf    || l_output;
  --
  -- Return as CSV download.
  --
  cloud_http.set_response_header_value('Content-Disposition', 'attachment; filename="open-verkoopfacturen.csv"');
  cloud_http.set_response_body_text(l_output);
  cloud_http.set_response_content_type('text/csv');
end;

Kopieer de hyperlink

Doorloop de volgende stappen:

  • Klik bij de applicatie op “Uitvoeren” (12).
    Applicatie uitvoeren
  • Kies de geregistreerde database (13).
  • Kies “Uitvoeren” (14).
    Applicatie uitvoeren
  • Het standaard modulemenu verschijnt.
  • De module kan uitgevoerd worden (15).
  • Maar bewaar eerst de link om de module los uit te voeren tegen de database (16).
  • Bij direct uitvoeren verschijnt na enkele seconden een CSV in de downloadbalk voor alle gekozen Exact Online administraties (17).
    CSV Open Verkoopfacturen
  • De hyperlink van stap (16) kan ook in de browser uitgevoerd worden (18) met dezelfde CSV als resultaat (19).

Direct Laden in Google Sheets

De volgende stap is om de module rechtstreeks in Google Sheets te laten laden via de volgende stappen:

  • Start Google Sheets.
  • Kies “Extra” (1) en dan “Script Editor” (2).
    Google Sheets Script Editor
  • Plak de onderstaande code in de Script Editor (3).
  • Kies “Opslaan” (4).
  • Voeg het werkblad “Data” toe aan het spreadsheet (5).
  • Voeg de URL toe (6).
  • Voeg de Cloud gebruikersnaam toe (7).
  • Voeg het Cloud wachtwoord toe (8).
  • Voeg tenslotte de formule toe om de gegevens te downloaden (9).
    Laad Exact Online gegevens in Google Sheets
  • De data verschijnt in het werkblad “Data”.
  • Als de formule ververst, wordt de data ook bijgewerkt volgens de cache instellingen op de database in Invantive Cloud.

Gebruikte Google Sheets code voor downloaden CSV met Basic Authentication:

//
// This function assumes the CSV has no fields with commas,
// and strips out all the double quotes.
//
// Based upon:
// https://redfin.engineering/when-importdata-isnt-good-enough-retrieving-csv-files-behind-basic-auth-with-a-google-apps-script-6c563f3328c5
//
function parseCsvResponse(csvString) 
{
  var retArray = [];
  var strLines = csvString.split(/n/g);
  var strLineLen = strLines.length;
  for (var i = 0; i < strLineLen; i++) 
  {
    var line = strLines[i];
    if (line != '') 
    {
      retArray.push(line.replace(/"/g, "").split(/,/));
    }
  }
  return retArray;
}

function populateSheetWithCSV(sheet, csvUrl, user, pw) 
{
  //
  // Request the CSV.
  //
  var resp = UrlFetchApp.fetch
  ( csvUrl
  , {
      headers: 
      {
         // Use basic authentication.
         'Authorization': 'Basic ' + Utilities.base64Encode(user + ':' + pw, Utilities.Charset.UTF_8)
      }
    }
  );
  //
  // Parse the response as a CSV.
  //
  var csvContent = parseCsvResponse(resp.getContentText());
  // Clear everything in the sheet.
  sheet.clearContents().clearFormats();
  // Set the values in the sheet (as efficiently as we know how).
  sheet.getRange
  ( 1
  , 1
  , csvContent.length /* Rows. */
  , csvContent[0].length /* Columns. */
  )
  .setValues(csvContent)
  ;
}

Beperkingen

In dit voorbeeld treedt nog een itgenboe020 error op bij het evalueren van de formule in Google Sheets. Dit wordt nog uitgezocht.

In dit voorbeeld is het nog niet mogelijk om een willekeurige tabel te downloaden voor alle Exact administraties zoals beschreven voor JSON in JSON genereren vanuit Invantive SQL. De nieuwe for csv clausule zal dit mogelijk maken.

Ik heb de stappen gevolgd en inderdaad een lijst kunnen downloaden als CSV. Echter toen ik de link kopieerde naar de URL kreeg ik telkens de vraag om in te loggen. Hier lijkt iets fout te gaan. Verder zou ik graag direct in google sheets een lijstje downloaden, dus niet eerst downloaden als CSV en dan uploaden in google sheets. Dit is namelijk al direct mogelijk vanuit exact. Maar misschien komt dit nog?