How to automate google sheets with macros

Google Sheets lets you record macros that duplicate a specific series of UI interactions that you define. Once you've recorded a macro, you can link it to a keyboard shortcut in the form Ctrl+Alt+Shift+Number . You can use that shortcut to quickly execute the exact macro steps again, typically in a different place or on different data. You can also activate the macro from the Google Sheets Tools > Macros menu.

When you record a macro, Google Sheets automatically creates an Apps Script function (the macro function) that replicates the macro steps. The macro function is added to an Apps Script project bound to the sheet, in a file titled macros.gs . In the event that there is already a project file bound to the sheet with that name, the macro function is appended to it. Google Sheets also automatically updates the script project manifest, recording the name and keyboard shortcut assigned to the macro.

Since every recorded macro is defined entirely within Apps Script, you can edit them directly within the Apps Script editor. You can even write macros from scratch in Apps Script, or take functions you've already written and turn them into macros.

Creating macros in Apps Script

You can take functions written in Apps Script and use them as macro functions. The easiest way to do this is by importing an existing function from the Google Sheets editor.

Alternatively, you can create macros within the Apps Script editor by following these steps:

  1. In the Google Sheets UI, select Tools > Script editor to open the script bound to the sheet in the Apps Script editor.
  2. Write the macro function. Macro functions should take no arguments and return no values.
  3. Edit your script manifest to create the macro and link it to the macro function. Assign it a unique keyboard shortcut and name.
  4. Save the script project. The macro is then available for use in the sheet.
  5. Test the macro function in the sheet to verify that functions as intended.

Editing macros

You can edit macros attached to a sheet by doing the following:

  1. In the Google Sheets UI, select Tools > Macros > Manage macros.
  2. Find the macro you want to edit and select more_vert > Edit macro. This opens the Apps Script editor to the project file containing the macro function.
  3. Edit the macro function to change the macro behavior.
  4. Save the script project. The macro is then available for use in the sheet.
  5. Test the macro function in the sheet to verify that functions as intended.

Importing functions as macros

If there is already a script bound to a sheet, you can import a function in the script as a new macro and then assign it a keyboard shortcut. You can do this by editing the manifest file and adding another element to the sheets.macros[] property.

Alternatively, follow these steps to import a function as a macro from the Sheets UI:

  1. In the Google Sheets UI, select Tools > Macros > Import.
  2. Select a function form the list presented and then click Add function.
  3. Select clear to close the dialog.
  4. Select Tools > Macros > Manage macros.
  5. Locate the function you just imported in the list. Assign a unique keyboard shortcut to the macro. You can also change the macro name here; the name defaults to the name of the function.
  6. Click Update to save the macro configuration.

Manifest structure for macros

The following manifest file example snippet shows the section of a manifest that defines Google Sheets macros. The sheets section of the manifest defines the name and keyboard shortcut assigned to the macro and the name of the macro function.

See the Manifest structure guide for more details on how Apps Script manifests are constructed. The Sheets and Macro sections describe the fields that define Sheets macros.

Best practices

When creating or managing macros in Apps Script, it is recommended that you adhere to the following guidelines.

  1. Macros are more performant when they are light-weight. Where possible, limit the number of actions a macro takes.
  2. Macros are best suited for rote operations that need to be repeated frequently with little or no configuation. For other operations, consider using a custom menu item instead.
  3. Always remember that macro keyboard shortcuts must be unique, and a given sheet can only have ten macros with shortcuts at any one time. Any additional macros can only be executed from the Tools > Macros menu.
  4. Macros that make changes to a single cell can be applied to a range of cells by first selecting the full range and then activating the macro. This means it is often unnecessary to create macros that duplicate the same operation across a predefined range of cells.

Things you can’t do

There are a few restrictions on what you can do with macros:

Use macros outside bound scripts

Macros are defined in scripts bound to specific Google Sheets. Macro definitions are ignored if defined in a standalone script or web app.

Define macros in Sheets add-ons

You cannot distribute macro definitions using a Sheets add-on. Any macro definitions in a Sheets add-on project are ignored by users of that add-on.

Distribute macros in script libraries

You cannot distribute macro definitions using Apps Script libraries.

Use macros outside of Google Sheets

Macros are only a feature in Google Sheets, and do not exist for Google Docs, Forms, or Slides.

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 4.0 License, and code samples are licensed under the Apache 2.0 License. For details, see the Google Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

A macro is one of the simplest ways to automate repetitive tasks in Google Sheets. Using a macro, you can record a set of actions and then reuse them in the future with just a single click. Macros enable you to harness the power of Google Apps Script even if you do not know how to code!

