How to automate google sheets with macros

Laura Taylor

Feb 13, 2019 · 5 min read

As one of the only technologists in a family of CPAs I’m always seeking ways to participate in the discussion. So when the topic of using Benford’s Law as a risk assessment tool recently surfaced in conversation I decided to develop a custom macro to automate the analysis inside Google Sheets.

In a nutshell, Benford’s Law is a mathematical theory based on a logarithmic function which states that for a set of non-manipulated, naturally occurring numbers, the frequency of leading digits “1” to “9” is distributed in a specific pattern as shown in Diagram 1.

Perfo r ming analysis on large sets of naturally occurring numbers, Benford’s Law predicts the leading digit “1” to occur approximately 30% of the time while predicting leading digit “9” to occur approximately 5% of the time as shown in Diagram 2.

This is in contrast with an “equal” distribution (approximately 11%) for the same leading digits as shown in Diagram 3 … as well as in contrast to “manipulated” numbers which are not as likely to follow the same predicted distribution.

Armed with Benford’s Law, auditors can perform risk assessments to identify transactions which differ significantly from the expected frequency distribution and more easily spot errors and potential fraud.

For more insight into Benford’s Law and its potential audit uses see this great Journal of Accountancy article Using Excel and Benford’s Law to detect fraud .

Google Sheets now provides macros to automate repetitive tasks. Macros can be created in two ways:

• Record macros using the built-in recording tool
• Import custom macro functions written in Apps Script

Since I wrote a custom Apps Script function to automate Benford’s Law, I’ll only discuss macro importing in this article.

Follow these three steps to install, import and run the Benford’s Law macro in Google Sheets:

1. Install and Import Macro

There are two ways to install and import the Benford’s Law macro:

Method 1: Make a copy of this Google Sheet with the macro already installed and imported. Click the Copy button to copy the Google Sheet into Google Drive. If you are not logged into your Google Account, you will be automatically prompted to do so.

Method 2: Create a new Google Sheet and manually install the macro following the slides in Diagram 4. You can find the Apps Script code for manual install here.

2. Add Sample Data to the Google Sheet

Import data or use the IMPORTRANGE function to import data from another Google Sheet.

To test the macro with sample data, download and import the example Excel file from the article Using Excel and Benford’s Law to detect fraud .

To run the Benford’s Law macro, navigate to the sheet with data to analyze and select the Google Sheets menu Tools > Macros > BenfordsLaw as shown in Diagram 5.

When the macro runs it prompts for two values:

These prompts require column letter values. Any other values will cause the macro to abort.

• The first time a macro is run in a Google Sheet it will ask for authorization. Follow the prompts to complete the authorization flow.
• You may want to make a copy of the data sheet before running the macro as it will modify the data number formatting as well as add additional columns and a chart to the sheet to accommodate the analysis.

Once the Benford’s Law macro has completed it will have inserted four additional columns to the right of the designated Start Column, performed the analysis on the Sample Data and inserted a line chart showing the comparison of the Benford’s Law and Sample Data First Digit distributions as shown in Diagram 7.

• The Benford’s Law macro will take longer to run on large data sets and it may initially appear that the macro is no longer working or the chart may initially display with a “No Data” message. Wait for the macro to fully complete.
• If the data set is too large, the Benford’s Law macro may exceedApps Script quotas and cease to function.

Need help automating processes inside your Google Sheets or G Suite domain? Get in touch

In this tutorial, you’ll learn how to use macros to automate inserting charts in Google Sheets. A macro is a feature in Google Sheets that allows you to record a certain set of actions and then reuse them to automate taking the same set of actions in the future.

Please see the tutorial on Macros in Google Sheets for more information on macros. In this tutorial, I will explain how to use macros to insert charts in Google Sheets.

Let’s imagine that you run an eCommerce store and you’ve exported all the purchases your customers have made over the past year into a Google Sheets spreadsheet. The spreadsheet contains the following columns: purchase_id, first_name, last_name, email, purchase_date, purchase_month and purchase_amount.

You then create a table that summarizes the number of orders per month and want to visualize this information using a bar chart.

You can obviously create a bar chart manually but you want to automate this process because you plan to run this analysis every month for the preceding 12 months of orders.

