What can you do with Google Apps Script?

Google Apps Script is a powerful tool that allows you to automate tasks and customize your Google Workspace apps like Sheets, Docs, and Slides. You can use it to create add-ons and custom functions, automate workflows, and integrate with other apps and services. In this article, we will explore some of the things that you can do with Google Apps Script.

1. Create Custom Functions

Google Sheets and other Google Workspace apps offer built-in functions that you can use to perform calculations and manipulate data. However, sometimes these functions do not meet your specific needs. With Google Apps Script, you can create your own custom functions that can be used in any Google Sheets spreadsheet.

Here is an example of a custom function that converts a temperature from Fahrenheit to Celsius:

function fahrenheitToCelsius(temp) {
    return (temp - 32) * 5/9;
}

To use this function in a Google Sheets spreadsheet, simply type =fahrenheitToCelsius(A1) in a cell, where A1 is the cell containing the temperature in Fahrenheit.

2. Create Add-ons

Google Workspace apps offer a vast array of functionalities. However, sometimes you may need to extend these functionalities by creating a custom add-on. With Google Apps Script, you can create add-ons for Google Sheets, Docs, and Slides.

Here is an example of an add-on that adds a custom toolbar to a Google Docs document:

function onOpen() {
    DocumentApp.getUi()
        .createMenu('Custom Menu')
        .addItem('Open Sidebar', 'openSidebar')
        .addToUi();
}

function openSidebar() {
    var html = HtmlService.createHtmlOutputFromFile('Sidebar')
        .setTitle('Custom Sidebar')
        .setWidth(300);

    DocumentApp.getUi().showSidebar(html);
}

This add-on creates a custom menu called “Custom Menu” and adds an item called “Open Sidebar.” When the user clicks on “Open Sidebar,” it opens a custom sidebar that displays the word count of the document.

3. Automate Workflows

Google Apps Script allows you to automate repetitive tasks and workflows. For example, you can create a script that automatically sends an email reminder to your team members if they have not completed a task by the deadline.

Here is an example of a script that sends an email reminder to the specified person if a task is overdue:

function sendReminder() {
    var overdueTasks = getOverdueTasks();
    if (overdueTasks.length > 0) {
        var recipient = 'example@example.com';
        var subject = 'Overdue Task Reminder';
        var body = 'The following tasks are overdue:\n\n';
        for (var i = 0; i < overdueTasks.length; i++) {
            body += overdueTasks[i] + '\n';
        }
        MailApp.sendEmail(recipient, subject, body);
    }
}

function getOverdueTasks() {
    var sheet = SpreadsheetApp.getActive().getSheetByName('Tasks');
    var range = sheet.getDataRange();
    var values = range.getValues();
    var today = new Date();
    var overdueTasks = [];
    for (var i = 1; i < values.length; i++) {
        var date = new Date(values[i][1]);
        var diff = today.getTime() - date.getTime();
        var days = Math.floor(diff / (1000 * 60 * 60 * 24));
        if (days > values[i][2]) {
            overdueTasks.push(values[i][0]);
        }
    }
    return overdueTasks;
}

This script gets the list of tasks from a Google Sheets spreadsheet and checks if any tasks are overdue. If any tasks are overdue, it sends an email reminder to the specified recipient.

4. Integrate with Other Apps and Services

Google Apps Script allows you to integrate with other apps and services. For example, you can use Google Apps Script to fetch data from a web API and display it in a Google Sheets spreadsheet.

Here is an example of a script that fetches data from the OpenWeatherMap API and displays the current temperature in a Google Sheets spreadsheet:

function getWeather() {
    var url = 'https://api.openweathermap.org/data/2.5/weather?q=London&appid=YOUR_API_KEY';
    var response = UrlFetchApp.fetch(url);
    var data = JSON.parse(response.getContentText());
    var temperature = data.main.temp - 273.15; // Convert from Kelvin to Celsius
    var sheet = SpreadsheetApp.getActive().getSheetByName('Weather');
    sheet.getRange('A1').setValue(new Date());
    sheet.getRange('B1').setValue(temperature);
}

This script fetches the weather data for London from the OpenWeatherMap API and displays the current temperature in cell B1 of the “Weather” sheet in a Google Sheets spreadsheet.

Conclusion

Google Apps Script allows you to automate tasks and customize your Google Workspace apps in many ways. In this article, we explored four different scenarios where you can use Google Apps Script to enhance your productivity. With Google Apps Script, you can create custom functions, add-ons, automate workflows, and integrate with other apps and services. The possibilities are endless!