Data Studio Connector fields get an update

The engineers at Google continue to roll out updates to Data Studio at a relentless and impressive pace.

Back in September they released community connectors, which allow you to connect any web service with an API to Data Studio for reporting.

Yesterday, they released an update which gives developers much more control over the connector fields in Data Studio, by allowing developers to define Data Types and Semantic Types with more granularity, in your Apps Script code.

Additionally, developers can now embed calculated fields into the connector’s schema too, so it’s not left up to the user to figure this out.

This is a huge improvement as it obviates the need for the end user to select the correct field settings (for example, which aggregation to use) and should therefore make it easier for users to build accurate reports.

Data Type and Semantic Type features in the connector fields

Continue reading Data Studio Connector fields get an update

10 techniques to use when building budget templates in Google Sheets

It probably won’t surprise you to hear that I use Google Sheets to build financial/budget templates and track my incomings and outgoing, both at home and for my business.

The dashboards available through online banking sites are pretty rudimentary and don’t give me much insight into what’s happening with my finances, particularly over longer time frames.

I like using Google Sheets, as opposed to another third party service like Mint, because it’s fully customizable, it’s easy to use and I can share any spending or budget templates easily with my wife.

Credit Card budget template with Tiller

I’m not a financial expert, so I won’t be dispensing any financial advice here. I won’t opine on what you should or shouldn’t show in your spending and budget templates in this post, nor will I talk about what your financial goals should be or how to get there.

What I will do in this post however, is show you some useful techniques in Google Sheets that you can use for building your own budget templates. Techniques to make them more insightful and more helpful for reaching your goals. They are:

  1. Why are you doing this?
  2. Invest time little and often
  3. Get basic formulas dialed
  4. Leverage power of more advanced formulas
  5. Don’t reinvent the wheel! Google has specific financial formulas
  6. Use comments to record specific details
  7. Get all your financial data into your Google Sheet with Tiller
  8. Set budgets and highlight spend over the budgeted amount
  9. Build drop-down menus to show different categories in your reports
  10. Use words!

Continue reading 10 techniques to use when building budget templates in Google Sheets

Excel tutorial: build a dynamic bump chart of the English Premier League

Premier League team performance

Find your team: See the history of each of the 47 teams in the Premier League here.

In this post I’ll show you how to create a dynamic bump chart like the one above, using historical data for the English Premier League going back to 1992-93 when it was created.

Continue reading Excel tutorial: build a dynamic bump chart of the English Premier League

Save time with this custom Google Sheets, Slack & Email test scoring bot

You’ve marked your students test scores and recorded it all in a beautiful Google Sheet.

You’ve dotted your i’s and crossed your t’s, checked your spelling and made sure all your scores are ready to go.

Now comes the tedious part, copying and pasting each student’s scores and feedback into an email or Slack message to send back to them.

Wait, there’s a better way!

With a little bit of upfront effort using Google Apps Script, you can build yourself a custom Google Sheets Slack/Email app that can automate that whole final step for you.

Besides being useful, it’s also pretty fun to post stuff from your Google Sheet direct to Slack!

Send data from Google Sheets to Slack
Continue reading Save time with this custom Google Sheets, Slack & Email test scoring bot

Creating dynamic charts in Google Sheets with drop down menus

In this post I’ll show you how to create dynamic charts in Google Sheets, with drop down menus (data validation).

I get lots of questions on how to add interactivity to charts in Google Sheets. It’s a great question that’s worthy of a detailed explanation.

Dynamic charts can really enhance reports and dashboards, allowing for more information to be conveyed in the same amount of screen space. This article will show you how to use the data validation method to make a Google Sheets drop down menu to control a dynamic chart.

Continue reading Creating dynamic charts in Google Sheets with drop down menus