Excel update rows with script (two tables)

[Stanis1]

Hi, I'm very new at excel online and typescript,I have two tables,in table 1 there are records of employees and their departments like "ID:1 ,Name:Alex,Department:2", and in the table 2 I have only the records of one department. I want to be able to when I change values in table 2 automatically update the values in table 1, look the ID and change the value of the row.

I'va tried with scripts and PowerAutomate flow , but only parses de data of all the table....here my code :

SCRIPT1:

 function main(workbook: ExcelScript.Workbook:( string {

        let sh: ExcelScript.Worksheet = workbook.getActiveWorksheet();

        //get table

        let tbl: ExcelScript.Table = sh.getTable("taulaProva1");

        //get table's column count

        let tblColumnCount: number = tbl.getColumns().length;

        //set number of columns to keep

        let columnsToKeep: number = 4;

        //set the number of rows to remove

        let rowsToRemove: number = 0;

        //resize the table range

        let tblRange: ExcelScript.Range = tbl.getRangeBetweenHeaderAndTotal().getResizedRange(rowsToRemove, columnsToKeep - tblColumnCount);

        //get the table values

        let tblRangeValues: string[][] = tblRange.getValues() as string[][];

        //create a JSON string

        let result: string = JSON.stringify(tblRangeValues);

        //return JSON string

        return result;

    }

SCRIPT2:

function main(workbook: ExcelScript.Workbook, tableValues: string) {

  let sh: ExcelScript.Worksheet = workbook.getActiveWorksheet();

  let tbl: ExcelScript.Table = sh.getTable("taulaProva1");

  //tbl.getRangeBetweenHeaderAndTotal().delete(ExcelScript.DeleteShiftDirection.up);

    //parses the JSON string to create array

    let tableValuesArray: string[][] = JSON.parse(tableValues);

    //gets row count from the array

    let valuesRowCount: number = tableValuesArray.length - 1

    //gets column count from the array

    let valuesColumnCount: number = tableValuesArray[0].length - 1

    //resizes the range

    let rang: ExcelScript.Range = sh.getRange("A1").getResizedRange(valuesRowCount, valuesColumnCount)

    //sets the value of the resized range to the array

    rang.setValues(tableValuesArray)

}

I will apreciate any help, thanks a lot!!!

[#2]

Why don't you 'build' table 1 based on table 2 (and maybe other tables)
It would be better to have a 'master' list (i.e. table 1) and then use pivot table or FILTER to show the sub-list (i.e. table 2) but (especially with the new functions like vstack) you could have a table for each department and then make table 1 a combination of those sub-tables so table 1 would be for viewing/reporting and sub-tables 2-x would be the 'master' tables.

[#3]

I think that this is very good idea,but I work in bussines and they want it like that, in script...
I don't know so much of scripts, i only want solution thaht updates the master table when one of secodary tables are edited,thanks!

[#4]

I've only dabbled in scripts but my recollection is that a script doesn't have an on-event equivalent. By that what I mean is I know that macros have events that are triggered and you can write code to happen when data changes, but I believe a script has to be manually run. They have added buttons to make that easier to do but a button would still need to be clicked to execute the update script.

[#5]

For who triggers the script don't worry, all is in powerautomate flow, mtarler,you know what code I have to write in the sripts for update one to other? thanks!