Creating a Form In Mac Numbers Using a Script

macmost.com/e-2086 One advantage that Numbers for iPad has over Numbers for Mac is Form mode. If you want to simulate a form in Mac Numbers, you can do it using a few lines of JavaScript code in Script Editor and even make those script available in the menu bar. This could be the starting point for a more complex set of scripts that check values and even allow you to edit row data in a form too.
FOLLOW MACMOST FOR NEW TUTORIALS EVERY DAY
▶︎ KZbin - macmost.com/j-youtube (And hit the 🔔)
▶︎ Weekly Email Newsletter - macmost.com/newsletter ✉️
▶︎ Twitter - rosenz and macmost 🐦
▶︎ Facebook - macmost 👍
SUPPORT MACMOST AT PATREON
▶︎ macmost.com/patreon ❤️
#macmost #numbers #javascript

[@bananababy605]

This video has been a great resource for me getting started with javascript with numbers. I've used it and figured out a few things on my own to get pretty far. I was wondering what resources are available to see the available functions? There doesn't seem to be very much. I found the library within script editor itself but it lacks a little information.

[@homemixer]

This so helpfull and make working with mac really easy! Thank you so much!

[@treyjimmy3589]

I know Im asking the wrong place but does anybody know a trick to get back into an instagram account..? I somehow forgot my password. I would appreciate any help you can offer me!

[@davitavye]

THANK YOU!!!!

[@emanuelfrfr]

Hey nice Video, do you know how to rewrite the last part of the code to ignore one cell while deleting and jumping back to the first cell. (The cell contains drop down and should not be deleted with the other ones?

[@darrylczuy7244]

Love the video, works perfect for what I'm doing, question I have is how do you clear data from a table with Multiple columns. I want to remove the data from Column D and the script as written clears it from a the form which only has one column

[@akackman3657]

Is there a way to clear the cells in the form at the end but have the cells retain spreadsheet formula setting when it is cleared?

[@brandonandreasen7387]

Pretty cool is there no way to have a enter button on sheet 2 just wondering

[@GilloGilloGillo]

Can you suggest specific JavaScript’s books for iOS?

[@franzpaterna8271]

Now it is better understanding. Can you also make a video with AppleScript? It should explain how to clear text from the column B to D. Also you can record actions (like copy text from C4 to C9). How can I record this with AppleScript in numbers?

[@duanmaomao]

Is that possible to get values of cells directly? You are using a loop. does it work somehow: values = table.ranges["A2:A20"].values().

[@ajmlam]

This video was very helpful. The problem I am having is the script will not run automatically in Numbers. I followed the instruction to add the sirup and select. I notice it added "// function run(input, parameters) {" to my Script." is there something I am missing?

[@sharon2436]

Hi Gary…. I am not such a whiz with Javascript and need a bit of help. I have 2 spreadsheets that have multiple tables on each spreadsheet. A checking account register (Main Table: Checking Account) and a savings account register (Main Table: Savings Account) with sinking funds, too. Right now, if I enter a Transfer to Savings in my checking register, I have to manually go to the Savings Sheet and enter the Transfer details into the register. I have specific Categories set up in my check register using a Pop-up Menu with a value of TSFR Savings to mark the transfer to savings category. Is there a way to create a script that when I select TSFR Savings that it automatically runs the script and transfers the data from that row to a new row in my Savings ledger? Or maybe a button I can create that when I press it, it looks for all TSFR Savings entered today and transfers that data to the Savings account sheet & table?

[@staceycarless]

It works fine, but I can’t get the script icon to appear in the top bar of numbers

[@IhabAlghazal]

Thanks for the video. I have a question what would the code be if I had multiple database sheets and each one has a name and I want the values to be added to the sheet that carries the same name as the first cell of the form?

[@macmost]

Hard to say without seeing it (and working on it). Start by learning what I show here, then try to modify in small steps.

[@christinebryant9143]

I made a form and the script worked but it only added the first field - I only wanted 2 fields so I added a third dummy . Not sure why it wouldn't accept just 2. BUT I tried to make a keyboard shortcut and failed. I added one to the Script Editor App to run my form - exact spelling 'Script Run Form' - can you help please to solve my 2 mysteries. Thank you Gary - I love watching your videos all the way from the UK..

[@mysticsunonline]

Thanks for the Great tutorial...Question: On my i-Pad is there another path than Javascript to update cells like you are doing in your tutorial which a desktop solution. It seems Javascript has been phased out in IOS to a degree. Are there other paths for moving data around in Apple Numbers for ipad or IOS devices.

[@macmost]

Phased out? It was never there. You can't do this on the iPad as there is no way to write scripts like this.

[@Rob-bc4yu]

Big fan of apple for video/music creation/graphic design/photography....but numbers pales in comparison to Excel. And I pretty much hate all things Microsoft lol. No ability to run/record a macro, very little scripting support unlike VBA, etc. but appreciate the content, very helpful thx!

[@chrisfikes]

thanks so much for this tutorial, is there a way to control the formatting of the new row that is created. I want it to input the data with a currency formatting but it keeps adding it in as percentage and then I have to manually go back and reformat the new data that was entered by the script

[@macmost]

That's odd. Try changing the format for the entire column and ensure than when you manually add a new row in Numbers, it does it with that same format.

[@chrisfikes]

@@macmost it still puts in the new data as percentage; is there a format cells command in applescript?

[@macmost]

@@chrisfikes I don't think so, but look. Have you tried formatting the data in your script? Like including the "$"? Or, try it with a new Numbers document and see what happens.

[@proteus1]

I wish to use the template invoice availble in number, but wish to have automation, so each time its opened or used so it does an automatic invoice number count? Can you help?

[@macmost]

Not exactly sure what you are trying to do, but automation probably isn't the way to go.

[@alexrasch2626]

Thanks for the video Gary! Is there a way to make a script that pulls data from a PDF file and automatically adds the data to a numbers table? I.e. a bank statement that is automatically put into numbers?

[@alexrasch2626]

Could you make a video on this?

[@macmost]

That would be difficult to do, if possible at all. Banks usually let you download spreadsheets already. Or if the data is in a table-like format, try copy and paste.

[@dannylolg]

Hi thank you so much for all your videos very helpful. I have a question and I’m hopeful you can help me with it. I’m currently using the formula countif to give me a result corresponding to what I need ( contif A1,>O) so I aways get the results of 1 when the number on that cell is greater then 0) I would like to add a second function there but can’t seam to figure out how to go about it. I need the results of the formula to be 1 for any number great then 0 and -1 if the the number is less the . Example if cell A1 is ( 2 ) the result is 1, if cell A1 is (-2) the result is -1. Hope it makes sense. Thank you in advance 🙏🙏🙏🙏

