Using Shortcuts To Add Data To a Numbers Spreadsheet

A new action in Shortcuts for Mac allows you to add a row of data to a Numbers spreadsheet. You can prompt for information and then record a time and some data. Numbers version 12 is required. An older technique allows you to change existing data in a Numbers spreadsheet.

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

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

Comments: 13 Responses to “Using Shortcuts To Add Data To a Numbers Spreadsheet”

Mark Stacks

3 years ago

Gary, Thanks for this video on "Using Shortcuts". I've wanted a way to enter the date and time in a cell without having to manually enter the characters. When using formulas, I haven't managed to automatically populate the new date/time in the next data row without the date/time changing every time I open the spreadsheet. This shortcut does the trick without multiple cells converting to a static date.

Thanks again. Mark

Gene

Good video Gary. I have a master spreadsheet. I take data (CSV files) each month and add it to the master. They monthly sheets contain all of the columns of the master but not in the correct order. Is there a way to use Shortcuts (or Automator) to automatically move the columns around so that the incoming table will match the columns of the master sheet? Thanks in advance.

Gary Rosenzweig

Gene: There may be a way, but it would never be worth it. If you only do this monthly, just bring the CSV into a new Numbers document, drag and drop the columns to arrange, and then copy and paste those rows into your main spreadsheet. You'd have to do that for 12 months a year for decades in order for it to be worth automating.

Anthony

2 years ago

Excellent video Gary. I tried following you step by step using my iPad, when I got to the quit step, I was not able to find the "Quit App - Numbers" It's not available. Is there another way of doing it? I need to quit after entering the data. Thanks again.

Anthony: This is a tutorial for Mac. I was just quitting to show the app launches when you use this. You don't need to quit on the iPad. But I don't know if this will work at all on the iPad. I never checked.

Have you done a shortcut to enter data quickly into the business spreadsheet(video), where you can just enter the expense or income from the shortcut directly into the spreadsheet app, then close it?

Anthony: It sounds like you are asking for exactly what I am showing in this video.

Nathan

Great info. The Javascript returns "Error: Error: Can't get object." What I'm I missing? Thanks.

Nathan: Check your code carefully around where that error occurs.

Erkki

Hey Gary, Thank you for this stuff, love it!
I read that you have not tried this on ipad. I did not know that and used this video on ipad and it works quite well around my devices that are connected to same icloud. I even dont have to copy paste the shortcut. But a problem has come up. Using same numbers file from 4 different devices, some rows go missing. Any thoughts? Numbers file versions something to uncheck? (the shortcut i created puts the entry to a new file name)

Erkki: Rows go missing? Like you see 1, 2, 3, 4, 6, 7, 8? That would be a filter that you have turned on. Turn off filters to see all rows.

Tom Lillis IV

I enjoyed the video - it helped me find a bug…er…unexpected results.

If the first column is a condition for filtering it will be pre-populated by the last entry and the data “Add” will be offset horizontally by one. So, in your video, if the table was set to only show this week's events the "event" field would contain the date and (if you had a third column) that would contain the event.

Unfortunately there is no way to test if the table is filtered prior to running the Shortcut.

Captain Randy

I created a shortcut to do exactly what you described. Each time I got a "Sheet Not Found Error". The spreadsheet I chose has many sheets so I tried another sheet and it worked. It took many trials but I think I've discovered that even though Numbers accepts a sheet name with an apostrophe the Shortcut will not. As soon as I renamed the sheet without the apostrophe it worked fine. Oddly enough the Table Name does have an apostrophe and it works perfectly. This seems like an odd flaw.

Comments Closed.

Using Shortcuts To Add Data To a Numbers Spreadsheet

Comments: 13 Responses to “Using Shortcuts To Add Data To a Numbers Spreadsheet”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information