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 Javasctipt 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 editied after the import

Right click the shortcut and click edit

Edit the list values to be the ones in the spreadsheet

Edit the value of the spreadsheet to open to the one you are using

Add the proper recipent to the send message

Click the information button in the upper right hand corner

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 Javasctipt 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](./automationFunc.js) and replace the deafult text in the `Run Javasctipt for Mac Automation` spot. 9. Set the message to be sent when updated and the recipent to who you want.

Demo

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

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

The message sent from the screen shot about the bills selected

bbland1/JXA-bills-spreadsheet