It uses Google Apps Script to generate a Sankey chart for cash flow based on your transactions in the Tiller Foundation Template.
What is the goal of your workflow? What problem does it solve?
Sankey is one of the most popular ways to compactly visualize flow-type information and it is a great fit for cash flow. It allows you to very quickly see if you have healthy cash flow and what your major in and out flows are.
How did you come up with the idea for your workflow?
Social media is abuzz with Sankey charts for cash flow and other data viz. I had some time to kill this long weekend and though this would be a fun project.
There is currently no way that I can tell to embed a Sankey chart directly in the sheet without using paid 3rd party extensions. So I decided to show it in a modal dialog on-demand. Please see the repo for setup instructions. Please star the repo if you like this visualization.
Permissions
I am publishing it under Apache license. Feel free to use, copy, improve.
Notes
If you have another idea to enhance Tiller, Iād love to collaborate.
Would love to see more detailed installation instructionsā¦ not sure where to start working on a Mac. Maybe a video would help follow your provided instructions:
Clone this repo locally.
Create an Apps Script project from your Google Sheet by going to āExtensionsā ā āApps Scriptā. The URL should look like āApps Script ā Google Apps Scriptā
Install Clasp and clone the project from the previous step.
Run clasp push on your local machine from the repo base path.
I got it working, but unfortunately with the small pop up window my various categories blow it out and it canāt fit on the screen. It was a lot to get it working for somebody who has never done google script development, I gotta say!
Really cool idea but I donāt think it is something I would use in current state - wish we could have it generate on a full page or similar, and obviously the hurdles of getting clasp and GitHub etc to all work are probably too much for most.
If the height of the dialog box is adequate but the chart is getting cut off, you can increase the chart height in the sankey_chart_ui.html file. Look for this:
var options = {
width: 500,
height: 600,
sankey: {
iterations: 32,
node: {
interactivity: true,
}
}
};
If the dialog itself is too small, its height is controlled in code.ts. lmk if that helps.
Thanks for trying it out and sharing the much better instructions.
I think I found and fixed the issue @traviswu . The issue was that I was setting the height too big for the modal and the sankey. Now I am getting the true element size after rendering the html template and also changed the css a bit to be able to take up the entire space. Please give it a shot. I have been practicing my css recently and didnāt know a lick of web dev when I wrote that plugin, haha
Also, let me know if there is any calculation weirdness you notice.
@jpm.moore Clasp is needed because the code is in Typescript. Clasp transpiles it to Javascript that Apps Script needs. I refuse to code in untyped languages as much as I can. Lmk if you are having trouble with Clasp, Iāll be more than happy to share the Javascript output.
Ok, I got it working but definitely above average difficulty to get going
Same issue as others it doesnāt size properly even attempting the var options fix, also would need to be able to have filters for it to work for my transactions.
@jpm.moore If you cloned before last night and didnāt pull again, you donāt have the attempted fix from last night. Can you confirm if you do have the latest code?
Also, what kind of filter do you need? I thought about having a minimum threshold to group up small items. But if you have something else in mind, I can probably do that easily.
@jpm.moore If I make a debug build that dumps the data behind the charts in your sheet, would you be open to sharing the data with me after any anonymization you want? I think the category names and the exact values donāt matter. Also, please post a screenshot if possible.
Thanks for creating and sharing this @purujit.saha !
Iām on ubuntu but installed it by copying the zip files into app script. Couldnāt figure how to upload the files into apps script so I created blank files and copied the contents from another vi window.
Anyway it runs ok using last monthās date range but when I set it for last year I get this error;
TypeError: Cannot read properties of null (reading āsubstringā)
at getCashFlowData(sankey:24:51)
Any suggestions on what is causing that? All of my transactions are assigned to a category.
Thanks for reporting the issue @rayfes
From the error you posted, it looks like at least 1 of your transactions donāt have the āAccount IDā column populated. Note that this column is hidden by default in the foundation template - you may have to expand the hidden columns to see āTransaction IDā and āAccount IDā columns. Let me know if you can spot it and if that solves the problem. The āAccount IDā column is used to map the transaction to an account group for savings - so that you can see where your savings is going.
Your hunch is likely correct. I unhid columns K (transaction ID) and L (account ID) and I have a huge number of transactions for which these fields are blank. I moved to Tiller from Mint about a month ago and imported my transactions via CSV export. Those are the ones with these fields missing whereas the newer ones that Tiller populated have these fields.
I have a large number of accounts so going back to fill these fields would be a pain so Iāll just leave it be and use this chart just for newer transactions. Not sure if you can do some error reporting in the code or perhaps just mentioning this in the usage instructions is enough to keep others from making the same mistake I did.
And is there a way to export the graphic as an image? When I set the size to be 2000 it still shows up inside this small popup box that is much smaller than my screen resolution.