Use macros to automate the process of visualizing your data

First select the data that you want to visualize.

Then begin recording a macro by selecting Tools → Macros → Record macros.

Choose “Use relative references” since you want to visualize the selected range.

Then insert the chart and customize it based on your preferences. When you are done, click Save to save your macro and give it a name.

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

Now, when you want to insert the same chart with updated data, just select the data and run this macro from the Tools → Macros menu.

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

The macro will create the chart and even set the title for you. All you need to do is to run the macro!

Behind the scenes, Macros are just Apps Script code

To view the script corresponding to a macro, just select Tools → Macros → Manage macros. Then click the three dots menu and select Edit script. This will open the Apps Script editor where you can view the code that corresponds to your macro.

You will see a file called macros.gs in the Apps Script editor. The code for all the macros in the Google Sheets spreadsheet will be present in this file. Each macro will be a function that has the same name as that of the macro. Since I named the maco “purchase_trend_chart”, there is a function in the macros.gs file that has the same name.

I have copy pasted the auto-generated code below. You’ll notice that there is a lot of unnecessary and repetitive code in the macro. For example, the code first creates a line chart. Then it deletes the line chart and creates a column chart instead. Then it deletes the column chart and creates another column chart with different settings. This third chart is then the final output.

The reason for this is that the macro is literally copying your actions step-by-step. When we write code, we try to directly create the output we want. Here, since the code is being generated automatically, the system doesn’t know what the final output should look like. Therefore, it records every single step. Since a line chart is the default chart that gets created when you select Insert → Chart, some code is generated to create this line chart first. Then when you change the chart’s type to column chart, more code is generated to delete the line chart and create a column chart instead. Therefore, although the auto-generated code will produce the correct output, it can be inefficient since it is going to literally go step-by-step.

You can simplify the above code by getting rid of the unnecessary steps. When you save the script, your macro will use this updated version of the script.

As I mentioned in the other tutorial on macros, I often record macros to learn how to do something in Apps Script. I will record a macro and then I’ll review the auto-generated code to learn how to use Apps Script to take the same action. This can be a simple way to improve your coding skills over time.

Conclusion

In this tutorial you learned how to automate inserting charts in Google Sheets using macros.

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!

Sustainability and Tech

Feb 21, 2020 · 10 min read

Course 1 of 5 Apps Script + Sheets tutorials

LEVEL: Beginner and/or curious!

You may be exporting data from an accounting tool into an .XLS format (Excel spreadsheet) every month, and then manually searching, cleaning, and replacing values. More specifically you probably spend a lot of time finding and replacing duplicates, and performing advanced operations such as a VLOOKUP or a QUERY function in order to pull data from other sheets or digging through records that use unfriendly date timestamps.

In this article I wi l l walk you through how to automate this monthly task by using a Google macro and a custom menu, and invite you to try it out yourself. In the future, you can convert your Excel into a Google Sheet by dragging it or uploading it into a folder in your Google Drive>then second click the file to view its options > open as > Google Sheet.

Let’s get started!

A) Make a copy of the script

B) Benefits of macros

C) Run macros from a custom menu

• Macro to create a pivot table of misspelled data
• Find and replace built-in tool
• Automatically perform a VLOOKUP to populate office IDs
• Automatically QUERY unfriendly timestamp formats
• Combining macros

D) Make your own changes to the script

• Change the name of your custom menu
• Make a new function from a macro
• Update the custom menu with the new function
• See your new menu option live
• How the macros are combined in the script

• Make a copy of this spreadsheet.

Google Sheet’s macro recorder will record your steps into a script that you can run repeatedly to get work done faster and accurately, and it’s especially useful if you do not feel comfortable writing a script in JavaScript by hand. The macro is saved only in that sheet with the name you give it, and can be executed by selecting it under Tools > Macros > >.

All 3 macros in this article used the absolute references option when recording them. This ensures that you do not have to have your mouse highlighting in a specific cell when running the macro because it saves the exact location of the cell you recorded.