Your browser does not support HTML5 video. Here is a link to the video instead.

For example, let’s imagine that you are building a simple compound interest calculator in Google Sheets. The calculator accepts three parameters as input: principal amount, time period, and interest rate as input. The output is the final amount at maturity.

You’re about to share this spreadsheet with your friends and family but you want to first format the input cells and output cells clearly so they’ll know which cells they can tweak based on their needs.

You come up with the following convention:

The input cells will have a light yellow background and input values will be dark gray in color and emboldened.

The output cell will have a light green background and the output value will be dark green in color and emboldened.

Since there are three input cells, you’ll have to apply the same set of formatting rules three times. Instead of repeating the same set of actions multiple times, you can record these actions as a macro the first time and then apply them going forward with a single click.

In this tutorial, I will show you how to record and use macros in Google Sheets.

How to record a macro in Google Sheets?

Step 1 — First select an input cell or range that you will format manually

While this tutorial focuses on formatting cells, you can also use macros to automate other types of actions like sorting.

Step 2 — Begin recording a macro

Step 2.1 — Select Tools —> Macros —> Record macro

Step 2.2 — Choose absolute references OR relative references

Use absolute references if the macro only applies to the currently selected cell or range. If you record a macro using absolute references, running the macro will only make changes to those cells (or ranges) that were selected when the macro was recorded.

Use relative references if you want the macro to apply to whichever cell or range is selected at the time the macro is run.

In this tutorial, we will use relative references since we want the macro to apply formatting to whichever cell is currently selected.

Step 3 — Apply formatting changes to the cell while the macro is being recorded and then save the macro

Your browser does not support HTML5 video. Here is a link to the video instead.

How to use a previously recorded macro in Google Sheets?

To apply a previously recorded macro to a cell or range, select it and then select the macro from the Tools —> Macros menu.

Step 1 — Select the cell or range you want to apply the macro to

Step 2 — Select the macro from the Tools —> Macros menu

When you run a macro for the first time, you will be asked to grant the macro access to your spreadsheet. This is a security feature in Google Sheets.

That’s it! The cell or range you selected will be formatted by the maco. All it requires is a single click.

Your browser does not support HTML5 video. Here is a link to the video instead.

Macros are essentially just Google scripts that are auto-generated for you

When you record a macro, Google Sheets is essentially creating a Google Apps Script for you based on the actions that you took.

To view the auto-generated code, select Tools —> Macros —> Manage macros and then select Edit script from the three-dots menu.

Your browser does not support HTML5 video. Here is a link to the video instead.

Here is the code that was auto-generated for the macro we recorded in this tutorial:

The /** @OnlyCurrentDoc */ tells Google Apps Script that the macro only applies to the current Google Sheets spreadsheet.

If you want to find out how to do something in Apps Script, simply record a macro while the change manually and study the Apps Script code that is auto-generated for you. This can be a great way for you to improve your Apps Script knowledge!

Conclusion

In this tutorial you learned how to use macros to automate repetitive tasks in Google Sheets. You don’t even have to write Apps Script code! Simply begin recording a macro, perform a set of actions manually and save the maco. That’s it! You can then apply those same set of actions in the future by running the macro that you recorded.

Macros are essentially just Google scripts behind the scenes so you can use them to learn how to implement some functionality in Apps Scripts. Simply record a macro and then look at the code that was auto-generated for you.

Macros are a powerful and underutilized tool in Google Sheets. You can get the benefit of Apps Scripts without even knowing how to code!

Hope this tutorial gave you ideas for things you can automate to become more productive in Google Sheets. Thanks for reading!

I’d appreciate any feedback you can give me regarding this post.

Was it useful? Are there any errors or was something confusing? Would you like me to write a post about a related topic? Any other feedback is also welcome. Thank you so much!

Google Sheets lets you automate repetitive tasks with macros, and you can then bind them to keyboard shortcuts to execute them quickly. They work using Google Apps Script to capture your actions for later use.

What are Macros?

A macro—or macroinstruction—is a specific sequence of actions that let you automate a series of steps to increase productivity. They work by recording your actions and saving them into a file that’s bound to the spreadsheet in which they were recorded.

When you record a macro in Google Sheets, it automatically creates an Apps Script with all the code to replicate your actions for you. This means you can create complex macros without knowing how to write code. The next time you run it, Sheets will do everything you did when you recorded the macro. Essentially, you’re teaching Google Sheets how to manipulate a document to your liking with a single command.

