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:
- Open a Google Sheet.
- Click to Extensions then Apps Script to open the Apps Script editor.
- 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
function readData() {
// Open the active sheet
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Get data from a specific range (A1 to C3)
const range = sheet.getRange("A1:C3");
const values = range.getValues();
// Log the data
console.log(values);}
What’s Happening Here:
- getActiveSpreadsheet(): Opens the currently active Google Sheet.
- getActiveSheet(): Accesses the active sheet.
- getRange(): Selects a specific range, such as "A1:C3."
- getValues(): Retrieves the data in a 2D array format.
For example, if your range looks like this:
Name | Age | City |
Alice | 25 | New York |
Bob | 30 | San Francisco |
Charlie | 22 | Chicago |
The values variable will look like this:
[
["Name", "Age", "City"],
["Alice", 25, "New York"],
["Bob", 30, "San Francisco"],
["Charlie", 22, "Chicago"]
]
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
function writeData() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Data to write
const data = [
["Product", "Price", "Stock"],
["Laptop", 1200, 15],
["Phone", 800, 50]
];
// Write data to the range starting at cell A1
sheet.getRange(1, 1, data.length, data[0].length).setValues(data);
}
Key Points:
- setValues(): Create a 2D array and give a specific range.
- 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:
Product | Price | Stock |
Laptop | 1200 | 15 |
Phone | 800 | 50 |
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
function formatHeader() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Select the header row
const headerRange = sheet.getRange("A1:C1");
// Apply formatting
headerRange.setFontWeight("bold");
headerRange.setBackground("#f4cccc");
headerRange.setFontColor("#000000");
}
Explanation:
- setFontWeight("bold"): Makes text bold.
- setBackground(): It sets the background color. Use predefined colors or HEX color code.
- 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 :
Set text alignment:
headerRange.setHorizontalAlignment("center");
Set column width:
sheet.setColumnWidth(1, 200); // Set column A to 200 pixels wide
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
function addSumFormula() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Set data in cells
sheet.getRange("A1").setValue(10);
sheet.getRange("A2").setValue(20);
// Insert a formula to calculate the sum
sheet.getRange("A3").setFormula("=SUM(A1:A2)");
}
What’s Happening:
- setValue(): Writes individual numbers into cells A1 and A2.
- 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.
function dynamicFormula() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // Define the range dynamically
  const lastRow = sheet.getLastRow();
  sheet.getRange(lastRow + 1, 1).setFormula(`=AVERAGE(A1:A${lastRow})`);
}
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:
- Reading Data: Extract data from a specified range using getValues().
- Formatting: Style your sheets programmatically with bold text, colors, and alignments.
- Writing Data: Insert 2D arrays into a sheet using setValues().
- 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!