Append a new row in a sheet

Hi , I’m implementing a Smart Rule to parse the name of a pdf file, set the creation date to the one in the file name, then append a new row to a sheet with the parsed information.

I put useful information in the name of some receipts, for tax purposes. I want to save the same information in a sheet for reference and I’d like to use a smart rule to populate it while filing the receipt.

I already implemented it in Hazel using a shell script, but I’d like to do it using DT.

I’ve a different sheet for each year, so the script should choose the sheet according to the date of the receipt.

Is this something achievable?

Yes. I have a script that does this for all receipts of a year which are part of a certain smartgroup. Since I need the information only after the year is over, I didn’t see the need to have it add a single line to a sheet each time a document is changed.

   Collect income and spending for one year in separate sheets 
   Conditions: documents are PDF
        document names contain the date in yyyy-mm-dd
        income documents are contained in group "Einnahmen"
		spending documents are contained in group "Ausgaben"
		uses custom meta data "Betrag" (aka amount) to save the income/spending 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/spending 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 */
var yearString = year + "-";

var dateRE = /\d{4}-\d\d-\d\d/; /* Regular expression for dates in record names: yyyy-mm-dd */
var databases = app.databases();

databases.forEach(db => { /* loop over all databases */
["Ausgaben", "Einnahmen"].forEach(typ => { /* ###MOD name of income/spending groups */
  var group ="name:" + typ + " kind:group", {in: db.root()} );
  if (group.length === 0) {
  var result ="name:~" + yearString + " kind:pdf", {in: group[0]}); /* ###MOD accepts only PDF documents */
  if (result.length === 0) {
  var sheet = app.createRecordWith({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 =[0];
	var dateb =[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 =;
	/* Split the date at "-", reverse the order to [dd,mm,yyyy] and format it as "" */
	var dateFormatted = dateMatch[0].split('-').reverse().join("."); /* ###MOD uses German date format */
	/* Add row to local array */
    myCells.push([dateFormatted,, tags.join(","), betrag]); /* ###MOD fields for sheet row */
  /* Add all rows to sheet */
  sheet.cells = myCells;
}) /* forEach Einnahmen/Ausgaben */
 }) /* forEach databases */

:roll_eyes:Yeah… good point, I need it just once a year, no need to update the file with each receipt!:joy:

Thanks for the script and for the epiphany!