Automatic Update Shortcut for Numbers Spreadsheet

This shortcut and script creates an efficient bill management process utilizing a Numbers document and apple's schortcut. By automating the process of updating the due dates and balances of any selected bill. When executed, the shortcut presents a list of bills, allowing users to select one, multiple, or all bills for updating. That information is passed through the shortcut to the Javascript to preform the updates.

It can only be ran on an Apple computer, JXA is not able to be used on phones. I am not sure about iPads.

What it does

Upon input reception, the script performs the following operations:

Data Preparation

• The specified sheet is accessed, and the script verifies the presence of input data.
• Utilizing the known bill column, the script constructs a lookup map to expedite bill row identification.

Iteration and Data Capture

• The script iterates through the list of selected bills to locate their respective rows within the sheet.
• Current bill data, including due date, pay frequency, payment amount, and balance, is extracted and stored.

Data Validation

• For each bill, if any data points (due date, pay frequency, or payment amount) are null, appropriate messages are generated and appended to an end message array.

Date and Balance Adjustment

• If the due date is before the current date, the script evaluates the pay frequency and increments the due date accordingly.
• If a balance exists, the payment amount is subtracted from the balance, and the updated balance is set in the spreadsheet.

Document Update and Response

• The Numbers document is saved to persist the changes made.
• The script generates a response containing the updates made, facilitating communication with external processes (e.g., shortcuts sends message).

Getting it work on your computers

Apple computers have an application called shortcuts. In shortcuts you can utilize apple's script editor/automator by using the Run JavaScript for automation (you can also use Applescript or shell scripts, depending on what you want).

Importing the shortcut from shared sample link

1. Click this iCloud link and click add shortcut https://www.icloud.com/shortcuts/bf019df510ff4df58384066abf5d8750 - If you know the information for the import questions you can fill them out now or they can be edited after the import
2. Right click the shortcut and click edit
3. Edit the list values to be the ones in the spreadsheet
4. Edit the value of the spreadsheet to open to the one you are using
5. Add the proper recipient to the send message
6. Click the information button in the upper right hand corner
7. Make sure the settings match the screenshot to allow the proper access of the shortcut steps to your items

How to make the shortcut yourself

1. Open Shortcuts.

2. Click New Shortcut in the upper right hand corner.

3. From scripting select (in this order):

   • List

   • Choose from List

   • Add to Variable

   • Open Spreadsheet

   • Run JavaScript for Mac Automation

   • Send Message

4. Add the bills needed to be selected from the list to the list values.

5. Set the choose from list to select from the list you made if is not set.

6. Set the chosen item to the variable named as you want.

7. set the spreadsheet to be opened to the specifc spreadsheet you will use.

8. Copy the JS from automationFunc.js and replace the default text in the Run JavaScript for Mac Automation spot.

9. Set the message to be sent when updated and the recipient to who you want.

Demo

A video showing the spreadsheet at first, closing it and then running the shortcut that automatically opens the spreadsheet again and updates the values.

2F19B47D-B31C-4E41-A6B8-5142B81971D5.2.mov

The message sent from the screen shot about the bills selected