Running macros from a custom menu can be helpful when you are working with more people because it makes it easy to find where to execute it from, especially if other teams like your script and make a copy of your sheet, the script carries over easily. Another benefit is you can combine multiple macro recordings and run them from a single menu option if desired.

Written by Laura Tennyson

Microsoft Excel has been the world’s leading business application for more than 3 decades. Indeed for many people, the name Excel is synonymous with the word spreadsheet. According to Microsoft, an estimated 1 in 5 adults on the planet use Excel and there’s no sign of that changing any time soon. When it comes to flexibility and accessibility, nothing beats a spreadsheet for data management and analysis.

With hundreds of advanced functions, Excel is still the tool of choice for many businesses, but Google Sheets is a good alternative. Because it’s cloud-based, Google Sheets and the other Google Workspace apps (formerly known as G Suite) are simple to use, accessible from anywhere, and great for collaboration. You access Google Sheets via your web browser, so you don’t need to install any software — all you need is an internet connection.

Although it’s a bit more basic than Excel, Google Sheets is free and it offers small businesses a great alternative to purchasing a Microsoft Office license.

Anyone can access Google Sheets files via their browser, multiple users can work in the same sheet simultaneously and changes are saved automatically. On top of that, you can see what’s happening with your data in real-time.

Both tools have their pros and cons. This means that it’s now common for companies to use both spreadsheet formats. As a result, many people will need a way to convert Excel to Google Sheets, and vice versa.

How to convert Excel to Google Sheets

1. Choosing between Excel and Sheets
2. Upload Excel to Sheets manually
3. Problems converting Excel to Google Sheets
4. Why convert Excel to Google Sheets automatically?
5. How to sync Excel to Google Sheets
6. Importing data from multiple Excel files to Google Sheets
7. How to filter data from Excel to Google Sheets

Migrating from Excel to Google Sheets or working with both?

Although your team might be permanently migrating to Google Workspace, you may still receive data or reports from external sources. Even if your company is fully onboard with Google Sheets, there’s a good chance you will have to maintain a relationship with Excel.

Some companies restrict their employees to Excel and insist that staff only share files as attachments.

At other times it comes down to personal preference: analysts sometimes prefer to use Excel for heavy data processing before transferring that information to Google Sheets for collaboration and reporting.

Sometimes you might need to use Google Sheets as an input source for other software, such as Google Data Studio.

Which is better? Excel vs Google Sheets

There’s no easy answer here, as it depends on your needs and what you’re used to!

In general, Excel is a more powerful option for heavy users processing large amounts of data. When it comes to online collaboration and ease of use, Google Sheets leads the way. Check out our full side-by-side comparison of Excel and Google Sheets.

Here’s a quick summary of how the two programs compare:

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.

Solutie

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.

RELATED: How to Supercharge Your Google Apps with the Script Editor

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.

In this tutorial, you’ll learn how to use macros to automate inserting charts in Google Sheets. A macro is a feature in Google Sheets that allows you to record a certain set of actions and then reuse them to automate taking the same set of actions in the future.

Please see the tutorial on Macros in Google Sheets for more information on macros. In this tutorial, I will explain how to use macros to insert charts in Google Sheets.

Let’s imagine that you run an eCommerce store and you’ve exported all the purchases your customers have made over the past year into a Google Sheets spreadsheet. The spreadsheet contains the following columns: purchase_id, first_name, last_name, email, purchase_date, purchase_month and purchase_amount.

You then create a table that summarizes the number of orders per month and want to visualize this information using a bar chart.

You can obviously create a bar chart manually but you want to automate this process because you plan to run this analysis every month for the preceding 12 months of orders.

Use macros to automate the process of visualizing your data

First select the data that you want to visualize.

Then begin recording a macro by selecting Tools → Macros → Record macros.

Choose “Use relative references” since you want to visualize the selected range.

Then insert the chart and customize it based on your preferences. When you are done, click Save to save your macro and give it a name.

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

Now, when you want to insert the same chart with updated data, just select the data and run this macro from the Tools → Macros menu.

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

The macro will create the chart and even set the title for you. All you need to do is to run the macro!

Behind the scenes, Macros are just Apps Script code

To view the script corresponding to a macro, just select Tools → Macros → Manage macros. Then click the three dots menu and select Edit script. This will open the Apps Script editor where you can view the code that corresponds to your macro.