[@macmost]

Create a new column. In that column use a nested IF statement to test for 0 and return the right result. Then use SUM to add up that column.

[@dannylolg]

macmostvideo thank you so much for your reply, I’m struggling to figure it out the complete formula for this 😅🤯

[@macmost]

@@dannylolg I have a tutorial on IF functions that may help: kzbin.info/www/bejne/anzHo2yabb-fbM0

[@dannylolg]

@@macmost thank you so much life saver :) done it , I created a column and added the formula on Cell O144 - :IF(SALE 05 UNITS

[@justdnk777]

Getting error from Application("Numbers"), any idea why?

[@etienneb4403]

Is it possible to put a button in the table with that script?

[@macmost]

Unfortunately not.

[@ernestoditerribile]

Is there a way to clear certain cells with a button. Like the way we switch to another page on the sheet or a website within numbers.

[@macmost]

You could do it if you know how to code. You could make an Automator script to do it. Not sure if that is in your set of skills so it is hard to recommend.

[@ernestoditerribile]

@@macmost I know how to Apple use Apple Scripts. But I don’t know if the scripts export to numbers in iOS, iPadOS and Web. Have converted a calculation sheet for HVAC made in XLSM with VBA Macros. I could convert all VBA to make all icons do what they did in Excel. But usually the guys who make the quotations and invoices, did it on Macs and Windows computers using Macs. I just converted it so they can do it on the fly as iPadOS, iOS and Web versions of Office 365 doesn’t support VBA Macros. Numbers does read the Excel file, but can’t do all the macro stuff so had to link so much fields to each other

[@holly9833]

I own a cab company and we are using numbers to track most things. We are looking for help. Do you have other resources?

[@PaoloMescoli]

Can you make that script available for downloading? I would like to try it.

[@macmost]

It is. Just click on the link in the description.

[@paxbritannica5598]

Is it possible to run an applescript from a pages file which has cells in it (my work case reports) to transfer data automatically onto a spreadsheet?

[@macmost]

Confused by what you are trying to do. You may be able to write something complex in Automator to do what you want. It would take a lot of work and a high level of coding skill. I'd consult with a coder about it if you want to proceed.

[@paxbritannica5598]

@@macmost I'm just trying to do the same thing as in the video except between two apps, pages and numbers

[@brianforrest6047]

Hi Gary, I am using your scripts with my spreadsheet. I see you have 2 Columns labeled "A". How do I do that for my spreadsheet Form?? Thanks, Brian

[@macmost]

Not sure what you mean. There is only one A column in a table. But I am using multiple tables here.

[@BabaGanoush512]

Thanks for this! Im very new at this, I followed step by step and copied everything as is but I keep getting an error Error -1700: Can't convert types. If I look under Replies I get this. app = Application("Numbers") app.documents.at(0).sheets.at(1).tables.byName("Data Entry").rows.length --> 4 app.documents.at(0).sheets.at(1).tables.byName("Data Entry").rows.at(0).cell.0.value() --> Error -1700: Can't convert types. Result: Error -1700: Can't convert types. Any help understanding this would be greatly appreciated. Thanks again.

[@BabaGanoush512]

Found the issue :)

