creating a script for Numbers (and assigning key combinations)

Hi everyone, I need a lot of help in creating a script for Numbers that selects all the contents of a cell, copies it and pastes it in the form of a formula. Following the creation of this script, I would like the latter to be assigned the combination of some keys, so that I can carry out this process as quickly as possible since I have around 20,000 formulas in text format inside the cell and I need to transform them into real active formulas. I look forward to your help and thank you

MacBook Air 13″, macOS 14.5

Posted on Jun 10, 2024 6:50 AM

Reply
Question marked as Top-ranking reply

Posted on Jun 10, 2024 1:21 PM

tell application "Numbers"
	tell front document
		tell active sheet
			tell (first table whose selection range's class is range)
				repeat with c in (get selection range)'s cells
					tell c
						if formula is missing value then
							set value to "=" & value
							set format to automatic
						end if
					end tell
				end repeat
			end tell
		end tell
	end tell
end tell


Create a service from that script using the Automator app.

  1. Create a "Quick Action" with no input in application Numbers
  2. Use the Run AppleScript action
  3. Delete the default script and paste in the code above (95% of which was from SGIII's script)
  4. Save it with a suitable name.


It will show up in the Numbers->Services menu. You can give it a keyboard shortcut in System Preferences->Keyboard->Shortcuts->App Shortcuts (or whatever the equivalent location is in more recent MacOS versions). When making the keyboard shortcut you need to type the name of the service in exactly as it appears in the menu in Numbers. Capitalization matters. Option is typically used for optional characters on the keyboard (Option z is Ω). You can steal Command = from Format->Font->Bigger.


Select a cell or a contiguous range of cells then run the service.

Similar questions

6 replies
Question marked as Top-ranking reply

Jun 10, 2024 1:21 PM in response to Yuri2302

tell application "Numbers"
	tell front document
		tell active sheet
			tell (first table whose selection range's class is range)
				repeat with c in (get selection range)'s cells
					tell c
						if formula is missing value then
							set value to "=" & value
							set format to automatic
						end if
					end tell
				end repeat
			end tell
		end tell
	end tell
end tell


Create a service from that script using the Automator app.

  1. Create a "Quick Action" with no input in application Numbers
  2. Use the Run AppleScript action
  3. Delete the default script and paste in the code above (95% of which was from SGIII's script)
  4. Save it with a suitable name.


It will show up in the Numbers->Services menu. You can give it a keyboard shortcut in System Preferences->Keyboard->Shortcuts->App Shortcuts (or whatever the equivalent location is in more recent MacOS versions). When making the keyboard shortcut you need to type the name of the service in exactly as it appears in the menu in Numbers. Capitalization matters. Option is typically used for optional characters on the keyboard (Option z is Ω). You can steal Command = from Format->Font->Bigger.


Select a cell or a contiguous range of cells then run the service.

Jun 10, 2024 10:32 AM in response to Yuri2302

"A script for Numbers that selects all the contents of a cell, copies it and pastes it in the form of a formula" says that there is one text-formatted "formula" in each cell that needs to be converted (I assume in the same cell). "I have around 20,000 formulas in text format inside the cell" says that there are 20,000 formulas in one cell. These are very different problems.


If it is one text-formatted formula per cell and if they all start with an = then all you need to do is change the cell formats to automatic. I don't think there is a simple way to make a keyboard shortcut for that but you can select all 20,000 cells and apply the automatic format to all of them at once.


If there are 20,000 "formulas" in one cell and if they are separated by a line feed and if they all start with an =,

  1. Select all the text in the cell
  2. Copy
  3. It might be necessary to enlarge your table so it has all the cells (cell addresses) that these formulas reference. Otherwise you might get reference errors when they become formulas
  4. For the column that is going to get all these formulas, format it as "automatic".
  5. Click on the cell where you want to start pasting your formulas (don't double click, just a single click to select the cell).
  6. Paste

They should paste into 20,000 cells in the column, all as formulas.




Jun 10, 2024 11:57 AM in response to Badunit

I think I wrote it wrong in the initial message; I have about 20,000 formulas in the entire Numbers file in question, one formula for each cell.

each cell has the text format of the formula in the cell, and I'm going to turn it into a real active formula. So the steps I intend to run through the script are:

1) selection of the entire contents of the cell (without having to double click on it).

2) copy all the contents of the cell, as well as the formula in text format (there is no "=").

3) perform the formula insertion operation, that is typing "=".

4) paste what you copied previously, that is the formula.


I would like a script that would make all this possible through the combination of two keys, for example the "option" + "z" keys.


Yesterday I managed to create a sort of shortcut, which involves using the "Control" + "uppercase" + "*" keys, but then I have to press the enter key, and overall I'm having trouble since I have to type four keys for each cell, which is inconvenient.

I await your reply, thank you.

Jun 11, 2024 5:56 AM in response to Yuri2302

It will be under the Numbers menu, not the Edit (Modifica) menu.

Numbers->Services


If the keyboard shortcut is not working, make sure that the menu title you entered there is exactly the same as it appears in the Numbers->Services menu, character by character. Any deviation at all and it will not work. The letter "a" is not the same as "A", for example.

Jun 11, 2024 12:58 AM in response to Badunit

Buongiorno, ho creato il servizio in Automator e poi nelle impostazioni della tastiera gli ho attribuito un'abbreviazione dei tasti. (Dove c'è scritto "formula di autoimpostazione).


Quando vuoi però mi reco in Numbers, visualizzo la barra del menu rapido, ovvero "modifica" e non appare alcuna azione rapida che ho creato.


perché non funziona?

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

creating a script for Numbers (and assigning key combinations)

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.