Admit it, we’ve all been there. Getting frustrated trying to find a specific sheet inside a huge workbook, and not being able to see it amongst all the other sheets.
Well, here’s a quick Apps Script to create a hyperlinked index page at the start of your workbook. Continue reading How To Add A Hyperlinked Index Sheet In Google Sheets
Let’s talk about funnel charts in Google Sheets.
The charts themselves are a bit of a novelty. Yes, they’re aesthetically pleasing because of that resemblance to a real-world, tapering funnel, which reinforces their message, but a plain ole’ bar chart would be equally suitable and actually easier to read data from (because the bars have a common baseline).
However, they throw up some interesting techniques in Google Sheets and for that reason, merit this long article.
We’ll build them using tricks with the chart builder tool, then with two different types of funky formula and finally, and best of all, we’ll build a tool using Apps Script, as shown in this image:
Continue reading Funnel charts in Google Sheets using the chart tool, formulas and Apps Script
Update December 2017: Google has added Waterfall Charts to the native charts in the Chart Tool of Google Sheets, obviating the need for you to manually create your waterfall charts (or use apps script) per my original post.
Now you simply highlight your data, click Insert > Chart and under the Chart type picker choose “waterfall”, as shown in the following image:
Continue reading How to create a waterfall chart in Google Sheets
Ever find yourself needing to repeat an action in your Google Sheets?
For example, on a recent client project I wanted to cycle through my spreadsheet data, compare it to another dataset and apply a matching algorithm. To do this and stay within the 6 minute execution limit for Apps Script, I split my data into “blocks” of 10 rows, counted how many blocks I had and then ran the matching algorithm once for each block until they’d all been processed.
By adding time-based triggers, I setup my Google sheet to do this automatically, rather than having me click a button each time.
Using Google Apps Script, you can add these time-based triggers programmatically, so they can run and stop automatically.
In this post I’m going to show a skeleton example of this time-based architecture, to which you can add your own functionality.
The entire code file is here on GitHub and the spreadsheet is available here (feel free to make a copy: File > Make a copy… and then you can open the script editor to modify the code).
Continue reading Build time-based triggers in Apps Script projects to automate programs
In the following image, I have two browser windows open. On the left, a Google Sheet containing a simple table of data. In the right browser window, a web page with a d3 chart that displays the data from the Google Sheet and can be refreshed dynamically when the data changes.
Continue reading Creating A D3 Chart With Data From Google Sheets
