How can you get started with Google Apps Script?
Google Apps Script allows you to create add-ons for Sheets, Docs, or Forms, automate your workflow, integrate with external APIs and a lot more.
In this episode we invited Data Analyst and Apps Script Expert Ben Collins and asked him how he got started in using App Script when developing solutions that helped his clients to automate their workflows.
In this video, I’m talking to Ben Collins about getting started with Google Apps Script. All and more coming up right after this. 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.
Discovering App Scripts
– 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.
Applications of Apps Script?
– 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.
What is App Script?
– 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.
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.
Resources for Learning Apps Scripts
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.
– A bit more advanced because they all talk about new features. And then there’s also Unscripted, is that a show?
– 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 is the Future of Google Apps Script?
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.