Macros are a powerful feature that can do basically anything Sheets is capable of doing. Here are just a few examples of its functionality:

  • Apply formatting and styles.
  • Create completely new spreadsheets.
  • Use any Google Sheets function, toolbar, menu, or feature.

The sky is the limit.

How to Record a Macro in Google Sheets

Fire up a Google Sheet and click Tools > Macros > Record Macro.

This opens the recording menu at the bottom of the window, with two options for capturing your actions:

  • Absolute References: The macro will do tasks only on the exact cells you record. If you italicize cell B1, the macro will only ever italicize B1 regardless of what cell you clicked on.
  • Relative References: The macro will do tasks on the selected cells, regardless of where they are in the sheet. If you italicize B1 and C1, you can re-use the same macro to italicize cells D1 and E1 later.

Select whether you want an absolute or relative reference, then you can start to click, format, and teach Sheets what order you want these actions to replicate.

After you’ve captured all the actions for this macro, click “Save.”

Enter a name for your macro. Google also lets you create shortcuts for up to ten macros. If you want to bind a macro to a keyboard shortcut, enter a number from 0-9 in the space provided. When you finish, click “Save.”

If you need to tweak your macro’s name or shortcut, you can edit a macro by clicking Tools > Macros > Manage Macros.

From the window that opens, tweak as desired and then click “Update.”

The next time you press the shortcut associated with the macro, it will run without having to open the macro menu from the toolbar.

How to Run a Macro in Google Sheets

If your macro is an absolute reference, you can run the macro by pressing the keyboard shortcut or go to Tools > Macros > Your Macro and then click the appropriate option.

Otherwise, if your macro is a relative reference, highlight the cells in your spreadsheet on which you want the macro to run and then press the corresponding shortcut, or click on it from Tools > Macros > Your Macro.

How to Import Macros

As mentioned earlier, when you record a macro, it gets bound to the spreadsheet on which you recorded it. But what if you want to import a macro from another spreadsheet? While it’s not a straightforward and simple task, you can do it using this little workaround.

Because recorded macros are stored as functions in Google Apps Script, to import a macro, you need to copy the function and then paste it in the new sheet’s macro file.

Open the Google Sheet with the macro you want to copy and then click on Tools > Macros > Manage Macros.

Next, click the “More” icon next to the macro you’d like to copy and then click “Edit Script.”

All macros save to the same file, so if you have a couple of macros saved, you may have to sift through them. The function’s name is the same one you gave it when you created it.

Highlight the macro(s) you want to copy, then press Ctrl + C. Be sure to copy everything up to and including the closing semi-colon.

Now, open the other spreadsheet you’ll be importing the macro to and click Tools > Macros > Record Macro.

Immediately click “Save” without recording any actions to create a placeholder function in the sheet’s macro file for us. You’ll be deleting this a little later.

Click “Save” again.

Open Google Apps Script by clicking Tools > Script Editor, and then open the macros.gs file from the left pane. Delete the existing function and then press Ctrl + V to paste in the macro from your other Sheet.

Press Ctrl + S to save the script, close the tab, and return to your spreadsheet.

Your spreadsheet reads the macros.gs file and looks for changes made to it. If a new function is detected, you can use the Import feature to add a macro from another sheet.

Next, click Tools > Macros > Import.

Finally, click “Add Function” under the macro you want to add.

Unfortunately, you will have to bind the macro manually to a keyboard shortcut again. Just follow the instruction previously mentioned, and you’ll be all set to use this macro across multiple sheets.

That’s all there is to creating and using macros in Google Sheets. There’s no limit to the routines you can create for your spreadsheets to limit the amount of time you spend doing these repetitive tasks.

Use Google sheets to automatically schedule macros – easier than Excel and free! Learn how to schedule an email!

Google Sheets is a perfect modern and easy alternative to Excel. If you want to schedule a spreadsheet task to be automatic, I recommend you first look into this alternative before using Excel and Task Scheduler.

Two main reasons:

Modern scripting language and editor

Cloud – means your computer can be off and is reliable. No random crashes.

Steps

1. Build the Sheet

2. Build the Script

3. Set the Triggers

4. Done

How to automate google sheets with macros

In this tutorial, you will learn how to setup a script that copies and emails data, and trigger it to fire at a specific time. I have personally used this a lot.
I give someone a tablet for data entry, they finish for the day, and I get the results emailed to me at night. It’s fantastic.

If you master this, you can automate tasks and truly forget about them due to the Google machine reliably churning in the background.

Create a blank Google Sheet – name the sheet anything you like https://docs.google.com/spreadsheets/