You will see a file called macros.gs in the Apps Script editor. The code for all the macros in the Google Sheets spreadsheet will be present in this file. Each macro will be a function that has the same name as that of the macro. Since I named the maco “purchase_trend_chart”, there is a function in the macros.gs file that has the same name.

I have copy pasted the auto-generated code below. You’ll notice that there is a lot of unnecessary and repetitive code in the macro. For example, the code first creates a line chart. Then it deletes the line chart and creates a column chart instead. Then it deletes the column chart and creates another column chart with different settings. This third chart is then the final output.

The reason for this is that the macro is literally copying your actions step-by-step. When we write code, we try to directly create the output we want. Here, since the code is being generated automatically, the system doesn’t know what the final output should look like. Therefore, it records every single step. Since a line chart is the default chart that gets created when you select Insert → Chart, some code is generated to create this line chart first. Then when you change the chart’s type to column chart, more code is generated to delete the line chart and create a column chart instead. Therefore, although the auto-generated code will produce the correct output, it can be inefficient since it is going to literally go step-by-step.

You can simplify the above code by getting rid of the unnecessary steps. When you save the script, your macro will use this updated version of the script.

As I mentioned in the other tutorial on macros, I often record macros to learn how to do something in Apps Script. I will record a macro and then I’ll review the auto-generated code to learn how to use Apps Script to take the same action. This can be a simple way to improve your coding skills over time.

Conclusion

In this tutorial you learned how to automate inserting charts in Google Sheets using macros.

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!

Manually transferring data to a spreadsheet is usually a tedious and time-consuming task. Is there any way to simplify this? Google Sheets have macro tools that automate the entire process. By registering each step, you can teach Google Sheets how to do it with one click. You can also add a custom keyboard shortcut for any item in Sheets, which will also increase your work efficiency. Macros in Google Sheets – how do you create them?

Macros in Google Sheets – how do you create them?

Macros are spreadsheet functions that can automatically perform specific tasks. Removing or adding formatting, inserting additional rows and columns, using more difficult functions or cleaning the data – there are really many options. All you have to do is to teach the spreadsheet how to perform each task, and then press the button or the appropriate keyboard shortcut.

Macros help optimize both individual and team work. Instead of instructing your colleague exactly what to do, just tell he or she to run the macro and the spreadsheet will do the job automatically for them.

Adding a macro is easy. Just run Google Spreadsheets and then choose:

Tools → Macros → Record macro

A small window “Recording new macro …” will open. All recorded activities in Google Sheet will be saved and then played back when the macros are run. The “Use absolute references” option saves actions to specific, selected cells. “Use relative references” will select the cells to the left of the one selected when the macro was run.

Now just click “Save”. One user can create up to 10 macros. Each of them has a number assigned.

To run it, press the key combination:

• PC: Ctrl + Alt + Shift + number,
• Mac: Command + Option + Shift + number.

See also:

Application of Google Sheets macros

Using spreadsheet macros, you can:

• use any tools to format Google Sheets
• use any functions from the Google Sheets toolbar, main menu or right-click menu,
• use any Google Sheets function
• select any cell, row or column,
• use standard keyboard shortcuts,
• enter any text into the spreadsheet,
• every Google Sheets routine, can be automated with macros.

To learn more about the more effective use of applications included in the G Suite package, contact us and take part in G Suite trainings organized by Fly On The Cloud.

Welcome to TNW Basics, a collection of tips, guides, and advice on how to easily get the most out of your gadgets, apps, and other stuff.

If you’re regularly working with data, but lack the interest, financial means, or need for a fancy data management platform, there’s no need to panic. As we’ve shown throughout this series of articles, good old Google Sheets is capable of a lot more than you might realize.

However, knowing all of Google Sheets’ neat formulas is one thing. Using it on scale is another. That’s where macros come in.

A macro is a sequence of instructions that can be repeated all at once. Macros also exist in Google Sheets, allowing you to record yourself performing a series of tasks.

Macros use case

