Basics of my setup. Working in single workbook with multiple worksheets in a 365 environment tied in to a Power Automate setup on Windows. Workbook will need to be accessible by multiple people and online, hence use of Office Scripts rather than VBA.
I'm a relatively advanced Excel user when it comes to formulae, but still very much a novice when it comes to proper coding (beyond very simple VBA for easy things like clearing a set number of cells). Fine working in Power Automate to set up relatively simple custom jobs (e.g., form filled in, send custom email based off contents of form to specific recipient).
I have several problems which I think are probably best solved by TypeScript, but my limited coding ability is holding me up, and I'm short on time at work to do this job and brush my coding up to the required standard.
All of the below problems are happening automated overnight, in a sequence eventually. These all also have to be tolerably maintained by someone if I leave the role, so they need to be easily documentable - hence why I've done a lot of work in actual formulae or in Power Automate, where people with a lower technical skill level than me will have a better chance of fixing it.
Problem 1 - Modifying given list of cells to same value
Aim - A script (or other solution) that changes each of a list on a worksheet (Sheet_SE) of cells on a separate worksheet (Sheet_TR) to the same string ("Sent").
On Sheet_SE, I have a dynamic formula (based on Unique) that produces a list of the cells which require updating (in the format Sheet_TR!$A$1 with a new cell for each reference), or returns FALSE if there are none to update. I also have a calculation of the number of cells which need to be updated (this number is never going to be above 100, and realistically unlikely to be above 20 on any one run), and a cell which returns the reference of the end of the list.
In my head, the easiest way is to check if there are cells to update, and if so to load the list of cells into an array in TypeScript, then run a loop over that array that sets the value of each cell to the required string. However, I am struggling to put it into practice.
Problem 2 - Adding an array to an existing table
This one is probably easier, and I have spent far less time trying to solve it as a result. If this seems achievable in Power Automate, please tell me - I'm much more competent in that than in TypeScript!
Aim - To add a n*2 array from one worksheet (Sheet_TA) to the bottom of a table (Table_ED) in specific columns on a separate worksheet (Sheet_ED), with the table and auto-formulae in other columns expanding to generate new data.
On Sheet_TA, I've used dynamic formulae to create a n*2 list of cells (where again, n is likely to go above 20 on any one run and never above 100). I also have a calculation of what n is.
I have some TypeScript code to add a new row to the bottom of Table_ED - simply Table_ED.addRow(-1, NewRow), where NewRow is an 1*Q array of the correct size and the first two elements will be the first 1*2 slice from the list.
Again, I think looping this code over an array in TypeScript is probably easiest, but I'm struggling.
Problem 3 - Delete all but one row from a named table
I think this is easily done in Power Automate (probably something like this), given I'll be using that for setting up the automation and integration with Outlook that I require. But please let me know if you think I'll be wasting time pursuing that solution.
Problem 4 - After date in a row passes TODAY(), move an entire row (values only) from one table to another
Again, I'm relatively confident that this can be achieved in Power Automate without much effort, but please let me know if you think I'll be wasting time pursuing that solution.
Thanks in advance.
I'm a relatively advanced Excel user when it comes to formulae, but still very much a novice when it comes to proper coding (beyond very simple VBA for easy things like clearing a set number of cells). Fine working in Power Automate to set up relatively simple custom jobs (e.g., form filled in, send custom email based off contents of form to specific recipient).
I have several problems which I think are probably best solved by TypeScript, but my limited coding ability is holding me up, and I'm short on time at work to do this job and brush my coding up to the required standard.
All of the below problems are happening automated overnight, in a sequence eventually. These all also have to be tolerably maintained by someone if I leave the role, so they need to be easily documentable - hence why I've done a lot of work in actual formulae or in Power Automate, where people with a lower technical skill level than me will have a better chance of fixing it.
Problem 1 - Modifying given list of cells to same value
Aim - A script (or other solution) that changes each of a list on a worksheet (Sheet_SE) of cells on a separate worksheet (Sheet_TR) to the same string ("Sent").
On Sheet_SE, I have a dynamic formula (based on Unique) that produces a list of the cells which require updating (in the format Sheet_TR!$A$1 with a new cell for each reference), or returns FALSE if there are none to update. I also have a calculation of the number of cells which need to be updated (this number is never going to be above 100, and realistically unlikely to be above 20 on any one run), and a cell which returns the reference of the end of the list.
In my head, the easiest way is to check if there are cells to update, and if so to load the list of cells into an array in TypeScript, then run a loop over that array that sets the value of each cell to the required string. However, I am struggling to put it into practice.
Problem 2 - Adding an array to an existing table
This one is probably easier, and I have spent far less time trying to solve it as a result. If this seems achievable in Power Automate, please tell me - I'm much more competent in that than in TypeScript!
Aim - To add a n*2 array from one worksheet (Sheet_TA) to the bottom of a table (Table_ED) in specific columns on a separate worksheet (Sheet_ED), with the table and auto-formulae in other columns expanding to generate new data.
On Sheet_TA, I've used dynamic formulae to create a n*2 list of cells (where again, n is likely to go above 20 on any one run and never above 100). I also have a calculation of what n is.
I have some TypeScript code to add a new row to the bottom of Table_ED - simply Table_ED.addRow(-1, NewRow), where NewRow is an 1*Q array of the correct size and the first two elements will be the first 1*2 slice from the list.
Again, I think looping this code over an array in TypeScript is probably easiest, but I'm struggling.
Problem 3 - Delete all but one row from a named table
I think this is easily done in Power Automate (probably something like this), given I'll be using that for setting up the automation and integration with Outlook that I require. But please let me know if you think I'll be wasting time pursuing that solution.
Problem 4 - After date in a row passes TODAY(), move an entire row (values only) from one table to another
Again, I'm relatively confident that this can be achieved in Power Automate without much effort, but please let me know if you think I'll be wasting time pursuing that solution.
Thanks in advance.
