Hi, this is Gary with MacMost.com. Today let me show you how to create
simple Numbers helper tools using Automator.
MacMost is brought to you thanks to a great group of more than 800 supporters.
Go to MacMost.com/patreon. There you can read more about the Patreon Campaign.
Join us and get exclusive content and course discounts.
So it's fairly simple to be able to create a tool in Automator that allows you to change
what's in selected cells. You can use this to create Quick Actions that work just in Numbers
and if you use Numbers a lot this could increase your productivity and help you do some things that
will be difficult to do otherwise. You may not find any of these specific examples I'm
going to show you useful but with the skills you learn hopefully you can make your own.
I'm going to launch Automator and I'm going to create a Quick Action. A Quick Action will allow
us to put the workflow in the Services Menu which then could be accessed with a keyboard shortcut.
Now I'm only going to add one action to this workflow. That's the JavaScript
action there. Run JavaScript. I'm going to replace what's in here with my first
script which is the simplest of them all. So I'm going to paste some code in here,
let's expand this so we have more room, inside the Library. Now we can see our code here.
Before we go any further I'm going to set this up so it works not in any application
but only in Numbers because that's the only place where this is going to make sense to use it.
Now let's look at what we've got here. In the beginning we take the application,
Numbers, and put it in a variable Numbers so we can refer to it. We also set
Include Standard Additions as true because we may actually be using some of that advanced
code in some of these examples. Then we have a bunch of lines here that the whole purpose is to
get the cells that are selected. So you should only be using this when you have some cells in
a table selected. At the beginning here it's going to look for all the tables that we have
in the current sheet, the active sheet, at the front-most document. So Range, for selection,
would be something like A1:B7 if you've selected all the cells from A1 to B7. But a list of these
ranges is going to be for each table. So if there are three tables you might have null, null,
A1 and B7 meaning that the first two tables have nothing selected and the third table has something
selected. So then we're going to loop through all of those ranges. We're going to sign the current
range, the range for say the first table, to this range and if it's not null, if it's something then
we know we've got the range. We figured out which table has the selection in there. So now
we know that thisrange is the range and tableNum contains the number of the table with that range.
So we can get the real range here which is looking at the table with the selection,
then getting the range for thisrange. So in other words we're storing the selection if it's table 2
B4 to C6 we've now put that range in here. Now we can get the cells from that range. So if there are
6 cells in that range we now have a little array that has those 6 cells.
Now we can do things with those cells. In a lot of cases you're just going to have just one cell
selected. So the range would be something like B2 to B2. But now we'll have a variable cell
that contains a reference to that single cell you've selected or the whole list of cells you've
selected in the range. Now we could do things to those cells. So we're going to create a simple
little loop here. It's going to loop through all of the cells. Then it's going to take each cell
and set its value to something, in this case just a name but it could be any text you want. Now
that's all we need to do. Except that what happens now is that every time a value is set Numbers sets
the selection to that cell. So if you selected, say, six cells it's going to go through each of
those six and at the end you're just going to have the last one selected. So we're going to reset the
selection range to the original range. So if you had six cells selected at the beginning you'd
have those same six cells selected at the end. Now let's try running this. So I'm going to shrink
this window a bit. I'm going to bring in a Numbers window so we could see both at the same time.
Now I'm going to try using this script to put that text into this cell. So if I run
this Automator action I'm going to use the Run button here, not the Run button here, then it
should work. You can see it does it. It puts that text right there. What if I were to select a range
of cells like that. Now when I run it you could see it fills in that text for the entire range.
So now that we have something somewhat useful let's Save it so we can activate this with
a keyboard shortcut in Numbers. In Automator here I'm going to Save and since I've already
created this as a Quick Action it's just going to ask for a name for the Quick Action.
I'm going to call this Numbers Helper and I'm going to Save it. Now when I'm in Numbers,
if I go to Numbers and then Services, I should see Numbers Helper appear here. Let's assign a
keyboard shortcut to that. We'll go to System Preferences and then Keyboard,
Shortcuts, under Add Shortcuts we'll create a New one. We'll type the menu exactly as it appears.
So, Numbers Helper. We'll set this up to only appear in Numbers and then we'll do a keyboard
shortcut like Command Shift 1 and Add. Now in Numbers here if I go to the Services Menu
I can see here Numbers Helper is now assigned to Shift Command 1. So let's give that a try.
I'll select another cell, Shift Command 1 and you could see it runs and puts that in there.
Now let's try to do some other things with this script. How about a random number.
You could easily use the random number formula to have a random number in a cell but the number
changes every time the table is recalculated. You could then Copy and then Paste the value in but
that's a bunch of extra steps. What if you just had a quick keyboard shortcut that threw a random
number into a cell. It could be useful for testing ideas. So here instead of setting the cell to be
this text we're going to use some JavaScript here that's basically going to take math random
which gives us a number between zero and one, so something like .563 or something like that. So a
fractional value between zero and one. Multiply it by ten. Use floor to round down. So we're going to
get a number between zero and nine. So with that there I could Save and now when I use the keyboard
shortcut I get a random number. I could do it again and again. I could select a range of cells
and each one is going to get a new random number. I could
simply use the keyboard shortcut again and you could see I get new random numbers there.
You could set this to be from zero to 99 or add 1 to it to make it one to a hundred.
Now it may have already occurred to some of you that you could use this to insert
the current date into a cell. In Numbers you could use a formula to do this but it would
always update to the real current date. What if you wanted to permanently put a date on the line.
You could use the Insert Date & Time menu command. But in order to do that first you have to be
typing in the cell. So you've got to double click in the cell. Then you got to insert it. Then it
does it only in this format. You can then change the cell format to something else. It's another
step. What if you just could put the date in there in the format you wanted to in the first place.
So first let's add some code here to get the date in JavaScript. So this code gets the current time
right now and then creates a date based on that time. It's getting the month adding one because
the month's are zero based, and then getting the day and then getting the full year. Now we can
set the value of the selected cells to that date. Let's save that and give it a try.
Shift Command 1 and you could see it puts the current date in there.
So here's another idea. Sometimes you have a formula result and you want to
make that formula result permanent. Like for instance if I have 2 in here and 3 in here
and I have a formula in here where it adds these two together.
Now every time I change one of these numbers it changes the result. Sometimes you want to
get rid of these numbers. Maybe you want to get rid of these columns or reuse these cells for
something else. But you want that result to stay there. You could do Edit, Copy, and then Edit,
and then Paste Formula Results. That would change this from the formula. Look here at
the bottom. You could see that's the formula in the cell. But if I Paste Formula Results
now you could see it says actual value is 5. The formula is gone replaced by that value that won't
change now. We could do this using this script here. Let's get rid of this date information here
and let's set the value of the cell to the value of the cell. Because that value property is going
to get the results of the formula. So by setting the value of the cell to the value of the cell we
remove the formula and replace it with the actual results. So with this selected now if you look at
the bottom it says formula of E3 plus C3. But if I do Shift Command 1 you could see it replaces it
with 5. That's now a permanent 5, not a 5 that depends on these two cells.
Now let's go back to the idea of random information. What if we wanted to put a
random value in there, like a random name or something. Let's create an array that just
contains the names of the planets in there. Now what if we wanted one of those to be randomly
placed in the cell. We could use this which would look in that array, get a random value
that's from zero to the length of this array. So it would get a value zero 1, 2, 3, 4, 5, 6, 7
because we're rounding down. So it would take the zero to 7, grab the zero to 7th item here
and put that in the cell. So let's Save it and we'll try it here. You could see it grabs one.
If we grabbed a bunch of cells and we did it it would put a random one in each cell.
Now let's say I had a text document like this and I wanted to copy some data into here. I paste it
into a cell, grab the second element, paste that into a cell. What if I could do the automatically.
What if I could copy all three of these and then paste it into three cells at the same
time. So to do that let's get an array but based on what's in the Clipboard. So we're going to get
The Clipboard. Then we'll get the values by splitting that bit of text using commas. So
you can use another character if you want. We're going to use commas. So it's going to
create an array containing all the pieces of text between all the commas. Now we're going
to do something a little bit different here in the for loop. We're going to go and check first to see
if we've run out of values. Because what happens if we've selected 7 cells but we only have 3 items
to paste in there. We want it to stop after 3. Then we're going to set the value of each cell
to each item in the array successively. So I'll Save that. Let me Copy what's in here.
I'll go to Numbers and I'll use Shift Command 1 to run the script. You could see it divided it up
into three different things and put them there. If I were to select extra cells you
could see it ignores the rest. If I were to select too few cells it just does those two.
How about data from the internet. I've placed, at a location in one of my websites,
a little piece of text that's just a single number. 57.8. So if I were to try to get the
value from that document I could put it into a cell in Numbers. First I'm going to get the value
from that location. So I need to use application.currentapplication for this and then
include standard additions for that. Then I'm just going to use that application which will be the
script engine that's running this and do a shell script. That shell script is going to be curl,
which simply fetches what's at a location on the internet, and then the location of that data. So
now my variable data is going to have that in it. So now in my loop here
I could just simply set the cells to that data. So let me Save that and let's give it a try.
If I do Shift Command 1 you could see it grabs the value from the internet.
Now this has huge potential. But you have to be able to have data out there that you can get.
I know I'm going to get lots of people saying oh, how can I use this to get stock data?
How can I use this to get weather data? Whatever. Well, you have to find a source of data out there.
It's really hard to find good sources of data that are open and easy to get like this. But
if you work at a company or school or something that has an IT department that can put data out
on the internet, like maybe you work at a school and you convince them to publish data like how
many students are in attendance today to a spot in a publicly accessible place then you
could get this data. You can combine this with what we saw before about splitting things with
commas to get more than one piece of data as well. But as far as getting stock information
and other things like that it's going to be a lot more difficult. You're going to have
to find a source for the information, be able to parse through a bunch of different data because
it's probably not going to be as simple as just a single number sitting out there.
So let's look at something a little bit different. Here's some code that's going to
create an array like we had before. But it's going to create it by looking at the current values
of the cell. So looping through the cells and getting the value of each cell. Then it's going to
loop through them again and use the pop function to pull items off that array.
The upshot of which is that it's going to reverse everything that is there.
So it's going to grab the first item, second item, third item, and then replace those with the third
item, second item, first item. Let's take a simple example. I can put two numbers in here,
like that. If I select those two numbers and I use this script here it will reverse those
two numbers. I could do it again to reverse them again. If I had a third number in here, and let's
do a fourth one as well, I could select those all and it's going to put them in the reverse order.
So that could be useful in certain situations. But what if I took the last part of this and instead
of just simply taking the last item and putting it in the first place I could randomize it.
So I'm going to loop through here and I'm going to grab a random item from the array
and pull that off. I'm going to use splice. That removes the item. So here I start with four items.
It's going to take those, take a random one and put it in the first spot. There will only be three
left. It's going to take a ransom one and put it in the second spot. Then there will be two left.
So it will go through until it runs out of items. So you can see these items here, if I use this
it's going to put them in a random order. I can keep doing it and it will randomize it each time.
So here's one final example. Instead of changing the value of the cell, I'm going to change the
background color. So here all I'm doing is looping through the cells and I'm setting
the background color property to a little array of three numbers. These are numbers between zero
and 65535 which is a color value. Red, green, and blue. So in this case it's going to set
the background colors to that. Now to make that a little easier you add a little extra function here
that just takes a hex value which is what you see when you create webpages. It will translate
that to an array like this. So I can use hex color and then a value like 006699 and then
I can apply that to a bunch of cells to change the color. It's a little easier to
come up with these colors than to actually come up with those three large numbers.
So there are a whole bunch of different examples of how you can use a simple Quick Action to be
able to change things in cells in Numbers. So if you use Numbers all the time and you think about
all the tasks you do you may be able to match up a task that you have to perform all the time
with this script and make it easier to perform that task with just a keyboard shortcut.