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.
