Google App Script — Take Your Automation To The Next Level

In this article I’ll be introducing to you a very powerful way to fully automate your projects and make them run entirely in the cloud 24/7 SUPER EASILY !

This magic way in question is Google App Script, In a nutshell Google App Script is just Javascript for Google, the same exact JS but with APIs to interact with Google Apps like Sheets and Docs, which makes the language more powerful is the environment it runs in that provides powerful features like triggers that allows you to run a script in a periodic manner which is why i prefer the language for anything automation

Today we’re gonna buid a little script that will log the price of Bitcoin every hour then will show you how to make a quick neat chart out of the data WITH ZERO CODE so let’s see the things we’re going over today

1. Introduction to App Script and running our first script
2. Adding NPM packages to our script to use
3. Using triggers to run our script and send us reports in case of any failure taking place
4. Graph the data we have

How To Start Using Google App Script

Head over to google sheets then click tools > script editor

You’ll see now your script editor window and let’s run our first script !

Hello World In Google App Script

In your text editor paste in the following line of code which I’m sure you’re familiar with if you have a basic knowledge of JS

console.log("Hello World !"); and hit CTRL and S to save it then hit the Run button as in the image down below

When you run a script for the first time it’ll prompt you to give it permission to run so just say accept and you should then see an output similar to this

WOOHOO! you’ve successfully run your first App Script

Now let’s move to the fun part and see how to add a library, in this case NPM package called Cheerio to perform some web scraping

Adding A Library To A Google App Script Project

So if you come from a JS / Node background you’re familiar with NPM and requiring a package to use it, with App Script it’s quite different as you’re going to see down below

To include a library in our project we need to click on “Libraries” then add the script id to that library, right now we’re going to add Cheerio to our project and the way we get that script id is by googling and find out

This is where to find that id so now let’s go ahead and add it from our script editor window

Click “Look up” then “Add” when it turns blue and you’re good to go !

Let’s run few examples from their github just to see what we’re dealing with

Let’s break down what’s going on here and what those packages do

1. UrlFetchApp is a built-in API that acts as a HTTP client that helps us retrieve the HTML source code of any page which is the first step of any web scraping project, think of it like axios
2. Cheerio is an amazing library to help us navigate the retrieved HTML content and extract desired pieces of data in a JQuery fashion, brush up on your JQuery selectors here
3. Since we’re expecting several elements with anchor tag, we use the function each() to loop over the results and the function MUST take an index parameter and then we get the text attribute of the selected item

Now let’s see how can we add data to our spreadsheet with App Script using SpreadSheetApp

Now you should be able to see the data logged to your spreadsheet like in the picture below

Scraping Bitcoin Prices Every Hour

Now with this knowledge gained in regards to web scraping basics and also extending Google Sheets using App Script, let’s now go online and find the current Bitcoin price and save it to our spreadsheet alongside a timestamp

To break this down into pieces we will have to

1. Navigate to the coinmarketcap website — a very popular cryptocurrency news / stats website
2. Look for the Bitcoin price value on the page and try to find a distinctive selector
3. Use App Script to append a new row to our spreadsheet containing that and a timestamp

Easy peasy ? Let’s get to coding :)

Now if we look at our spreadsheet we should see the info being added

How To Automate This Process ?

In my opinion, the most useful feature in App Script is creating triggers, so you write a simple function then the trigger will execute it regularly as specified by you

To create a trigger follow the picture below and we’ll have it run hourly when asked how often

Then click the big blue button in the bottom right to add a new trigger, you’ll be prompted to specify few options so to make it run hourly we pick as in the picture below

TADAAAAA now click save and pat yourself on the back cause you’ve created your first fully automated USEFUL program

Your imagination is the only limit to what you can do now with the power of App Script and that wraps up our article / tutorial today ♥

Conclusion

App Script is a powerful way to extend Google products with the power of code and we get to automate a lot of daily tasks using it, with proper JS basics you can do a lot with it and combine many functionalities of Google products for your own needs