How to use Google Apps Script to automate processes in your company at no cost

Within the collection of tools and services that we use both in the Studio and in some of our own products, Google App Scripts is one of our favorites. This tool allows us to automate recurring tasks and processes for free by interacting with Google services such as Google Sheets, Docs, Drive etc.

Getting started with Google App Scripts

To create our first script we can do it by accessing the Google Apps Script page. This is the main page of the tool where we will see all the scripts created, the logs of the executions and the associated triggers.

We can also create a script directly from the Google tool to which we want to associate it, in the case of Sheets we can do it by clicking on Tools → Script editor.

This will open a development environment in the cloud where the script code can be written and executed directly.

Now that we know how to create a script, we are going to learn through two examples the basic functionalities that will allow us to start creating automatic processes.

Script to update currencies in Billbot

The first example that we are going to cover is a script that we use to obtain and format the list of currencies that we use in Billbot app that we created in the Studio.

The first thing we have to solve is how to make a request to an API from the script. For this it will be necessary to use the class UrlFetchApp that will allow us to do it and it's one of the most useful methods that AppScripts have.

In our case, we not only need to make one request, but multiple ones, given the structure of the data that the API returns. For this we will use the fetchAll method that allows us to make multiple requests at the same time.

Here is how the code looks like:

In order to see the results of the request we can use Logger.log. This would be the equivalent of the JavaScript console.log but it will allow us to see the results in the App Scripts debugger. To see the records we can press ctrl + Enter on the keyboard or cmd if you are on Mac.

Once we have the data fetched, the next step is to format it as we want. For this we will simply use a JavaScript reduce and we will convert the result of it into a string.

Once the data is formatted we need to store it in the spreadsheet. This can be done using the SpreadSheetApp class. The essential methods of this class are getActive(), which allows us to take the reference of the file to which the script is associated, and getSheetByName(), which allow us to establish the reference of the specific sheet of the associated file.

Once we have the reference of the sheet in a variable, we can use it to read and store data within its rows and columns. With the getRange and setValue methods we can write inside the cell, and with the getValue method we will obtain the value found in it.

Specifically, in our script we want to store all the data obtained in the first cell of the spreadsheet, so we can achieve this with these four lines of code.

Once the script is executed we will find the output on the cell 1,1 of our spreadsheet. We can see that is filled with the data obtained from the API and formatted with our needs.

Only by making use of the Logger, SpreadSheetApp and UrlFetchApp classes that we have just seen, we can create very powerful scripts with some knowledge of JavaScript and some creativity. The following example is a clear case of this: with the use of these three APIs we built a system for creating automatic invoices for our startup.

Cravy Partners billing system

In Cravy every week we had to create invoices for the restaurants that were partners of our platform. Initially we started creating them manually with a Google spreadsheet that we converted to pdf. When we had more than 10 restaurants, the process was quite tedious and could take a whole day of work.

Each restaurant had two documents: the invoice with the commission for our service and a receipt with all the orders associated with that commission.

Taking advantage of the fact that we already had the invoice template and total control over the database and the backend of the project, we decided to create an App Script that would do the following:

In this article we are going to focus on showing the most interesting options that the API provides us and we will not go into detail about the logic of the script itself, but if you want to see the complete script it can be found on Github.

Creation of the folder in Google Drive

One of the classes that we have not seen yet is DriveApp, which allows us to perform operations on Google Drive folders and files.

In our case we want to create a new folder with the date as the name within a specific Google Drive folder.

The FOLDER-ID can be found at the end of the url slug when we are inside the folder as we can see in the image.

Create a copy of the Google Sheets templates for each restaurant

In order to create a spreadsheet for each restaurant, what we do is to execute the function for each of the restaurants that we have. We can do this through a simple for.

Once the function is executed, it requests the restaurant information from the backend using the UrlFetchApp class as we have seen in the Billbot example, and we do the necessary operations in JavaScript to obtain the data in the format we want.

Once we have this data we proceed to create the new spreadsheet using the following lines of code.

Using the create() and copyTo() functions we create two new empty spreadsheets and then copy the content of the spreadsheet that serves as a template. Keep in mind that this will create the files in the root folder of our Google Drive, so in the final step we will have to move these files to the corresponding folder and remove them from the root.

The next step of the script is to fill the spreadsheets we just created with the information obtained from the API using the getRange() and setValue() methods of the SpreadsheetApp class that we have seen in the previous example.

Store the copies created in the corresponding folder and create a PDF version of the file

Finally, the last step is to store the two spreadsheets created and export them as PDF. To do this we only need a small function.

The steps that this function performs are the following: first create a folder with the name of the restaurant inside the date folder generated in the previous step.

Then paste the spreadsheets from the second step into the folder with the name of the restaurant with the addFile() function. Last, it creates the pdf version with the createFile() function.

Finally we delete the files from the root folder to avoid accumulating them.

Conclusions

As we can see, with the App Script and our creativity we can do very powerful things that help us in the day to day of our company and save us hundreds of hours at the end of the month. These are just a couple of examples that we have implemented to automate certain parts of our operations, but the same can be applied to many more cases.

In addition, another advantage of using App Script is that it is a completely free service within the limits of the platform, so we can have cron jobs and other automatic processes at no cost.