Automating tasks in Google Sheets can transform your workflow by saving time, reducing errors, and simplifying complex processes. Whether you're managing data entry, generating reports, or syncing external data, Google Sheets offers powerful tools like macros, Google Apps Script, and add-ons to streamline your work.
Benefits of Automating Google Sheets
Time Savings: Eliminate repetitive tasks like manual data entry or formatting.
Error Reduction: Minimize human errors for accurate data and analysis.
Simplified Workflows: Automate complex processes to make them more manageable.
Scalability: Handle large datasets or recurring tasks effortlessly.
Top Ways to Automate Google Sheets
1. Built-in Functions:
IMPORTRANGE: Import data from another Google Sheet dynamically.
IMPORTHTML, IMPORTXML, GOOGLEFINANCE: Fetch data from websites or financial markets.
Data Validation: Enforce rules to ensure consistent data entry.
2. Google Apps Script: Write custom scripts for advanced automation, such as custom functions, email triggers, or scheduled tasks.
3. Macros: Record repetitive actions for quick playback.
4. Add-ons and Plugins:
Coefficient: Sync data from external sources like Salesforce.
Autocrat: Automate document creation from Sheets data.
Power Tools: Enhance data manipulation with advanced utilities.
Google Sheets Automation with Macros
What are Macros
A macro records a series of actions or keystrokes, which can be replayed to automate repetitive tasks, such as data entry and formatting.
How to Record a Macro in Google Sheets?
Open Google Sheet
Choose a spreadsheet, which you want to automate.
Now, click on Extensions then Macros, and then Record Macro.
4. At the bottom, you have two options - Use absolute references and Use relative references.
Use absolute references - When applying macro use exact location as recorded.
Use relative references - When applying macro use active selection.
5. Now click on save.
👁 GeeksforGeeks The option between Use absolute reference and Use relative references.
6. Record your desired actions in the spreadsheet that you want to automate (formatting, calculations, etc.).
7. Name your macro, assign a shortcut (optional), and click OK.
Example: Formatting a Dataset
Scenario: You want to format a dataset with headers, borders, and conditional formatting for better readability.
Record a macro to:
Freeze the first row (View > Freeze > 1 row).
Apply bold formatting to headers.
Add borders to the range (e.g., A1:C10).
Set conditional formatting (e.g., highlight values > 100 in column B).
Save the macro as "FormatDataset".
Run the macro on new datasets to apply the same formatting instantly.
Example: Auto-Filling Formulas
Scenario: Auto-fill a formula to calculate the sum of two columns (e.g., =B2+C2 in A2).
Using the Fill Handle:
Enter =B2+C2 in A2.
Click the cell, locate the blue square (fill handle) in the bottom-right corner, and drag down to copy the formula to other rows. Google Sheets adjusts cell references automatically (e.g., =B3+C3 in A3).
Using Smart Fill:
Enter data in a pattern (e.g., full names in column A, first names in column B for a few rows).
Select the range where you want Smart Fill to apply.
Press Ctrl+Shift+Y (Windows/Chromebook) or Cmd+Shift+Y (Mac).
If Google Sheets detects the pattern, press Enter to accept the suggestion.
Automate Tasks Using Sheets Plugins and Add-ons
Popular Add-ons for Automation
Coefficient: Sync data from Salesforce and automate reports.
Autocrat: Automate document Power Tools creation.
Enhance data manipulation capabilities.
Examples -
Syncing Data from Salesforce: Use Coefficient to sync Salesforce data to Google Sheets.
Automated Reporting: Schedule automatic report generation with Coefficient.
Data Alerts: Get notified via email or Slack when data changes.
Google Sheets Automation with Apps Script
Google Apps Script is a JavaScript-based scripting language developed by Google. It allows you to automate, extend, and integrate Google services. Here's a simple example of automating Google Sheets -
This script creates a custom menu in your Google Sheets UI. When you click the first item, it shows an alert. You can customize this to suit your needs.
Setting Up Google Apps Script in Google Sheets
Open a fresh Sheet to get to the Apps Script editor.
Go to Extensions > Apps Script.
A new window will pop up with Google Apps Scripts.
Look for the '+' sign near 'Files' in the top-left corner.
Pick 'Script.'
Type a name for your script and hit enter.
Examples
1. Custom Functions -
Let's make a special function to figure out how much something grew as a percentage using two numbers. Start by opening up the script editor in Google Sheets.
Copy this function and stick it in the script editor:
Hit the 'Run' button to get the script going. Go back to your Google Doc and give it a quick refresh. Now you can work out the percentage growth by typing this formula into any cell =((B2-A2)/A2)*100.
This formula calculates the percent change between two values. It subtracts the old value (A2) from the new value (B2), divides by the old value then multiplies by 100 to get a percentage. The result shows how much B2 has grown or shrunk compared to A2. A positive number means growth, while a negative number indicates a decrease.
2. Automated Email Notification -
In this example, we’ll write a script that sends an email notification when a specific cell (e.g., A1) in the Google Sheet reaches a certain value
Open a new file in the script editor.
open the Google Apps script editor
Paste the following function in the editor:
Replace “YOUR SPECIFIC VALUE” with the value you want to check for and “youremail@example.com” with your email.
Run the script.
An authorization window will open.
review your permissions
Click ‘Review permissions.’
Choose your account and click ‘Allow.’
click Allow to authentic account
Set a trigger to run this function at your desired frequency by clicking on the clock icon in the left sidebar of the script editor.
Set a trigger to run this function at your desired frequency
Click on the ‘+ Add Trigger’ button at the bottom.
click on the button to add a trigger
For the function to run, select ‘checkCellValueAndSendEmail.’
click the function you want to add the trigger to
Select ‘From spreadsheet’ as the event source.
select the event source
Click on the event type dropdown and select ‘On change.’
select the event type
Click ‘Save’ to save the trigger.
Now you will receive an email whenever the threshold you defined in the “YOUR SPECIFIC VALUE” variable is crossed.
3. Scheduled Data Refresh -
For our final example, we’ll demonstrate how to automatically update an external data source in your Google Sheet at a predetermined interval. Note: This example assumes you have set up an external data source using =IMPORTDATA(“URL”) or any similar function.
Open Apps Script and create a new script like before.
Paste the following function in the script editor:
Open the script editor.
Paste the following function -
Run your script.
We’ll schedule a data refresh by creating a new trigger.
Select ‘Triggers’ from the Apps Script menu on the left-hand side of the screen.
For our final example, we'll demonstrate how to automatically update an external data source
Click the ‘Add Trigger’ button at the bottom-right corner.
Select the function you want to create a trigger for from the drop-down.
Select the function you want to create a trigger for from the drop-down.
Select ‘Time-driven’ as the event source.
select the event source as time-drive
Choose the frequency with which you want the function to run (e.g., every minute, every 5 minutes, etc.).
Choose the frequency with which you want the function to run
Click ‘Save’ to save the trigger.
Your spreadsheet will now update automatically every day.