Create two sheets “Data Entry” and “Data Storage”.

Go to sheet “Data Entry”

Add the date to C2

Add headers to C3 and C4 (“Hour” and “Sales” in my case)

Format table as you see fit, but to follow along exactly make sure it is C4:D12 excluding headings.

Add a heading to B3 called “Hidden Columns”, fill the cells down to B12 with $C$2. This will be hidden, but when we copy the data to “Data Storage”, I like to do this so it is easy to index the data for historical reports – especially if you have 1000s of entries.

You should have something similar to the below:

How to automate google sheets with macros

Feel free to deviate from the tutorial in regards to the design – all you will have to do is change the ranges around later in the script. Which is no problem.

My idea for the above is to have someone enter into column D. The data will then be copied and cleared from the form at the end of every day.

Tools – Script Editor. This will open a new tab.

How to automate google sheets with macros

2. Top Left, name the script anything of your choosing.

How to automate google sheets with macros

3. Delete all the current code, and copy in:

4. After changing any ranges or sheet names (if you are copying the tutorial exactly, you will be fine), click the “Debug” button right next to the play button.

5. Follow the prompts to approve authorization. This is overkill by Google.

6. Now click the play button. Your data should be copied and deleted! Run it again, and your new data is copied under the last! (you can also format the Storage page if you care)

How to automate google sheets with macros

How to automate google sheets with macros

This is very easy! Go back to the Google Script page.

Next to the play icon, click the clock icon “Current project’s Triggers”

Click on “No triggers set up. Click here to add one now.”

How to automate google sheets with macros

4. Leave everything to default, but play around with the “Hour Timer” and “Every hour” box as desired. I changed mine to day timer, 6pm to 7pm.

How to automate google sheets with macros

5. Click Save, and then save the script as well. You are now all done. Easy as that.
(Note – trigger time doesn’t change timezone to match your country, so you just have to have a play around if you are outside the US).

Head back to Google Scripts.

Click on Resources-Libraries

We need to add something that changes our range to strings and make it HTML friendly, this cool library “SheetConverter” does it for us. Copy in 1kL7pHe3Ru9hCcc1YXJTdPCuPRo-rDzH9fi3–QF8TE7AxIrQoJXZht8A and click Add

Change to highest version (I’m using 6) and click save.

How to automate google sheets with macros

5. Now we need to add in a function that uses the library we just added to change our range to HTML, then send the email. Copy in the following code, which includes the copy code we created above.

6. Click “Run” – ”Run Function” and run the copy script. You will have to review permissions again.

7. Assuming you have changed the script to your email, you should now get an email after you run it!

How to automate google sheets with macros

The format conversion is never perfect, but it does a good enough job for most of my uses. Don’t forget to setup the trigger again if you want to use it.

Until I turn it off, my script will be copying data and sending emails indefinitely. It’s easy quick, and robust.
On top of this, there is a ton of practical uses for this platform. Labour tracking, inventory, collaboration, reporting, time keeping to name a few. And it’s all free!

If all your data is on Excel, follow my Excel to Google Sheets tutorial. Also, read about Excel Macro Scheduling!
Another cool use of the above, it to make an alert system that monitors your shares/stocks/etfs. Check out my Google Sheets Stock Dashboard and see if you can do it!
If you want to automatically get your Google Sheet data to Excel, check out the tutorial Link Google Sheets to Excel.

In this post, I’ll teach you how to automatically email a Google Sheet as a PDF attachment from within Google sheets itself. Sending emails with attachments is a very common use case in the business world. Just ask any analyst – they probably spend hours every day putting together and emailing spreadsheet based reports to various stakeholders.

A typical workflow might look like this:

There is usually a spreadsheet that contains some raw data.

A report is created either in a document or in the spreadsheet itself as a separate tab.

The report is exported as a PDF.

The PDF is emailed to various stakeholders.

In this post, I’ll show you how to automate the above workflow. Especially steps 3 and 4. Whether you can automate the first two steps will depend on your specific use case.

Consider the following spreadsheet where I have data on one sheet and a report template in another.

The Data sheet has the monthly sales numbers.

The Report sheet has a template that summarizes the key info the CEO cares about.

The goal is to send out the report as a PDF attachment from within Google sheet itself. It’s very easy to do that using Apps Script. First open the script editor by selecting Tools → Script editor. Then copy paste the following code into the editor.

Please replace the email address in the code snippet below with your email address.

The message object has a property called attachments that is used to specify an array of files to attach with the email message.

The following code exports the spreadsheet as a PDF file named “Monthly sales report”.

