[MUSIC PLAYING]
MINHAZ KAZI: Welcome to DataVis DevTalk."
I'm your host, Minhaz Kazi.
Our today's guest is Ben Collins.
Ben is a freelance data analyst, developer, visualizer
and founder of the Collins School of Data.
He's also a Google Developer expert.
Let's get started.
[MUSIC PLAYING]
Ben, welcome to the show.
BEN COLLINS: Thank you, Minhaz.
Thank you for having me.
MINHAZ KAZI: Tell us a little bit about yourself.
What got you interested in spreadsheets and scripting
in the first place?
BEN COLLINS: Sure.
So after college, I went into the finance world.
And obviously, my bread and butter then is spreadsheets.
So I'm spending all day with data in spreadsheets
with formulas, and gradually, just
became more and more comfortable with the formulas world.
And it's a little bit like a rabbit hole.
The deeper you get into it, the more you discover.
And so the formulas became sort of,
let's find out how I can make these formulas more efficient
and make my work better.
And then that led into the world of scripting, and saying,
OK, this is really interesting.
How can I automate these processes I'm doing
and extend power of the spreadsheets in my work?
So that was my first foray with spreadsheets,
and that was sort of, started 15 years ago, that journey,
I guess.
And then sort of in 2010-ish, I came
across G Suite and the Google Sheets world,
and made that step across into G Suite and Google Sheets,
and found Apps Script pretty soon after that.
So I could continue that journey of learning and getting more
from my spreadsheets.
MINHAZ KAZI: So I have to ask you this though,
VLOOKUP or INDEX MATCH?
BEN COLLINS: A good question.
And I've got feet in both camps, I'd say,
because I think that the VLOOKUP is a great one when you just
need to do something quick and easy.
It works, it's great, as long as you know how to use it.
And then the INDEX MATCH gives you a lot more flexibility.
It seems to be the question that divides the spreadsheet
world in two.
And I know that in my old office,
we had camps in both, advocates for both,
and there were pretty fierce advocates for each.
But I think that I would say learn both.
Go and do the VLOOKUP, and then really, it's
not too difficult to jump across and learn INDEX MATCH.
And that opens up tons more opportunities
with what you can do with the INDEX and the MATCH,
both separately and together.
MINHAZ KAZI: So let's say I'm an analyst
and I don't know anything about Apps Script.
Why is it important for me?
And how do I get started?
BEN COLLINS: Sure.
So let's say every week you create a report,
and you apply the same formatting,
you create the same chart, you do a little bit of preparation
with your data, and each week it's the same.
And you're sort of spending 15 minutes, half an hour, an hour,
doing this pretty repetitive process.
Then that's a great contender for automation.
And that's what scripts allow you to do.
So with just a few lines of code,
you can transform some data.
And then a few more lines of code, you can create a chart.
And that's the power of Apps Script.
You write that once, and then each week, you can press a row
and it will do those steps for you.
So a great way to get started actually,
is to use the macro recorder.
And what that is, is a built-in feature in the UI,
so in the actual spreadsheet itself
where you can press the big red button,
do your thing, which is sort of add some formatting,
create the chart, process your data, and then press stop.
And the macro recorder turns that
into code in the script editor, which
you can then just go and run when
you want to repeat the action.
So that's a great way to get started.
And then from there, you can open up the script editor
and see exactly the code that the macro recorder has created,
and you'll start to see, even if you don't understand it
at first, you'll start to recognize sections
where they perhaps, you'll see some cell references.
And then you'll start to understand a little bit
about how that code is interacting
with the spreadsheets.
Another way is custom functions.
So custom functions are a way for you
to write a little bit of Apps Script
to do something that's completely
unique to your workflow.
So you might create a function that
uses Google Maps to work out the distance between two points,
and you can just use that function then just
in your Google Sheets, without needing to keep rewriting code
or going to external, to the maps
and doing that separately and then copying and pasting it
back in sort of thing.
So it's a lot of time saving.
MINHAZ KAZI: Switching gears, how did you
get started with Data Studio?
BEN COLLINS: So I remember back in 2016,
I was doing a lot of work with Google Sheets.
And then up pops onto the radar this new tool
coming from Google that's a business intelligence
tool for the self serve.
In other words, something that you're
going to be able to use yourself to create these
visualizations and reports.
And so I was just super interested.
Because I'd worked in this space for a long time now,
and so I'm always sort of interested in new tools,
and especially ones that will extend
the capabilities of what I'm doing with the tools
I have right now.
And so I was super interested, jumped on it
and got involved with a beta program
and collaborated with you on some feedback,
and wrote some blog posts, recorded some videos,
and that's sort of how it all sort of got started for me.
MINHAZ KAZI: One question I often hear from folks,
is that you can always build graphs and charts
in spreadsheets and can share that.
What is the benefit of having a dedicated business analytics
tool or reporting tool like Data Studio?
BEN COLLINS: That's a great question.
And I think one of the principle benefits is, first of all,
it's very simple and easy to use to create very professional
looking reports.
So with a spreadsheet, it's a little bit more work
to make it look like a report, a dashboard,
rather than a spreadsheet that just has some charts in it.
So maybe that's important, so that your stakeholders look
at the report and take it a little more
seriously than just perhaps it's a thrown together spreadsheet.
So it makes your professional reports very easy to create.
Another huge benefit is, it separates the data
from the visualization.
By having a dedicated report that people
interact with, with the data in a separate layer
that they can't touch.
It just make sure that that data is nice and secure.
MINHAZ KAZI: You have the unique capability
of looking at this from both an analyst's point of view
and from a developer point of view.
Thinking from that angle, how do you
think Data Studio can be beneficial to small and medium
businesses?
BEN COLLINS: What it does is, it democratizes data.
It opens up the data to so many more people.
And so both building with Data Studio or being given a report
and interacting with it and using a report,
are both very, very intuitive and easy.
They have that same interface as the G Suite tools,
so it sort of feels familiar already.
And then building the actual report
is really quick and really easy.
And so it really lowers that barrier to entry for people
to go and explore their own data.
And then also just using those reports.
If a report is shared with you, it gives you a lot of options
to interact with the data, to drill down
into the data in a very visual way.
MINHAZ KAZI: I love the demo that you
built with the International Space Station position,
so you could see the position of ISS at any point of time,
like where it was.
Tell us a little bit about that.
How did you build that?
BEN COLLINS: Sure.
So it was right around the time actually that Google
was introducing the Community Connectors, which
are the connectors that you can connect to third party
services with Data Studio directly and pull
in that data from another web service
into Data Studio, which is like super cool.
I was excited about this.
And I was looking around for some examples
to just try this out, because there
was a couple of examples on Google's websites,
which I tried.
And then I thought, you know what?
I wonder if there's a way to plot the position.
I'm always interested in space stuff.
I follow the rocket launches and that kind of stuff,
so I'm a bit of a space geek.
I searched around, found an API that
publishes the position in latitude and longitude
of the International Space Station and thought,
let's see if we can make this work.
And it was a great first connector
to build, because the authentication was,
there was no authentication, so it was a nice simple connector.
Pull in that lat and long, and plot it on the world map
in a nice dashboard that had some touches, like a picture
and sort of a current date and things just to make
it look a little more polished.
MINHAZ KAZI: You have built a number of these connectors,
both as like open source, and for your clients.
What does your workflow look like when
you're trying to build a connector for a new data set?
Where do we start?
What is your workflow?
BEN COLLINS: So I would, I always
start by spending some time with that service.
And it depends.
If it's a service that I use myself, obviously,
I'd be familiar with it.
But if it's a client that I'm working for
and it's their service or a service
they use, then what I like to do is,
make sure I go and get a demo account,
spend some time with the tool, so I
understand what the context is.
And I can start to understand why would somebody
want that data in Data Studio and what
are their plans to do with it.
I think it's very important that you
have that context before you just jump in and rush
to build the connector, so that you
know when you bring that data through that pipeline, where
it's going to end up and what it might look like.
So I start with the tool, the service, spend some time there.
Next thing is to go and find any documentation about the API.
Spend time reading that, see if there
are any tutorials or guides to just have a look at
and understand.
And then what I do is, I open up a Google Sheet and the script
editor and build a connection to the API
from my Google Sheet first, and then
start putting the data into Google Sheets.
And the reason I do that, is because I
don't need to worry then about the Data Studio architecture
yet.
I can focus purely on the connection to the API,
start getting some data back, maybe check the authentication
and how that works.
And then start parsing that data and making sure
that I've got the API piece all built and sort of rock solid.
Then I can open up Data Studio and, or rather
the Apps Script for Data Studio, put in the boiler plate,
build the plumbing, and then plug in the API piece finally.
And that's my approach that rather
than trying to sort of immediately
jump to Data Studio file and build it in one go.
MINHAZ KAZI: So hopefully some of our audience
would benefit from that.
Talking about the audience, can you
share your favorite Data Studio tip with them?
BEN COLLINS: Sure.
So I was thinking about this a little bit
before I came in today, and I was racking my brain
for something super specific, maybe
a neat little unique feature or something.
And then I sort of realized that actually,
my top tip really is to spend some time learning
about the best practices for data visualization.
So knowing the best chart type to use for the data you have.
How to make sure that your dashboard is
as impactful as possible.
Because that will have a bigger impact
than sort of focusing too much on the tiny granular details,
if you really can make sure that your data pops off the page
and gets that message across as efficiently and effectively
as possible, then you'll get maximum impact
from your dashboards.
MINHAZ KAZI: Ben, thanks for being on the show.
BEN COLLINS: Thank you very much, Minhaz.
MINHAZ KAZI: You can check out Ben's blog
and his courses on his website.
Link is in the description.
For more Data Studio content, make
sure to subscribe to the GCP channel.
You can always visit our website at developers.googl
e.com/datastudio.
Until next time, keep building more awesome vis.
[MUSIC PLAYING]