Let’s say you have a sheet containing an imported data table, and you now want to import the same table but with updated figures. The original table might have two columns: A and B. You might want to add these figures together, creating a column C. Then you decide to add filters to the headers, and sort the sheet by the combined figure in column C.

The problem is, as you’re importing the new table of data, you’re overwriting the sheet’s previous data, including the manually added column C. To prevent that from happening, or at least to stop you having to manually add that column again and sort the table by it, we can use a macro.

Using macros in Google Sheets

It works as follows: Go to the Tools menu, select Macros, and then Record macro. From now on, it’s recording your desired sequence of tasks — so start performing them.

When you’re done, click Save and give your macro a name. Next time, instead of performing the sequence of tasks by hand, simply select the macro from the menu.

You can create multiple macros in the same Google Sheets document to automate boring, repetitive tasks. So, what are you waiting for? Back to work, you productivity wizard!

Google Sheets is a popular spreadsheet tool that has changed the way people collaborate today. This web-based spreadsheet tool serves best as a free alternative to the Microsoft Excel and allows to create and edit spreadsheets data online. Excel has more features and built-in functions than this free online tool, but it is preferred because it is free and due to its online accessibility from any device.

Google Sheets has now added these powerful features to automate monotonous tasks. Working in spreadsheets involves recurrent tasks that can be boring and tiresome. Macros are the apt way of being productive that lets you automate the boringly monotonous tasks. Spreadsheet users who deal with multiple sheets having similar charts, functions, and data would reap benefit from the macros. Macros are the best way to save your time that would allow you to focus on your important task rather than doing the deary round of bland tasks.

What are Macros?

Macros are the programs that will allow you to automate the recurring task without the need of you writing code. Macros record your action and save them so that you can reuse them when needed with a single click of a button. Macros come in handy when you want to automate the tedious work in sheets like add formatting, inserting additional rows, inserting additional columns, formatting tables, creating charts, inserting tricky formulas, inserting functions and more.

Macros in simple terms is a time-saving tool which is a trifecta of recording a repetitive task, saving the task and running the task in future whenever you want without writing any code.

Create Macros to automate tasks in Google Sheets

Launch Google Sheets by entering sheets.new in your browser URL or simply open Google Drive folder and press Shift + S to create a new Google Sheet in that folder.

Type some data in any cell of the sheet. Navigate to Tools and select Macros from the drop-down menu.

Click on the Record macro from the submenu. This will open a Recording New Macro box at the bottom of your sheets. You will be asked to choose between the two options. Either User absolute references or use relative references.

Select Absolute references when you want to apply formatting techniques to the exact location as recorded. Say you want to format a range of cells A2: C1. Select the data in those range cells and make the font bold. The macros will be applied to these cells only and will always make the data in those cells range appear bold regardless of which cell you clicked. Select Relative references when you want to apply formatting to the different cells, irrespective the exact location as recorded. It applies macros based on where your cursor is rather than the exact location where the macros were recorded.

This is useful when you want to insert chart, functions, or formulas on the cells you select and its nearby cells. Say if you record bolding data in cell B1, the macro can be later be used to bold cells in C1.

After you select between the two choices, the google sheets will start recording. Any formatting which you want to automate in the cell, column, or row will be recorded. Make sure to plan what you want to record well in advance.

Apply the formatting in any desired cell-like changing the font style, color, etc. The Macro recorded watches each of your step.

Once done, click the Save button and type the name for your Macro.

You can also set a custom Short key to have quick access to your Macro.

Click the Save button to create your macro.

To access the Macro, Navigate to Tools, and select Macros.

Click on the Macro folder you want to run from the submenu.

Edit your Macro

You can change the name of the Macros, edit the Macro scripts, remove macros or even add the keyboard shortcut after its created.

Navigate to Tools and click Macros from the drop-down menu.

Select Manage Macros from the submenu to edit your macro.

To delete a macro or edit its scripts, go to More beside the options Macros and click Update.

Import other macros

Google Sheets allow you to import any custom functions created using Google Apps script file to the Macro menu.

On your Google Sheets, navigate to Tools. Select the options Macros from the drop the menu. Click the option Import from the submenu.

Click Add function button next to the function you want to import.

That’s all there is to it. I hope you find the post useful.