Google Apps Script is a cloud-based JavaScript platform that empowers you to automate and increase the functionality of Google Workspace applications. One of the effective features is its seamless integration with Google Sheets, which makes it an effective tool for anyone looking to streamline the process and save time.


In this blog, you will explore a simple and comprehensive guide on how to work with Google Sheets using Apps Script? Whether you are reading, writing, formatting, and using formulas, you will get all the information you need to know.


Getting Started with Google Apps Script and Google Sheets

If you are a beginner, getting started with App Script is easy. To begin working with Apps Script follow these steps:



  1. Open a Google Sheet.
  2. Click to Extensions then Apps Script to open the Apps Script editor.
  3. Give another name to your project, and start writing your code in the in-built editor.

All done! Let’s dive into some practical use cases and simplify your tasks.


How to Read Data from Google Sheets?

Reading data is the first and common step in automating the task while working on google sheet. Here’s the example which shows how you can retrieve data from a Google Sheet:


Example: Reading Data from a Range


What’s Happening Here:

  1. getActiveSpreadsheet(): Opens the currently active Google Sheet.
  2. getActiveSheet(): Accesses the active sheet.
  3. getRange(): Selects a specific range, such as "A1:C3."
  4. getValues(): Retrieves the data in a 2D array format.

For example, if your range looks like this:

NameAgeCity
Alice25New York
Bob30San Francisco
Charlie22Chicago

The values variable will look like this:



How to Write Data in Google Sheets?

You can also write data in your Google sheets by programming using Apps Script. Here’s the code:


Example: Writing Data to a Range


Key Points:

  1. setValues(): Create a 2D array and give a specific range.
  2. Range Size: Make sure range size and array size should be equal. Means, if the array has 3 rows and 3 columns then the range should be a[3][3].

After running the function, the sheet will look like this:

ProductPriceStock
Laptop120015
Phone80050

Formatting Data in Google Sheets

Formated data is easier to understand and more aesthetically pleasing. Apps Script offers methods to apply styling, such as font color, background color, and bold text.


Example: Formatting a Header Row


Explanation:

  1. setFontWeight("bold"): Makes text bold.
  2. setBackground(): It sets the background color. Use predefined colors or HEX color code.
  3. setFontColor(): Changes the color of text.

After running this code, the header row will have bold text with a light red background and black text.


Other Alternatives for Formatting  :


Using Formulas in Apps Script

Among the most powerful features of Apps Script, the ability to add and manipulate formulas through programming is efficient.


Example: Adding a SUM Formula


What’s Happening:

  1. setValue(): Writes individual numbers into cells A1 and A2.
  2. setFormula(): Inserts the =SUM(A1:A2) formula into cell A3.

After running the script, the sheet will show the sum of A1 and A2 in cell A3 (i.e., 30).


Example: Using Dynamic Formulas

You can use Apps Script to generate dynamic formulas based on the data range.



The above code calculates the average of all numbers in column A and puts the formula below the last row.


Summary

Google Apps Script is an incredibly versatile tool for automating tasks in Google Sheets. In this blog, we covered:


  1. Reading Data: Extract data from a specified range using getValues().
  2. Formatting: Style your sheets programmatically with bold text, colors, and alignments.
  3. Writing Data: Insert 2D arrays into a sheet using setValues().
  4. Formulas: Use setFormula() to create dynamic formulas and automate calculations.

By mastering these extensive functionalities, you can streamline repetitive tasks and unlock the full potential of Google Sheets for your projects.


Conclusion

Google Apps Script is a game changer for automating the tasks in Google Sheets for improving your productivity. Whether you have to read, write, and format the data, the Apps Script helps you to interact with formulas programmatically. By harnessing the power of Google Apps Script, you can streamline repetitive tasks and increase your efficiency in managing your data.


Now it's your turn to practice with the scripts shared above, and don’t hesitate to tailor them to your specific needs. The more you practice with App Script, the more you will find it a valuable tool in your workflow toolbox. Happy scripting!