How to Run Custom JavaScript Code in Google Sheets Using Apps Script

Hi everyone! In this blog post, we are going to explore how we can Run custom JavaScript code in Google Sheets to perform custom calculations, process data to meet our own needs, and do many more things.

Google Apps Script

Google provides Apps Script — a cloud-based scripting language that gives us the ability to run custom JavaScript code in Google Sheets. Apps Script is not a different type of language; it is just JavaScript that Google has abstracted to run with full control and the ability to impose limitations. You can explore more about Google Apps Script by visiting the Google Docs overview page.

We can run Apps Script code using three methods. In all methods, we can run the same code, but it is called in different scenarios and with different permission levels. These methods are known by different names, and they are as follows:

• Custom Functions: These are JavaScript functions that we can use directly, just like built-in sheet functions. For example, =myCustomFunction(...arguments). We can pass arguments into custom functions, such as A1 or A1:D4. These arguments are handled differently in the code: for a single cell like A1, we get the cell value, while for a range like A1:D4, we get a range object. These functions must return a value that is displayed in the calling cell or a range that starts from the calling cell. These functions cannot perform actions that require authorization, like calling a service or editing the sheet using the Spreadsheet class. To learn more, visit the Custom Functions page on Google Docs.
• Custom Menus: We can add custom menus, like Home and Edit, with custom menu items that run different functions written in Apps Script. We cannot pass arguments to these functions, but they do have permission to perform actions that require authorization, like calling services or editing the sheet, because they are manually triggered by the user.
• Triggers: These are functions that are neither used directly in a sheet as functions nor called by menu items. Instead, they are automatically triggered by specific events, such as opening a document, editing a cell, changing a selection, and more.

Before we start exploring how to write Apps Script code, let's first understand what tasks we can accomplish using Apps Script:

• We can create custom functions to perform custom calculations on sheet data.
• We can create custom menus to handle various tasks.
• Fetch data from the internet and display it on the sheet.
• Apply custom formatting to cells based on specific logic.
• Generate report tables based on sheet data.
• Send emails using the Mail service, including data from the sheet.
• And many more tasks that aren't provided by built-in Google Sheets functions.

Let's Get Started Writing Apps Script Code

We can open the Apps Script editor by clicking on Extensions > Apps Script. Once the editor is open, you'll see a page with a file named code.gs. In this file, we can write our functions for custom functions, menus, and triggers. You can also create additional files as needed by clicking on the ➕ button.

When creating functions, you must follow certain rules: function names must be distinct from built-in function names, must not end with an underscore (_) (as this is reserved for private functions), and must follow JavaScript naming conventions. You can call functions from other files within the script project.

The following code shows a simple custom function that calculates the sum of squares for a range of numbers and displays the result in the calling cell:

// Function to calculate the sum of squares
function sumOfSquares(range) {
  let total = 0;
  
  for (let i = 0; i < range.length; i++) {
    for (let j = 0; j < range[i].length; j++) {
      let number = range[i][j];
      
      if (!isNaN(number)) {
        total += Math.pow(number, 2);
      }
    }
  }
  
  return total;  // Return the sum of squares
}

After writing the code, deploy the project by clicking on Deploy > New deployment, selecting the deployment type (e.g., web app), filling in the required information, and deploying the project. Once the project is deployed, we can use this function as a built-in function like =sumOfSquares(A1:A5).

Creating a Custom Menu

We can create custom menus to easily access and run scripts for specific tasks. To do this, we need to include a reserved function in our Apps Script code called onOpen. This function runs every time the sheet is opened. The following code is a simple example of how to create a custom menu named Custom Tools, which contains an item called Square Selected Range.

// This function adds a custom menu to Google Sheets
function onOpen() {
  // Get the spreadsheet UI
  const ui = SpreadsheetApp.getUi();

  // Create a new menu in the toolbar
  ui.createMenu('Custom Tools')  // Name of the custom menu
    .addItem('Square Selected Range', 'squareRange')  // Add another item to run a different function
    .addToUi();  // Add the menu to the UI
}

// Function to square all numbers in a selected range
function squareRange() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getActiveRange();
  const values = range.getValues();

  // Loop through each cell in the selected range
  for (let i = 0; i < values.length; i++) {
    for (let j = 0; j < values[i].length; j++) {
      let number = values[i][j];
      
      // If the value is a number, square it
      if (!isNaN(number)) {
        values[i][j] = Math.pow(number, 2);
      }
    }
  }

  // Set the squared values back in the selected range
  range.setValues(values);
}

This function squares the values of all the cells in the selected range and then displays their respective squares. The setValues() function edits the sheet, and it works because it is triggered by the menu item. If you try to use this function directly as a custom function in the sheet, you will get an error.

With Google Apps Script, we have the power to extend Google Sheets beyond its built-in capabilities. Whether we're performing custom calculations, automating workflows, or integrating external services, Apps Script enables us to tailor our spreadsheets to suit our unique needs. So, Let's dive into the code, experiment with new ideas, and make our Google Sheets work smarter for us!

I hope you found this post on creating custom functions and menu items with Google Apps Script both informative and inspiring. If you have any questions or need further assistance, feel free to comment. Happy coding!