SPEAKER 1: You may be exporting data
from an accounting tool that's in an Excel format every month
and then converting it to a Google Sheet.
Then, you manually search, clean, and replace values.
More specifically, you may 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 formats.
In this episode, I will walk you through how
to add automation to this monthly task
and run it by choosing a menu option
at the top of your sheet, even if you are not
as familiar with JavaScript, thanks to Google Sheets Macro
Recorder.
Welcome to the "Sheets to Apps Show,"
where we talk about how to tame these tabular beasts
into productive applications.
If you are new to the Macro Recorder in Sheets,
it's a built-in tool that records your steps
into a script that you can run repeatedly
to get work done faster and more accurately.
It's especially useful if you do not
want to write all your steps by hand in JavaScript.
Included in this video's description
is a sample sheet with step-by-step instructions
and helpful resources for you to check out later if desired.
The script includes three macros that
were recorded using the Absolute References option.
This saves the exact location of the cells while you record.
We run these from a custom menu in your Sheet, which is helpful
when working with more people because it makes
it easy to find where to execute it from,
especially if other teams wish to 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.
To visit the logic, you select Tools, Script Editor
from your copy of the sheet linked in the video.
The first step was to record a macro that
automates the creation of a pivot table
to visualize what data is misspelled,
such as state names.
When done from the Script Editor,
you can refine its name so it's more explicit.
It's also a best practice to format the function's name
into camelCase.
For example, I changed newPivot to misspelledStatesPivot
and named the file in the Script Editor to code.js.
You then repeat this renaming process
for each macro you record.
Next, you add the following lines
to display a custom menu whenever
the sheet is opened by a user and displays four suboptions.
This menu is displayed at the top of the sheet as Clean My
Export, and selecting its suboption
Find Misspelled States kicks off the automatic creation
of the pivot table every time it is clicked
from the tab that holds the data you would
like to represent as a pivot.
Note that the first time you use the Custom Menu,
you are prompted to authorize your script,
or when you save new changes to your script.
And if you encounter a warning page that
says this app is not verified, then
proceed to click Advanced, Go To, and the name of the sheet,
and then Allow.
Next, a new tab called Misspelled States
is created with a pivot table inserted,
listing how many versions are misspelled as abbreviations,
such as CA or NY.
We fix this by using the Find and Replace
feature under the Edit menu, since you
can't use the macro recorder to automate
this task at this time.
The next macro in your custom menu
matches each data entry to a particular office code,
since office codes are not included in your systems
export.
The macro does this by recording you
using a VLOOKUP formula that looks up states and office
codes from another tab in your sheet
and then dragging that field down the entire column.
Now you can select Clean My Export, Lookup Office Codes,
and in a few seconds it populates column E
with the correct values for you.
The third macro saves you time in having to split or modify
unfriendly date formats.
For example, your system may store dates
as a blob of numbers like this, and you
want to find all records that fall within January 2019.
You then record your actions using the query formula
to search for it in a table like this.
Now, from the Custom Menu called Clean My Export,
you can select Query 2019 Records,
and it returns the values within that January 2019
period in table form.
Note-- once your script is created,
you can always update it with new search criteria.
For example, you can modify just a few digits
if you change your search to a different year,
and this way you don't have to rerecord
the macro from scratch.
As mentioned earlier, you can execute multiple macros
at once if they are performing tasks on the same page.
For example, of the three macros I discussed,
we can combine the VLOOKUP and query macros into one action,
since they work on the same sheet
and do not need to run in a particular order.
The macro that creates a pivot table
would not be a good fit to combine with the other two,
because it works from a different sheet.
However, you can always make adjustments to your script
by hand or record your macros in a way that work together,
such as creating a pivot table in the same sheet.
To combine the VLOOKUP and query macros,
we create a new function called Macro Combined
and include both functions called Lookup Office Codes
and Query Records By Year.
Then, we add a new item to the menu option called Finding
Query From The Spreadsheet.
To run both macros in one click, you then
select Custom Menu, Clean My Export, and then
the Find In Query option.
And there you have it--
a walk-through of how to build your own automation
scripts in Google Sheets using the macro recorder.
And community, if you've found this episode helpful,
please click like and subscribe to receive
updates of future episodes.
Cheers.
[MUSIC PLAYING]