How to use Google Apps Script to automate processes in your company at no cost
Tags: javascript, googlecloud, webdev, programming
Published: 2021-01-05 13:47:59

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](https://script.google.com/home). 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.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/s23mmt7k4l8wswvurq5l.png)

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.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/jt8gx0oez0fc1np08yja.png)

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

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/ei1j7k7djncy3fidu7xe.png)

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](https://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](https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app) 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:

```jsx
function myFunction() {
  var API = 'API_URL';
  var EUR = API + '&base=EUR';
  var USD = API + '&base=USD';
  var GBP = API + '&base=GBP';
  var CAD = API + '&base=CAD';
  var AUD = API + '&base=AUD';
  var CHF = API + '&base=CHF';
  var MXN = API + '&base=MXN';
  var RUB = API + '&base=RUB';
  var INR = API + '&base=INR';
  var BRL = API + '&base=BRL';
  var DKK = API + '&base=DKK';
  var SEK = API + '&base=SEK';
  var NOK = API + '&base=NOK';
  var HRK = API + '&base=HRK';
  var NZD = API + '&base=NZD';
  var CZK = API + '&base=CZK';
  var JPY = API + '&base=JPY';
  var PLN = API + '&base=PLN';
  var RON = API + '&base=RON';
  var THB = API + '&base=THB';
  var AED = API + '&base=AED';
  var HKD = API + '&base=HKD';
  var HUF = API + '&base=HUF';
  var ILS = API + '&base=ILS';
  var SGD = API + '&base=SGD';
  var TRY = API + '&base=TRY';
  var ZAR = API + '&base=ZAR';
  var SAR = API + '&base=SAR';
  var BGN = API + '&base=BGN';
  var QAR = API + '&base=QAR';
  var ISK = API + '&base=ISK';
  var MAD = API + '&base=MAD';
  var RSD = API + '&base=RSD';
  var ARS = API + '&base=ARS';
  var BHD = API + '&base=BHD';
  var BOB = API + '&base=BOB';
  var CLP = API + '&base=CLP';
  var CNY = API + '&base=CNY';
  var COP = API + '&base=COP';
  var EGP = API + '&base=EGP';
  var IDR = API + '&base=IDR';
  var KRW = API + '&base=KRW';
  var PEN = API + '&base=PEN';
  var PHP = API + '&base=PHP';
  var UAH = API + '&base=UAH';
  var UYU = API + '&base=UYU';
  var GTQ = API + '&base=GTQ';
  var PYG = API + '&base=PYG';

  var response = UrlFetchApp.fetchAll([
    EUR,
    USD,
    GBP,
    CAD,
    AUD,
    CHF,
    MXN,
    RUB,
    INR,
    BRL,
    DKK,
    SEK,
    NOK,
    HRK,
    NZD,
    CZK,
    JPY,
    PLN,
    RON,
    THB,
    AED,
    HKD,
    HUF,
    ILS,
    SGD,
    TRY,
    ZAR,
    SAR,
    BGN,
    QAR,
    ISK,
    MAD,
    RSD,
    ARS,
    BHD,
    BOB,
    CLP,
    CNY,
    COP,
    EGP,
    IDR,
    KRW,
    PEN,
    PHP,
    UAH,
    UYU,
    GTQ,
    PYG,
  ]);

	Logger.log(response);
}
```

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.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/daut23vd9l4t1w0onq1u.png)

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.

```jsx
var data = response.reduce(function (previous, current) {
    var currentJson = JSON.parse(current);
    var currencyData = currentJson.rates;
    var currency = currentJson.base;
    var currencyDataWithBase = { ...currencyData, [currency]: 1 };

    return { ...previous, [currency]: currencyDataWithBase };
}, {});

var dataParsed = JSON.stringify(data)
```

Once the data is formatted we need **to store it in the spreadsheet**. This can be done using the [SpreadSheetApp](https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet-app) 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.

```jsx
var ratesSpreadSheet = SpreadsheetApp.getActive();
var dbSheet = ratesSpreadSheet.getSheetByName('db');

dbSheet.getRange(1, 1).setValue(dataParsed);
SpreadsheetApp.flush();
```

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.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/wxj7y02j3czr9d1h1wuc.png)

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](https://cravy.co/) 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.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/82nmn9xoaad7auzdoxi6.png)
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/0phw7n3be9ajeek69kgh.png)

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**:

1. Create a folder with the current date to store documents in Google Drive in an organized way.
2. Obtain orders information for each restaurant by making a request to the backend.
3. Create a copy of the Google Sheets templates for each restaurant and fill them with the information obtained from the API.
4. Store the created copies in the corresponding folder and create a PDF version of the file.

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](https://github.com/reboot-studio-code/apps-script-examples).

### Creation of the folder in Google Drive

One of the classes that we have not seen yet is [DriveApp](https://developers.google.com/apps-script/reference/drive/drive-app), 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.

```jsx
  const now = new Date();
  const billDay = new Date(now.setDate(now.getDate() + 1))
    .toISOString()
    .slice(0, 10);

// Creating the day folder to save the bills
  const folderId = DriveApp.getFolderById('FOLDER-ID')
    .createFolder(billDay)
    .getId();
```

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.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/dhio3uo9pmqs2ds6zz4j.png)

### 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*.

```jsx
for (var index = 0; index < restaurantsData.length; index++) {
    fillSheetWithBillingData(
      restaurantsData[index],
      billDay,
      firstDay,
      lastDay,
      folderId
    );
  }
```

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.

```jsx
//Setting the reference to the original sheets
var billSheet = originalSpreadsheet.getSheetByName('Bill');
var ordersBillSheet = originalSpreadsheet.getSheetByName('OrdersBill');

//Create new restaurant sheet
var newBillSheet = SpreadsheetApp.create(
      'Factura-' + restaurantName + '-' + billDay + ''
    );
var newOrdersBillSheet = SpreadsheetApp.create(
      'Annnexo-' + restaurantName + '-' + billDay + ''
    );

//Copy the parent sheet content to the new created sheets
billSheet.copyTo(newBillSheet);
ordersBillSheet.copyTo(newOrdersBillSheet);
```

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.

```jsx
function exportSomeSheets(
  restaurantName,
  folderId,
  newBillSheet,
  newOrdersBillSheet
) {
  // Save the files in to the correspondent folder
  var folder = DriveApp.getFolderById(folderId).createFolder(restaurantName);
  var copyNewBillSheet = DriveApp.getFileById(newBillSheet.getId());
  var copyNewOrdersBillSheet = DriveApp.getFileById(newOrdersBillSheet.getId());

  folder.addFile(copyNewBillSheet);
  folder.addFile(copyNewOrdersBillSheet);
  folder.createFile(copyNewBillSheet);
  folder.createFile(copyNewOrdersBillSheet);

  DriveApp.getRootFolder().removeFile(copyNewBillSheet);
  DriveApp.getRootFolder().removeFile(copyNewOrdersBillSheet);
}
```

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.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/8d18j90owthqw5wq8fz7.png)

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.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/ia16uhsa5mcqptilmtz1.png)

## 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.