To run the script, click the play icon.

When you run it, Google will ask you to authorize the script to access your data and send out the email. Once you authorize it, you’ll receive an email like the one below.

There’s one problem though, the attached PDF also includes the sheet containing the raw data that was used to generate the report. We don’t want that. We only want to attach the sheet called “Report”.

One way to solve this is to first hide the data sheet using code, send out the email and then unhide the data sheet using code.

Here’s the code to do that:

Please replace the email address in the code snippet below with your email address.

The following line hides the sheet called “Data”.

Then this other line in the code unhides it after sending the email.

Now, the attached PDF only contains the sales report and the raw data isn’t included anymore. It looks really nice too! Here’s a preview of the attached PDF from within Gmail.

The final step is to make it possible so this report can be emailed with just a single click. The easiest way to do that is to insert a button (using a drawing) into the data sheet and assign the sendReport script to that drawing.

Insert a new drawing in the data sheet by selecting Insert→Drawing.

Then, draw a button like the one below and insert it into your sheet by clicking Save and Close.

Once you save your drawing, you should see the button in your sheet.

The final step is to assign the sendReport script to this button so that clicking the button will run the script.

Click the “three dots menu” (it will appear if you move your mouse over the button) and select Assign script.

Then, enter sendReport in the popup window and click OK.

Now, whenever you click the button, the sendReport script will be run and the email will be sent out with the report attached as a PDF.

Summary

In this post, you learned how to automate emailing a Google Sheet as a PDF attachment.

I’d appreciate any feedback you can give me regarding this post.

Was it useful? Are there any errors or was something confusing? Would you like me to write a post about a related topic? Any other feedback is also welcome. Thank you so much!

It can be useful to automate some tasks in Chrome when you have to do the same thing repeatedly. This not only will save time but also improve your productivity. Chrome by default offers automations like auto filling. In addition, there are also extensions that enable you to record and repeat actions on your browser without intervention. In this article, we will explore how to automate tasks in Google Chrome using default features and using extensions.

Default Automation Features in Chrome

Below are some of the tasks Chrome will automate without any extensions:

    – Chrome offers powerful password management which will help you to save all your browsing related passwords in one place. – you can auto fill the saved password, credit card information, names and addresses. – you can directly open the PDFs without the need of going to downloads folder and open.
  • Chrome will automatically offer translation when you view webpages in other languages.
  • Setup multiple pages to open them on startup every time when launching the browser.

Why Do You Need Extensions?

All the default automation features in Chrome are simple in nature. However, you may be interested in recording your activities and repeat again. This kind of automation needs external tools for which you need to find suitable extensions in Chrome. Here, we will explain automating tasks in Chrome with two extensions. However, you have many extensions available for doing this stuff.

Automate Tasks Using Chromium Browser Automation (CBA)

Chromium Browser Automation, CBA in short, is an automation extension for Chrome with a simple interface. It allows to record your actions while navigating the browser including clicking, filling forms and much more. The steps are set up in code injections that can access the web page DOM as well as be exported or imported for use in other devices.

First, you need to visit the Chrome Web Store and search for and add the extension to your Chrome.

How to automate google sheets with macros

Understanding CBA Extension’s Features

Click the extension on the Chrome toolbar to open and then the plus “+” sign to add a project or select an existing one under the “Project” tab.

How to automate google sheets with macros

  • Recording actions – “rec” button helps you to record simple actions like button clicking, filling of forms on your browser. The extension saves these activities and it can repeat the process on its own. Once you are done recording, click the “stop” button.
  • Playing actions – “play” button helps you to repeat the recorded actions on the browser. Note that you may not see the screenplay but the extension will execute the actions in the background.
  • Code Injection is a powerful feature for web developers and other expert users. It allows them to inject executable functions (scripts) to a site page for testing and debugging among other actions.

How to automate google sheets with macros

  • To import/ export an injection, right-click the CBA icon and select “Options”. This will open a new interface in a new tab where you can edit your interactions, import or export scripts.

Automate Tasks in Chrome using iMacros for Chrome

iMacros is another interesting extension for Chrome which allows you to record any repetitive activities on the browser. With iMacros, you can easily navigate web pages, fill in online forms and so on. Moreover, you can save these macros for later use even on other browsers such as IE. To add and use iMacros Chrome extension, go to the Chrome Web Store, search for and add “iMacros for Chrome” to Chrome browser.

How to automate google sheets with macros

  • Now click the extension’s icon on Google Chrome toolbar to access the user interface.
  • The first section contains the recorded macros and iMacros demos. To record a macro for your activities, click the “Record” button and select “Record Macro” to start.