[@justdnk777]

@@BabaGanoush512 May I how you fixed it, Rick? thanks!

[@mikeneirinck189]

can the script be tied to the spreadsheet instead of the app?

[@macmost]

Not sure what you mean. It is tied to a spreadsheet.

[@mikeneirinck189]

@@macmost If you would share the spreadsheet with other users on another Mac or even an iPad/iPhone would the script be available?

[@macmost]

@@mikeneirinck189 No. The script is a separate piece. Automator is a Mac app, so even if you sent them the script, there is no way to run it on an iPhone or iPad. But the iPad version of Numbers already has forms.

[@mikeneirinck189]

@@macmost It does. As the number of added records could increment fast I am a bit scared numbers might slow down. So I am considering simply adding a value to the previous one and that requires a script.

[@Curru21]

This is an useful script and I love it, but you make a script where you need to add a pop up menu in the form table and then the script identify what table needs to fill according to the selection of the pop up menu? Regards

[@macmost]

Yes, probably. It will take some work but if you have some coding skill you can probably work something out.

[@antoniagia6482]

T Tutorial in italiano, grazie

[@aayushjain2544]

how can we add shortcut to that button

[@macmost]

To the menu item that runs the script? Try doing it in System Preferences, Keyboard, Shortcuts.

[@alyceyee5011]

@@macmost hi sir! can you explain in details how can I add keyboard shortcut to "Submit Form"?

[@macmost]

@@alyceyee5011 kzbin.info/www/bejne/lWWanH2siJ2hns0

[@SimanjeetSingh-hf2xy]

@@macmost that isn't really helping. Could explain the process, Cause in the video it does it for only the left side of the menu bar and script is on the right side.

[@SteveFlatEarth]

I'm ok at AppleScript programming but I'm a little lost with what you are doing. I sent you an email. Hopefully it gets to you. I'm basically trying to move a row of data in one table to a particular row in another table on the same sheet. It's possible? What programming language is best to use? Javascript?

[@macmost]

Use AppleScript or JavaScript, whichever you are more comfortable with. But how often do you need to do this? It is easy to do with copy and paste. I would only work to create a script if you have to do this so often that the time it takes to create the script will pay off. Even then, maybe there is some other workflow that could lead to not having to do this so often.

[@SteveFlatEarth]

@@macmost I have to do it every 30 minutes so definitely worth it to script it. I'm stumbling trying to script it but hopefully will figure it out. Trying to use your javascript and modify that because I'm not sure how to do it with applescript. Can't find any examples.

[@SteveFlatEarth]

@@macmost Do you know if there is a javascript command to force STOCK function to pull new data without the necessity of restarting numbers? Also I'm following your example but I don't want to create a new row since my table is precreated at the beginning of each day manually. I just want to transfer the data from the column of one table to a column of another data and I want it to paste the formula results since the originating table is a formula. Just stuck on these various parts. Otherwise it's close to working :-)

[@macmost]

@@SteveFlatEarth The STOCK function in Numbers only gives daily numbers (FYI). But it should update automatically when you open the document. Otherwise, just change one cell to update all formulas. A cell with a checkbox in it will work for this purpose.

[@SteveFlatEarth]

@@macmost Thanks. I'm still struggling with converting your example to my needs. I just need to transfer one column of data in one table to an existing column in another table in the same sheet but I have zero experience with Javascript so I'm stumbling a bit. If you can check my email I think it's something very easy to do. I'm willing to pay you for your time. Thanks.

[@thebittenmac]

Or use a database app …

[@AppleSoft]

But which one on a mac? Any suggestions?

[@ernestoditerribile]

@@AppleSoft FileMaker Pro

[@v4uevents]

can we create form… WITHOUT using script ?

[@macmost]

Not in Numbers on the Mac. You can do it in web-based systems like Google Forms. It depends what you are trying to DO.

[@spacedudey2k]

I will NEVER understand programming

[@krunchyboi107]

This is so awful that Mac has made it too difficult and it can't be simple as it was in the past...Too much work for the average user

[@Trackhoe075]

Maybe it’s me but why make this so damn difficult just enter the number on your first sheet? Easy peasy !!! I don’t get how writing JavaScript makes this sensible! Geez just enter it once in the first sheet! I’m certainly not gonna spend hours learning JavaScript to enter 4 numbers nope not me! I got other problems if I can’t enter info in four cells like major problems! First of all I would duplicate whatever setup on both handheld and desktop! The info on both desktop and iPhone or iPad in either numbers or pages, and both will be updated over WiFi …..DONE

[@rubberk]

kzbin.info/www/bejne/Z5SxhXyhZrBqaLc

[@franktognini4786]

I watch serval of your video that I like but I keep going to different video and you never show a simple step, where do you switch to script editor, each video where you talk about script editor I have to stop the video and give up because I cannot find that. so simple and you just go to it assuming we all know how to do that, so frustrating