Apps Script tutorial: Clean-up sheets with macros from a custom menu
Course 1 of 5 Apps Script + Sheets tutorials
LEVEL: Beginner and/or curious!
Introduction
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 will 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!
Course outline
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
A. Get your sheet
- Make a copy of this spreadsheet.
B. Benefits of macros
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 > {{name of your macro}}.
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.
C. Run macros from a custom menu
Automatically create a pivot table to find misspelled data.
- Locate the 1) EXPORTED DATA tab, then select the menu at the top of the sheet called Clean my export > Find misspelled states.
- You will be prompted to authorize your script to run it.
If you get this additional verification step
Click Advanced and then the second link at the bottom that says Go to…
And finally Allow
- Next, a new tab called Misspelled states will be created in your sheet with a pivot table of all the states and how many there are of each.
- In this case, states should not be listed as abbreviations, but you can find that California has 3 entries made as CA, and New York has 2 entries made as NY.
Replace data with correct entries
- Let’s quickly fix this using the Find and Replace feature under the Edit menu (Ctrl + H) since you can’t use the macro recorder for this task.
- Enter CA in the Find field, California in the Replace with, check the box Match entire cell contents in the Search field, and finally select the button Replace all. This gives you a confirmation in bold at the bottom of the dialog box that 3 instances were changed.
- Do the same find and replace process for NY to be New York.
Automatically perform a VLOOKUP to populate office IDs
- In the cleaning process you also need to match each row to a particular office code, since they are not exported with this information from your system. Depending on the size of your data, it’s handy to use a VLOOKUP formula to pull those codes from a new tab you create in your sheet or can import them from another sheet using the IMPORTRANGE function. In this example we will match up office codes from another tab in the same sheet and have them matched up using a macro you already recorded using the VLOOKUP so you can repeat this process over and over again when working with exported data.
- In your 1) EXPORTED DATA tab, you have a column named OFFICE_CODES and the macro will automatically do a look up for your from the tab OFFICE CODES in your sheet.
- It uses the following formula:
Note: You have to use the $ signs in order for the formula to lock into these positions, without it, if you drag this formula down your column the positions will change.
- Optionally, if you want to import the code from another sheet, your macro would use the following formula instead since it includes the URL of the sheet.
- Let’s run the macro to see it in action. From your 1) EXPORTED DATA tab, locate the menu option at the top called Clean my export > Lookup office codes. It will look like this.
Automatically QUERY unfriendly timestamp formats
Every system you work with will have a specific format in which it saves dates in. In this example, dates are stored as Year + month + day in one blob of numbers in every row. Let’s say you wish to find all records in January for the year of 2019, you can do so by using the QUERY formula to search “201901__” as a table, saving you time in having to split or modify the date’s format. Using a macro, you can have this step automated for every monthly export.
Lets try out the macro! From the 1) EXPORTED DATA locate the menu at the top Clean my export > Query 2019 records, this will run this formula:
And create this table:
Combining macros
As mentioned you can execute multiple macros at once if they are performing tasks on the same page. For example, of the 3 macros in this sheet, we can combine the VLOOKUP and QUERY macros into one step since they work on the same sheet and do not need to run in a particular order. The first macro for example that creates a pivot table would not be a good fit to combine with the other two, because it works in a seperate sheet. You can always make adjustments to your script by hand or record your macros in a way they can work together, such as creating a pivot table in the same sheet, but again, the order in which the macros are executed are important in our workflow since we need to find and replace the correct spelling of states in order to pair it with the proper office code in the VLOOKUP.
Lets see a pairing of 2 macros (the office code lookup and locating of Jan 2019 dates) in action by visiting the 2) CLEANED EXPORTED DATA tab and locating the menu Clean my export > Find & Query:
I share how the script looks when combining the scripts at the end of this article.
D) Make your own changes to the script
To show you how to combine macros and/or make them accessible and shareable from a custom menu, visit Tools > Script Editor, this will bring you to where the sheet stores the script, called an Apps Script. It is a container bound script since it is specifically tied to this spreadsheet. This means that when you wish to run the custom menu options on new data exports, you need to paste this code in that sheet’s script editor (and make sure columns are in the right place for the macro to operate).
It can automate a lot of business workflows across different Google and non-Google products.
The first function uses an onOpen trigger, which tells the sheet to create a menu with 4 options in the spreadsheet’s interface when opened by a user:
Change the name of your custom menu
At present the name of the macro is ‘Clean my export’, we can easily change this my renaming it to ‘Export clean up tools’ and then clicking the Save icon button. Then visit your spreadsheet again and refresh it the page, and when it finishes loading you will see the new name for your custom menu.
Note: you can also make changes to the steps in one of your macros by hand. For example if I now want the script to query dates for January in 2017 I can modify the code to search for ‘201701__’ like this:
Make a new function from a macro
First step is to record your own macro. Click column A of the 1) EXPORTED DATA tab so the whole column is highlighted:
Then visit Tools > Macros > Record macro (recording will begin). First step you wish to record is visiting Data > Pivot table. Choose the option to create a new sheet.
While still recording, in the right sidebar select Add next to Rows and choose State. Then select Add next to Values and choose State as well. The click Save on the macro recorder’s dialogbox.
Let’s visit Tools > Script Editor to visit your macro your steps will be logged this way in a file called macros.gs (on the left).
Copy the code and paste it into Code.gs at the end of all the code.
Note: If you were starting from scratch you would create a new file called Code.gs
Update the custom menu with the new function
Now let’s update the custom menu to include this macro’s functionality by noting the name of the newly pasted function in Code.gs which is newpivot.
Note: best practice in JavaScript is to use camelcase which means rewriting newpivot as newPivot.
Scroll all the way to the top of your file and create a new item in the onOpen function so that you create a new menu option at the bottom of your custom menu called ‘Make new pivot’ and it calls your function ‘newPivot’ and click the Save icon.
Note: when naming menu options, choose short and explicit names so users understand what that option does.
See your new menu option live
Return to your spreadsheet and refresh the page, after it finishes loading you will have a new a fifth menu option under Clean my data called Make new pivot. To run it, make sure you are on the 1) EXPORTED DATA tab when selecting that custom option.
How the macros are combined in the script
Upon deciding which macros can be executed at the same time, you can nest them as we did for the office code lookup and the timestamp query by creating a new function called ‘macroCombined’ and listing it as an option labeled ‘Find & Query’ in the custom menu.
