- In this video, I'm talking to Ben Collins
about getting started with Google Apps Script.
All and more coming up right after this.
(upbeat music)
Hi there, and welcome to another video of measureschool.com,
where we teach you the data-driven way of digital marketing.
My name is Julian, and on this channel we do
marketing tech reviews, tutorials,
and the occasional interview just like this one.
So if you haven't yet, consider subscribing.
Now awhile ago, Ben Collins did a video for us
on how to pull and Mailchimp data
with the help of Google Apps Script into Google Sheets.
And, since then, I was really enamored
with Google Apps Script and trying
to figure it out a little bit more.
So I thought why not ask the expert himself.
I came back to Ben Collins, and we sat down
to talk about how he got started at technical marketing,
how he taught himself how to code and learn Apps Script,
and what his tips would be for Apps Script beginners.
We've got lots to cover, so let's dive in.
So welcome to the show Ben.
- Hi, thank you, thank you for having me Julian.
- Take us a little bit into what you're doing now.
I always see you as the Apps Script guru
when I look at your blog,
and how you think about stuff
and how you solve stuff with Apps Script.
But take us back to when you discovered Apps Script
with your blog and clients asking you,
"Can you develop this for us?"
What made you choose Apps Script as a platform
as opposed to Excel, right, also really big platform.
- Yep.
How did I get here?
Well, in some ways,
you know, I sorta say that
Apps Script chose me in some ways,
because, like I said, I was using Excel a lot
when I was in the financial career, in the financial mode.
And I was teaching myself some VBA
and really enjoying learning the technical side of things.
And basically becoming what I call a spreadsheet developer,
which is sort of how I talk about myself now,
where you build applications around spreadsheets
to automate data analysis and reporting.
And just the sort of clients I had as well were,
they were mostly, you know, start-ups
or digital marketing agencies actually.
And I would say 75% or 80% of my clients were
Google Sheets users or Google Doc users.
And so, actually, when they wanted to
sort of automate a reporting process or
connect to some sort of API
to bring data into their sheets,
it was always, can we do this with Google, with Google Apps.
So, you know, that's just where the requests came in.
As soon as I started to dig into Apps Script,
which was actually ...
I first came across it, I think, four years ago.
So even though it even proceeded any of the client work,
but really I picked it up properly about two years ago.
- Great, so give us some examples
when you help out your clients with Apps Script,
what kind of projects do you take on?
And tell us maybe a you case where an Apps Script
made a great difference for your client as well.
- Yeah, sure, I can think of a few examples actually.
So, I worked with a client last year.
So, their own, so this,
my client had their own clients, so like an agency,
and their clients were in the music business.
I think just once a month or maybe once a week
they wanted to compare all the different charts,
charts across these different European countries,
and then match up all the different songs
to see where the particular songs were on different charts
to try and discover new artists,
who was going up and who was going down, that kind of thing.
And they would do this whole process
by hand, or mostly by hand.
They would sort of, they had a few
tools to bring the data into Google Sheet,
but then they would be matching them all
by hand, which takes a huge amount of time.
It would take them days each month,
and it was error prone, you know, because
you can easily miss one song
when it's a big, long list of a hundred.
And so, what I helped do for them was to actually
build a whole tool and Apps Script
that would take two lists in your sheets
and compare the two with this fuzzy, matching zone
because the song titles weren't quite always the same.
And it would match them approximately and say
here's the, sort of, 75 songs that we've matched up
across these different charts,
and then here's the 25 new ones
that appeared on the German chart Top 100
but didn't appear on the Italian chart Top 100,
or something again.
Give them a little output like that, and it would take
under a minute to sort-of churn through
and just give this output.
And then they could have this list
that has previously taken them a couple days.
So that was a really fun project,
it was actually just an interesting
Apps Script work on that one, and then the climbs rate.
That was a fun project.
And then I'll tell you one other example
actually as well that I
created it for myself, actually,
because I've done some work teaching at a General Assembly.
I've taught there, data analysis schools three times.
Their part-time data analysis schools.
And, one thing you do in that,
is you mark all the student projects,
and you give them all feedback,
which can mean sending out 25 different emails,
and then 25 different slack messages.
You know it can be kind-of burdensome
to just do it one-by-one.
So what I did was, because we had all the results
in a spreadsheet already, so it was kind-of
cutting and pasting from the spreadsheet to an email,
then putting the name at the top and pressing send,
and then doing the next thing.
So we had this spreadsheet with all the emails,
all the names, all the marks in the project,
all the feedback good and bad,
and improvements and what-not.
And so I wrote a little script to just
pull that automatically into an email,
and then automatically into a slack
message as well, and just send.
So you could literally just press a button,
and it would send out all 25 schools simultaneously.
And I didn't have to do that manual steps.
Those are really nice things, well actually there,
You know, it could save me a couple hours work.
- So what would you say,
tell us a little bit about Apps Script.
What would need somebody know
in order to get started with it?
What are the basics,
and where could they find out more about this?
I know you have a great blog,
but there might be other resources out there that
can get people up to speed as well.
- Yeah, sure.
So Apps Script is based,
it's like the Javascript language essentially.
It's a little bit different,
because it's a little bit, sort-of like a slightly,
slightly older version of Javascript.
But it's essentially the Javascript language,
which is the defective language of the web
that we're all used to, and I'm sure ...
So it's a good one to learn anyway, even if
maybe you don't necessarily go down
becoming like an Apps Script specialist,
just having a little bit of knowledge there
means you know a little bit of Javascript,
which is gonna help you
with the technical side of things anyway.
Well, so the nice thing is that,
because it's on the Google servers,
you can just open up a spreadsheet, a blank Google Sheets.
Under the Tools menu, there's a script that is a,
you can just pop that open and start writing.
There's plenty of examples online.
We can talk about those, some URLs in a moment.
You just start writing a little bit of code
and just press run and just ...
You can have your first program going very, very quickly.
So it's a nice environment,
because you can go straight in there,
you're not getting too frustrated,
trying to set everything up or understand how to
do all that side, that side of things.
Yeah, I mean there's a lot of great resources out there.
The official Docs are pretty comprehensive,
and you need to go there.
You'll get used to using them when you,
you're doing an Apps Script
and you wanna find out some specific thing.
You'll go to Doc as a starting point
and find out what this particular class does,
maybe that you want to use.
And it'll tell you what the parameters are,
or how you, what the syntax is, and that kind-of thing.
So the Docs are, even though that
they take a little while to fully understand
exactly what the information there is telling you
and things, but definitely you'll wanna spend
some time there so we can share that one.
And they also, they have a five-minute quick start
to just get you up and running as well,
just a couple of example scripts that you can start running.
Then, I've written a blog post myself actually
that's called Beginner Guide to Using Apps Script
that's very, very basic.
It just talks about how you can write
your very first program about the classical, "Hello world!"
Computer program just using Apps Script,
and it talks a little bit about
finding that script that is there.
And it's,
that's very basic, so you can start there.
And then there's a couple of great forums online as well
that you can go to with questions.
That might be something that you would use
a little bit further down the line,
once you've got a little bit of
momentum. - One resource that
I can also recommend also, a little bit more advanced,
but there are some great YouTube resources
on the Developer channel of Google.
- Yep. - A bit more advanced
because they all talk about new features.
And then there's also Unscripted, is that a show?
- Yep. - Where Apps Script developers
basically geek out on Google hang outs
on the newest developments in Apps Script.
So, I've heard there are some great developments going on.
So where do you see the whole Apps Script platform going?
What do you think, is it still something for the future?
Or should everybody stop using Apps Script?
- Actually, that's a good question and a valid question
because technology changes so quickly.
But I think Apps Script is definitely
on a very solid foundation and will be here
for a long time yet, because it's,
it's connected now with all of the different
Google App products and connects, like I said,
very seamlessly with all the APIs.
And so there's a lot of people building,
a lot of tools now, with Apps Script.
And their investing in it, in that it constantly,
as you said, on the development channel
they bring up new resources,
they're always adding new features and things of that.
They actually had the Google Cloud conference in March,
so that, not so long ago, and the last totally
Unscripted episode was all about some
of the developments there actually
and talking about where the future of Apps Script is going.
So I haven't had a chance to watch that myself,
but it definitely sounds very positive.
- Yeah, so, at the end we have some time to talk about you,
and what are you up to at the moment?
And where can people find out more about you?
And what you offer?
- Sure, so, my website is benlcollins.com,
so benlcollins with the L in the middle,
and, at the moment, I am,
I am focusing on online learning actually.
So I launched my first course in February
about building Dashboards and doing reporting
with Google Sheets and Data Studio.
And that has a little bit of Apps Script in there as well.
And then,
I'm working on a second course now.
Again, it's another another Google Sheets one,
but more about the data analysis side of things,
data cleaning and things.
And I'd like to, so I'd like,
that's where my focus is at the moment
more so than the client work.
I'd really love to just launch
a handful of these Google specific courses around data,
around teaching people to automate processes, and ...
Because I really enjoy teaching,
and I really enjoy seeing people
learn, and that's probably the most rewarding
thing I can do.
So that's my focus at the moment
is just trying to kinda teach more of these courses.
- Yeah, I can just say that
we have bought Ben's course as well,
and then I offered Ben,
"Hey, why don't you come onto measure school
"and sell your courses through us as well."
So you can check it out there as well,
but I can vouge for the course.
It's really well put together,
and it's also unique in the marketplace
because who can you find that actually knows his way out.
And who knows his way in Apps Script
but also data analysis.
That's a unique ability from my perspective.
Alright, thank you Ben,
and I hope to get you on again next time.
- Great. Well thanks so much Julian.
It's been a great chat, and I hope to roll out some value.
And if anyone has questions,
then you find contact details for me on the show.
- Thanks.
Alright. So there you have it.
I hope you found these tips useful
on how to get started with Google Apps Script
and enjoyed our conversation.
Now there is much more to this conversation
that you can check out on the techmarketer.io/bencollins
where we have the full interview for you.
We had to cut it down a little bit
for this format here on YouTube,
but I hope you enjoyed this conversation.
And, if you like these videos
that we do here on measure school,
then be sure to hit that subscribe button right over there.
I'll see you next week in our next video.
My name is Julian.
'Til next time.