Hi, this is Gary with MacMost.com. Let me show you how to use
Numbers and Shortcuts together on your Mac to easily record data.
MacMost is brought to you thanks to a great group of more than 1000 supporters.
Go to MacMost.com/patreon. There you can read more about the Patreon Campaign.
Join us and get exclusive content and course discounts.
Now sometimes you want to create a spreadsheet where you want to easily record
data. Maybe you want to put just the date and time into a spreadsheet or maybe add
some additional information and you don't want to have to open the spreadsheet and
then type it all out yourself. Well the latest version of macOS Monterey
there's some added functionality in Shortcuts that lets you do this.
So first let's create a Numbers spreadsheet to hold this data. I'll just start with a blank
template here. I'll put Date/Time for the first column and Event for the second column. Then I'll
get rid of all the additional columns. Maybe lengthen this column a little bit right there.
There's no data in this now so I'll shrink this a much as I can. I have to have at least
one row in here that's not a Header row so I'll just leave that blank. Let's name everything
properly. I could leave the name as Sheet 1 and Table 1 but let's actually give them proper names.
I'm just going to double click in here and call this Data. Maybe this is the data sheet and you
have other sheets that show reports and things. The Table here let's just call that Events.
So let's Save the document and I'm going to call it Record Events and just put it on my
Desktop. You should probably put it somewhere in your Documents folder. But I'll put it on
the Desktop so we can easily see it here during this tutorial. So I've saved that.
So now what will be nice if I could trigger a shortcut to actually fill in a date and
time and an event. Let's Hide Numbers here for now. We could see the file there. Let's launch the
Shortcut App. I'll create a new shortcut. Let's call it Record Event into Spreadsheet. I'm going
to search over here, under Apps, for Numbers. Here I can find three actions that pertain to Numbers.
So we've got one here called Add Row to Top or Bottom of a Table. That's exactly what we want.
We don't need to create or open the spreadsheet in advance. We can just go right to this. I'm going
to double-click it to add it. Now we've got Add Values to the Bottom, we can do bottom or top,
and then we define a Table, Sheet, and Document. Let's work backwards here. I'll click here and
select that document. Great. The sheet name remember was Data. The Table name was Events.
We do want to add to the bottom and then what do we want to add. Well, we want to add the date.
So let's go and search for Date here, and there is an action for getting the current date. Let's move
that above here and it gets the current date. If we wanted to we can Format the date. Let's
put Format underneath, Show More, and we can select the date and time format.
Now let's click here and I'm going to actually Control Click in here and do Insert Variable,
Select Variable, and Select the Result of the Formatted Date. So it takes the current date
and time, formats it like we want, and then is going to use that in what it adds to the table.
Now there's also a Plus here that allows us to add more data. So this will add one cell. We can use
this here to add another cell and continue going. But let's test this out first. So I'm going to
go to Settings here and I'm going to Pin this in the Menu Bar. So now we see it right here.
Now I can actually quit Shortcuts and if I were to run this it's going to open up
the Spreadsheet and sure enough it's going to put the date in that format right here
and nothing for Event because we're only adding that one cell of information here. The first time
we do this we're going to end up with a blank line because we had to have a blank line here
in order to keep the table there. So now we can get rid of that. Now anytime we use this again,
whether the spreadsheet is opened or not, it's going to open it and then add something new to it.
Now what would be nice is if we can actually record an event as well.
So to do that what we want to do is search for Choose and there's Choose from List. Let's add
that to the beginning here. Now we need a list of items. So I'll search for List and let's add List
up above. So first define the List. Let's define a bunch of events here. I'll just create some sample
ones. This creates the List. We see it says Choose From and it automatically selects the List so it
was right before it. If not we could have clicked here and selected List as the Input. Now we get
that data here. We want to add it here. So where did that Plus button go? Unfortunately that Plus
button is gone. I'm going to Control Click on it and clear that out. Click again to Add Values. The
first value I want Insert Variable, Select, and get the formatted Date. Now that Plus button is
back. Click that, Control Click, Insert Variable, select variable and get the chosen item here.
So now what you get is two things to that row, formatted date and the chosen item. So I'll Quit
Shortcuts and I'll run it again. You could see it comes up with this nice list. I can select one
and click Done or just double-click that item. It will open up the spreadsheet and Add
that here. So you can add one or many more items to a spreadsheet row always creating a new row
there. It would be nice if it didn't leave us sitting here in Numbers. You can kind of add
that to the shortcut. If I Search for Quit, I can do Quit App, Choose the App, search for Numbers.
What I find is that it doesn't quite work unless you add a wait, because it is still working on
this when it tries to execute that and so it won't quit. So you have to wait for say three seconds
here. Which gives us time to review the entry. So now if I wanted to add an event I could trigger it
like that. Select one. You could see it adds it there and a few seconds later it Quits Numbers.
So you could probably see all sorts of uses for this. You can record all sorts of data into a
table in a spreadsheet and the spreadsheet doesn't just have to be that table. It could be a complex
spreadsheet. You're just adding a row to a single table in that spreadsheet. You can add extra
things. You can have several lists here. You can ask for input right here where you ask for a value
that you can enter. So you can have an item for sale, the amount sold, the total price, a prompt
for all of that. It automatically puts the date and records that all on the line in a spreadsheet.
Now while those actions are new to Shortcuts for awhile we've been able to use Scripting to add or
modify data in a spreadsheet. So there's no way to use those actions to change the value of a cell.
But you could use the same scripting that you would have used before in Shortcuts or even before
Shortcuts existed in Automator to change the value in a cell. So, for instance, let's start off a new
Shortcut here by opening the spreadsheet and we will choose that same spreadsheet right here.
Let's go and say we want to change the value of a specific cell. There's nothing here that allows
you to do that. But if we use a bit of JAVA script for automation we can. So here's the script. First
we define the application. a little Link to the application to variable Numbers will link
to application Numbers. Then a link to the Table inside of the document. So we do Numbers.documents
and then the name of the document. Then we do .sheets, the name of the sheet and .tables and
the name of the table. Then we get a link to the cell. So we take the table.cells and then cell B2
so specifying cell B2. Then we take that cell and set its value to something. So this could
be something we choose from a list like in the script before. If I open the spreadsheet so we can
see it here below here is cell B2. Then if we run this we can see it changes the value of cell B2.
So by using scripts like this you can actually change values in existing spreadsheets rather
than simply adding new rows. You can expand on that to do all sorts of different things
with Shortcuts and Numbers. Hope you found this useful. Thanks for watching.