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 */
exit
}
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 = 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);
/* Split the date at "-", reverse the order to [dd,mm,yyyy] and format it as "dd.mm.yyyy" */
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 */
})();
Yeah… good point, I need it just once a year, no need to update the file with each receipt!
Thanks for the script and for the epiphany!