How to automate google sheets with macros

  • Perform the actions you want to record and when done, click “Stop” and save the macro with the .iim file extension.
  • Switch to the “Play” tab, select the file you saved on the top section and then click “Play Macro” to repeat the actions automatically.
  • You may also select the option to play the macros in a loop by clicking the “Play loop” button.
  • Under the “Manage” tab, you can customize the iMacros extension from the “Settings” or select a saved macro file and click “Edit Macro” to make and save changes on the file.

How to automate google sheets with macros

Interesting features of iMacros Extension

iMacros can be used to automate more advanced tasks such as testing of web apps, data extraction as well as working on multiple web pages.

  • Compatibility with other tools – iMacros can be used with other extensions such as ad blocking extensions to improve the speed of the browser.
  • Data Mining/ Extraction – the extension can extract information from sites and fill the data in forms or spreadsheets. You can even add data or collect data from Excel or Google Spreadsheet.
  • Web Testing – This is a useful feature for web developers to save time when performing lengthy user experience tests, speed tracing and web transaction monitoring.
  • Built-in Automation Templates – Another useful element is the built-in macros that you can customize to suit your needs. You can file these macros under the “Demo-Chrome” folder in the top panel of the iMacros interface.

How to automate google sheets with macros

Sharing Macros

Sharing Macros – iMacros allows you to save the macro in a file, add to your bookmarks or embed on your website to share with other users within your network.

  • Right-click a macro from the top section and select “Edit” or go to the “Manage” tab and click “Edit Macro” to open the editor.
  • At the bottom of the editor, click the “Save As” option.
  • This takes you to another pop-up. Select to save as a file or a bookmark and then you can share this within your network.

How to automate google sheets with macros

Wrapping Up

The level of automation in Chrome really depends on your requirement. Using the default auto fill or saving passwords don’t need any special skill. Though extensions can help you to record and repeat the steps, it is still a complex task.

If I have to do something twice, I usually try to find a way to automate or streamline it. Why? Because if I spent five minutes every single day sorting or moving rows from one spreadsheet to another then that adds up to 30 hours a year. If you have a few different spreadsheets that you’re trying to keep organized, think about how quickly that 30 hours a year could multiply.

How to add a custom script to Google Sheets

In the top navigation, select Tools > Script Editor to open the script editor window, then click File > New > Script File to open a new script file.

⚠️WARNING: If you directly copy & paste formulas from this post – make sure you doublecheck the formatting on quotes and doublequotes – copying from a webpage could alter the formatting and cause the script to break. You may need to replace quotes and doublequotes while you’re in the script editor.⚠️

Auto-sorting by date or priority

I use this script in my to-do lists, client status sheets, and review sheets to automatically organize rows by priority or due date, ensuring that the most time-sensitive item is at the top of the sheet.

This function auto-sorts the given range A2:H30 within the sheet name Review Tracker by column 5 (the column where I keep due dates).

var sheet = SpreadsheetApp.getActiveSheet();

if (sheet.getName() == ‘ Review Tracker ‘) <

var editedCell = sheet.getActiveCell();

var columnToSortBy = 5 ;

var tableRange = “ A2:H30 “; // What to sort

// column A = 1, B = 2, etc…

var range = sheet.getRange(tableRange);

Moving rows to a separate tab upon completion

This is a script I use for client status sheets or review sheets to automatically move one row from an in-progress sheet to a completed sheet.

This function watches column 7 in the sheet Review Tracker for the value Complete and then moves the row to the sheet Completed Reviews when that value appears.

// moves a row from a sheet to another when a magic value is entered in a column

// adjust the following variables to fit your needs

var sheetNameToWatch = “ Review Tracker “;

var columnNumberToWatch = 7 ;

// column A = 1, B = 2, etc…

var valueToWatch = “ Complete “;

var sheetNameToMoveTheRowTo = “ Completed Reviews “;

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = SpreadsheetApp.getActiveSheet();

var range = sheet.getActiveCell();

if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) <

var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);

var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);

Automated email updates for new rows

This is a script I use for reviews. Whenever a team member adds a new row into my review tracker spreadsheet and marks it as Ready to Review, an email is automatically sent to my inbox that includes: Name, Client Name, Item Being Reviewed, Link to the Item Being Reviewed, Deadline, Notes, and Status.

This function watches the status for the value Ready to Review . When that value is found, it adds the value sent to column 8 and sends out an email with specified row information (found in the set HTML template for information message section). If the sent value is already in column 8 , the function skips to the next row that features both Ready to Review and a blank column 8 .

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

