How to Automate Task in Google Sheets

In today's fast-paced world, automating tasks in Google Sheets can significantly boost your productivity and efficiency. By leveraging the power of automation, you can streamline repetitive tasks, reduce errors, and free up time for more strategic activities. This comprehensive guide will show you how to use macros, Google Apps Script, and various add-ons to automate tasks in Google Sheets effectively.

Benefits of Automating Google Sheets

Reduced Time on Repetitive Tasks: Automation helps professionals avoid manual data entry and report generation, allowing more time for strategic work.

Reduce Errors: Automation minimizes human error, ensuring data accuracy and reliable analysis.

Simplified Processes: Automate complex workflows to make them more efficient and easier to manage.

Top Ways to Automate Google Sheets

Built-in Tools

Google Sheets offers many built-in tools to make your work easier:

• Importrange: Import data from other sheets automatically.
• IMPORHTML, IMPORTXML, GOOGLEFINANCE: Fetch data from websites.

Google Apps Script

• Custom Scripts: Create your automation tools using Google Apps Script.
• Scheduled Tasks: Set tasks to run automatically at specific times.

Add-ons and Plugins

• Autocrat: Automate document creation.
• Power Tools: Enhance your data handling capabilities.

Google Sheets Automation Examples

1. Email Reminders: Automatically send email reminders and reports.
2. Data Combination: Merge information from different sheets into one.
3. Web Data Fetching: Update data from websites, such as stock prices or weather reports.

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

1. Open Google Sheet
2. Choose a spreadsheet, which you want to automate.
3. 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.

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.

Also Read

How to Record a Macro in Excel

Examples

1. Formatting a Dataset

Google Sheets users can make their datasets more readable and useful by organizing and arranging the information. They clean up the data, lock header rows, format cells and numbers, use conditional formatting, and wrap text. This process helps people understand and analyze the data better, which leads to better decisions. The main aim is to present information in a way that's easy to grasp and work with.

2. Auto-Filling Formulas

Google Sheets offers users two primary methods to auto-fill formulas -

a. Fill Handle -

Start by putting your formula in the first cell where you want to see the results. Let's say you've got =B1+C1 in cell A1 assuming B1 and C1 have the values you're adding up. Now, click on that cell with your formula. Look for a tiny blue square in the bottom right corner - that's the fill handle. Move your mouse over it and you'll see it turn into a plus sign. Click on that little square and drag it down or across to copy your formula to other cells. Google Sheets is smart enough to change the cell references in your formula as you drag, so it matches the new positions. It's pretty handy!

Pulling the formula down to A2 changes it to =B2+C2 and keeps going like that for the rows below.

b. Smart Fill -

This is a cool feature that's like having a helper for boring tasks. It sees patterns and fills in formulas on its own. It's pretty advanced stuff.

Add your info in a set order. You might put full names in one spot and first names somewhere else. Type out the pattern for a few lines then pick the boxes where you want Smart Fill to finish the job. Hit Ctrl+Shift+Y if you're on Windows or Chromebook, or Command+Shift+Y for Mac users.

If it gets the pattern right just press Enter to say yes to its idea.

3. Data Validation -

Google Sheets offers data validation as a feature. Users can set rules for what info goes into certain cells. This tool helps keep data clean. It cuts down on mistakes and makes spreadsheets more consistent. As a result, people can trust their findings and studies more.

Automate Tasks Using Sheets Plugins and Add-ons

Popular Add-ons for Automation

Coefficient: Sync data from Salesforce and automate reports.

Autocrat: Automate documPower Tools:ent 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 -

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('First item', 'menuItem1')
      .addToUi();
}

function menuItem1() {
  SpreadsheetApp.getUi().alert('You clicked the first menu item!');
}

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

1. Open a fresh Sheet to get to the Apps Script editor.
2. Go to Extensions > Apps Script.
3. A new window will pop up with Google Apps Scripts.
4. Look for the '+' sign near 'Files' in the top-left corner.
5. Pick 'Script.'
6. 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:

function percentageGrowth(oldValue newValue) {
if (oldValue === 0) {
throw new Error("Can't calculate growth percentage when old value is zero.");
}
var growth = ((newValue – oldValue) / oldValue) * 100;
return growth;
}

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:

function checkCellValueAndSendEmail() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var cellValue = sheet.getRange(“A1”).getValue();

  if (cellValue === “YOUR SPECIFIC VALUE”) {

    MailApp.sendEmail(“youremail@example.com”, “Notification from Google Sheets”, “Cell A1 has reached the specified value!”);

  }

}

• Replace “YOUR SPECIFIC VALUE” with the value you want to check for and “[email protected]” 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 -

function refreshData() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var formula = sheet.getRange(“A1”).getFormula();

  sheet.getRange(“A1”).setFormula(“”);

  SpreadsheetApp.flush();  // Waits for all pending changes to be applied

  sheet.getRange(“A1”).setFormula(formula);

}

• 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.

Conclusion -

By using Google Sheets automation, you can increase your productivity and data precision. Macros offer an easy way to automate repetitive jobs, while Apps Script opens possibilities for complex workflows and data handling. Be sure to pick the right method according to your requirements and always put data security and best practices first. With some practice, you can use the power of automation to change your spreadsheets from burdensome tasks to strong instruments that simplify your work and support wise decision-making.