Creating Useful Helper Actions For Mac Numbers

By using JavaScript in Automator you can change the value of selected cells in Numbers. But a little more coding you can place specific or random values in the cells, reorder them, insert data from the clipboard or even an Internet location. There are all sorts of ways to use code like this to do things you couldn't easily do in Numbers otherwise.

You can also watch this video at YouTube (but with ads).

Video Transcript: 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.

Download the text of the scripts here.

Comments: 8 Responses to “Creating Useful Helper Actions For Mac Numbers”

Scott R

4 years ago

Interesting video. Can you give some quick recommendations on the best way to start learning the JavaScript language you showed for automating Numbers? Do I start with learning Automator first? Is JavaScript the primary or only language for Automator? Is there a JavaScript language documentation feature within Automator similar to the function browser in Numbers? I'll starting watching your Automator videos ASAP. Thanks.

Gary Rosenzweig

Scott: You can use JavaScript, AppleScript or shell scripts in Automator. There is no documentation in Automator, but there is in the Script Editor app (the Library) and Script Editor is a good place to play around and learn "JavaScript for Automation," which is what is used in Script Editor and Automator. But a basic background in JavaScript is helpful, and that is easy to find all over the Internet since it is used everywhere.

Dan

Awesome as always! Thanks.

Question: Pretend I have a single column of 10,000 words (1 per row) and I want to put that data into a 100X100 table. Is there any way to simply paste the data and have it fill the first column, move the the next, and so on, or would I have to copy/paste one row at a time? Thanks!

Dan: One idea: Add a second column. Put an XXXXX in every 100th row of that second column. Then export as a CSV. Then open it in TextEdit. Then find and replace every line break with nothing, so it is one long list off items on one line. Then find and replace every XXXXX with a line break. Now you have 100 lines of 100 items. Save and import that CSV into a new Numbers document.

Brenda Brooks

I found this subject really interesting and immediately found a use for 3 of your examples (Date, Actual Value and Reverse Cells). I would love to be able to do the same in Numbers on my iPad. Any ideas how I could achieve that?

Brenda: You can't automate on iOS like you can on a Mac. There is no Automator app, just Shortcuts, which doesn't do much for Numbers. And you can't write scripts like with JavaScript or AppleScript.

John

3 years ago

The last part about setting a background color automatically is almost what I'm trying to do! I have a spreadsheet of weather data, and I figured out how to calculate a color for each number. So I have one column with numbers and the next column with hex codes. Is there a way I can write a script so that when I select a bunch of cells in a column, for each cell it will take the value of the cell directly to the right (something like "#953AAC") and use that as the text color for this cell?

John: Yes, you should be able to do that. Try working it out.

Comments Closed.

Creating Useful Helper Actions For Mac Numbers

Comments: 8 Responses to “Creating Useful Helper Actions For Mac Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information