if (ActiveSheet.getName() == ‘ Review Tracker ‘) <

var StartRow = 2 ;

var RowRange = ActiveSheet.getLastRow() – StartRow + 1;

var WholeRange = ActiveSheet.getRange( StartRow,1,RowRange,7 );

var AllValues = WholeRange.getValues();

for (i in AllValues) <

//set current row

var CurrentRow = AllValues[i];

if (CurrentRow[ 6 ] == “ Ready for Review ” && CurrentRow[ 7 ] != “ sent “) <

//define column to check if sent

// column A = 1, B = 2, etc…

//var EmailSent = CurrentRow[ 8 ];

//if row has been sent, then continue to next iteration

//set HTML template for information

//set the row to look at

var setRow = parseInt(i) + StartRow;

// column A = 1, B = 2, etc…

ActiveSheet.getRange(setRow, 8).setValue(“ sent “);

>//if review ready

//define who to send emails to

var SendTo = “ [email protected] “;

//set subject line

var Subject = “ New Deliverable to Review “;

//send the actual email if message is not empty

>//if sheetName Review

Using multiple scripts

If you’re running multiple scripts you’ll need to adjust so that they all have unique names. In my example below, the function onEdit calls each function, titled myFunction1 and myFunction2. If you have multiple functions with the same name, Google will only run the last function unless you differentiate.

Below is an example of combining the Auto-Sort and Move Upon Completion functions above into a single script.

var sheet = SpreadsheetApp.getActiveSheet();

if (sheet.getName() == ‘Review Tracker’) <

var editedCell = sheet.getActiveCell();

var columnToSortBy = 5;

var tableRange = “A2:H30”; // What to sort

// column A = 1, B = 2, etc…

var range = sheet.getRange(tableRange);

// moves a row from a sheet to another when a magic value is entered in a column

// adjust the following variables to fit your needs

var sheetNameToWatch = “Review Tracker”;

var columnNumberToWatch = 7;

// column A = 1, B = 2, etc…

var valueToWatch = “Complete”;

var sheetNameToMoveTheRowTo = “Completed Reviews”;

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = SpreadsheetApp.getActiveSheet();

var range = sheet.getActiveCell();

if (sheet.getName() == sheetNameToWatch && range.getColumn() == columnNumberToWatch && range.getValue() == valueToWatch) <

var targetSheet = ss.getSheetByName(sheetNameToMoveTheRowTo);

var targetRange = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

sheet.getRange(range.getRow(), 1, 1, sheet.getLastColumn()).moveTo(targetRange);

Adding project triggers

In order for your scripts to run, you’ll need to add a trigger. Triggers allow your scripts to execute upon certain events like opening the spreadsheet, editing the spreadsheet, etc.

To add a project trigger, click on the trigger (clock) icon to open the project trigger popup. From here you can add a new trigger and select which function should run and what event should trigger the function to execute.

How to automate google sheets with macros

Tracking basic data in a spreadsheet is pretty simple — it’s remembering to update your data each day, week or month that can be a problem. But why update manually when you can take advantage of some simple automation tools?

Here’s a step-by-step guide to creating an automatically updating Google spreadsheet to track data. We’ll use “number of Twitter followers” in this example, but the code can be easily tweaked to fetch and store other data as well.

How to automate google sheets with macros

Create a Google spreadsheet

Head to Google Drive and create a blank spreadsheet (if you don’t yet have a Google account, you can sign up there). Title the sheet anything you want, and you’re ready to begin. (The default will be “Untitled spreadsheet,” but you can click on that to change it to something that will better identify it in your list of documents.)

How to automate google sheets with macros

Add your first formula

Spreadsheet formulas can do more than math calculations; they can also extract data from URLs. Since you may want to track more than one page — not only yours but competitors’ — we’ll set it up to be easily scalable.

In the first column, list the Twitter accounts you want to track. The second column will be a formula for a Twitter Web Intent page.

What’s a Twitter Web intent page? Instead of the more HTML-complex profile page, which you might find at a URL like https://twitter.com/Computerworld, Web Intent pages are designed for developers who want to put Twitter modules on their pages. The format is https://twitter.com/intent/user?screen_name=TWITTERHANDLE , so the spreadsheet formula should be:

