Script to collect income and expenses per year in sheets - JavaScript

I use DT3 to collect all my expense and income receipts using different databases for private and real estate/tenament receipts as OCRed PDFs. For tax purposes, I need all the receipts with dates and amounts in separates tables. After some experimenting, I came up with this JavaScript script. It can be run from the script editor, the command line or from within DT3.

Obviously, the script relies on some assumptions like the availability of a custom meta data field with the amount for each receipt. I tried to annotate all parts where modifications might be necessary.

Nevertheless, it might be useful for someone. If only to see how some DT3 tasks can be solved with JavaScript.

/*
   Collect income and expenses for one year in separate sheets 
   Conditions: documents are PDF
        document names contain the date in yyyy-mm-dd
        income receipts are contained in group "Einnahmen"
	expense receipts are contained in group "Ausgaben"
	uses custom meta data "Betrag" (aka amount) to save the income/expenses amount in the sheet as well
   The year is entered by the user in a dialog
   The script loops over all databases and creates one sheet per database/income and database/expense documents
   Possible modifications are marked with ###MOD
   */	
   
(() => {

var app = Application('DEVONthink 3');
app.includeStandardAdditions = true;

var yearDialog = app.displayDialog('Jahr', { defaultAnswer: "2020" }); /* ###MOD language */
var year = Number(yearDialog.textReturned)
if (year < 2019 || year > 2050) { /* ###MOD range */
  /* check if the year is in the required range */
  app.displayAlert("Jahr liegt zu weit in der Vergangenheit oder Zukunft."); /* ###MOD language */
  exit
}
var yearString = year + "-";

var dateRE = /\d{4}-\d\d-\d\d/;
var databases = app.databases();

databases.forEach(db => { /* loop over all databases */
["Ausgaben", "Einnahmen"].forEach(typ => { /* ###MOD name of income/expenses groups */
  var group = app.search("name:" + typ + " kind:group", {in: db.root()} );
  if (group.length === 0) {
	return;
  }
  var result = app.search("name:~" + yearString + " kind:pdf", {in: group[0]}); /* ###MOD accepts only PDF documents */
  if (result.length === 0) {
    return;
  }
  var sheet = app.createRecordWith({name: db.name() + ": " + typ + " " + year, 
      type: "sheet", tags: "Auswertung", creation: new Date(), 
	  columns:["Datum","Name", "Tags", "Betrag"], /* ###MOD names and number of columns */
  });
  var myCells = [];
  result.sort((a,b) => { /* Sort records by ascending date */
        var datea = a.name().match(dateRE)[0];
        var dateb = b.name().match(dateRE)[0];
	return datea > dateb;
  }).forEach(rec => {
    /* ###MOD Data to collect, possibly name of custom meta data field */
    var betrag = app.getCustomMetaData({from: rec, for: "Betrag"});
    var tags = rec.tags().filter(t => t !== typ);   /* ignore tags "Einnahmen"/"Ausgaben" */
	
	/* Get the date from the record name and format it */
	var dateMatch = rec.name().match(dateRE);
	var dateFormatted = dateMatch[0].split('-').reverse().join("."); /* ###MOD uses German date format dd.mm.yyyy */
	
	/* Add row to local array */
    myCells.push([dateFormatted,rec.name(), tags.join(","), betrag]); /* ###MOD fields for sheet row */
  })
  
  /* Add all rows to sheet */
  sheet.cells = myCells;
}) /* forEach Einnahmen/Ausgaben */
 }) /* forEach databases */
})();

[from] the command line

Can you clarify this?

I was thinking about

osascript -l javascript filename

Didn’t really try it but theoretically it should be possible.

Didn’t really try it but theoretically it should be possible.

The only thing to adjust there is that the -l argument is case-sensitive. (Capital J, capital S)

With a JS helloworld.scpt on the desktop, we would need to write something like:

osascript -l JavaScript ~/Desktop/helloworld.scpt

FWIW one of the nice things about JS, which can let us write less code, while also reducing the surface area exposed to accidents and bugs, is that we can define the list we want directly by using .map in place of .forEach

Instead of:

  1. Declaring a mutable Array variable,
  2. repeatedly pushing into that variable, and finally
  3. using that variable in another expression

we can take a shortcut, and directly define the result that we want, with no need for any pushes, by defining a map along the lines of:

const nameDate = x => x.name().match(dateRE)[0];

sheet.cells = sortBy(comparing(nameDate))(result).map(rec => {
   ...
   ...
   return [dateFormatted,rec.name(), tags.join(","), betrag];
})

Where sortBy and comparing might be defined as something like:

// sortBy :: (a -> a -> Ordering) -> [a] -> [a]
const sortBy = f =>
    xs => list(xs).slice()
    .sort((a, b) => f(a)(b));

// comparing :: (a -> b) -> (a -> a -> Ordering)
const comparing = f =>
    x => y => {
        const
            a = f(x),
            b = f(y);
        return a < b ? -1 : (a > b ? 1 : 0);
    };

(From: https://github.com/RobTrew/prelude-jxa )

You’re probably right. I didn’t really try to use JavaScript to its deepest depths. At least it’s a lot more terse than Applescript.

I absolutely agree that there’s no need to do that :slight_smile: (I never get involved in building object interfaces and classes for example)

But in the context of casual scripting, I do find that the three Array methods

allow us to avoid all those mutating variables that cost, in practice, most of our real debugging time.

They are pretty much all we have to protect us from the various XKCD bear-traps that lurk around spending time on automation :slight_smile:


etc etc