=CONCAT(“https://twitter.com/intent/user?screen_name excerptSlick slick-slide next-page” itemscope itemtype=”http://schema.org/MediaObject” itemprop=”associatedMedia” data-section-name=”slide4″>

How to automate google sheets with macros

Write a spreadsheet function

Ideally, it would be best to use the Twitter API to pull in this data. However, to use the Twitter API, or any other API that requires authorization for use, you’d need to set up OAuth2 authorization for your spreadsheet. That’s a bit outside the scope of this tutorial; but if you’re interested, Google has an Oauth2 library for Google Apps Script.

Since I don’t have space to include these instructions, I’ll take an easier way out and extract number of followers from the Web Intent page. Google Sheet’s IMPORTXML function lets you extract specific HTML using XPath queries.

XPath is a fairly complex language to learn, but SelectorGadget makes it easy to point-and-click your way to finding XPath for specific data on an HTML page.

This is what SelectorGadget shows for an XPath query: //*[contains(concat( ” “, @class, ” ” ), concat( ” “, “count”, ” ” ))]//*[contains(concat( ” “, @class, ” ” ), concat( ” “, “alternate-context”, ” ” ))] . However, because the Google Sheets function uses double quotation marks to enclose arguments, you’ll first need to change all of the XPath double quotes to single quotes, and then enter a formula like this for your followers column:

=ImportXML(B2, “//dl[(((count(preceding-sibling::*) + 1) = 1) and parent::*)]//*[contains(concat( ‘ ‘, @class, ‘ ‘ ), concat( ‘ ‘, ‘alternate-context’, ‘ ‘ ))]”)

Again, click-and-drag that down the rest of your column.

How to automate google sheets with macros

Auto-save fetched data

Use this formula in the rest of column C, and values will automatically fill in for other accounts you’re tracking. However, those values won’t be saved; they’ll change each time you open the sheet.

To keep historical data as the sheet’s currently designed, you’d need to copy and paste values manually into another column or spreadsheet. What fun is that? Instead, let’s create a new function to 1) Find the first empty column, 2) label the column with the date of data extraction, and 3) copy the value from column C into that first empty cell.

How to automate google sheets with macros

Write a function to store data

To store data, we’ll need to create a spreadsheet function. Head to Tools > Script Editor to create functions for the spreadsheet. You’ll see a default function pop up called myFunction.

How to automate google sheets with macros

Save the date

You’ll want to know when the data was fetched and stored. So, put the current date that the function runs in row 1 of your first empty column. This command:

sheet(1,numColumns + 1).setValue(new Date());

will set the value of row 1 of the first column without any data — cell 1, numColumns + 1 — to the current date and time.

Then loop through the rest of the cells in the column with your latest data by using the for loop at left.

Full function code:

How to automate google sheets with macros

Function to store Twitter followers

Change that to storeFollowers() and use the following code

Comments in the image show what each line is doing.

Save this — you’ll be asked to name your script project something.

How to automate google sheets with macros

Schedule your function to auto-run

Last piece: Schedule your new store-the-data function to run. Click on the clock icon to show your current project’s triggers, and then the link to “Click here to add one now”. You’ll be able to set your function to run whenever the spreadsheet is opened manually (choose “From spreadsheet” as the trigger event) or on an automated schedule (select “Time-driven” as the event) — hourly, daily, weekly or monthly. You may be asked to authorize this in your Google account.

Then, voila! A self-updating spreadsheet that collects and stores data automatically.

I have a table with cells that have functions/formulas, like this one:

I need a script that creates a new row, copying with it the functions/formulas of the last used row. I find this script which create a new row but it doesn’t copy functions/formulas. How could I implement this formatting copy task in Google Apps Script without having to manually select and copy?

How to automate google sheets with macros

4 Answers 4

Use the code below to copy also formula’s as normal values. Add the code by selecting Tools from the spreadsheet menu. Then select script editor and add the code. Make sure to press the “bug” button and authenticate the script.

Remark

Setting the contentOnly to false will yield a standard copy. Setting it to true , will paste only values. The example script you found, does way more then pasting values.

Example

I’ve created an example file for you: Add Row With Formula’s

How to automate google sheets with macros

How to automate google sheets with macros

This ArrayFormula can do just the same without involving a script. Enter it in D4, and it will be carried over automatically in any amount of empty cells below it.

Notes: “B4:B” means, look all the cells starting from B4 until the end of column.

While, ArrayFormula takes care of copying itself into the cells below it. Just make sure that the cells below it are empty.

How to automate google sheets with macros

In case if you need to add a new row on top (first row) and copy formula from the first top row then you’ll need to copy formulas across using getFormulas() and setFormulas() functions. You can change the value of firstRow to 2 if your spreadsheet has headers for example.