Pass excel data to to other excel Script (all in excel online)

Question

Hi, I'm very new in Excel , I have two excels, in excel A I have a table with all departments (with id),

in the Excel B I have a table with only 1 department(with id), I want to change values on the excel B and update in the excel A that haves all departments.

thanks!

(they want it with a script in excel online)

jkpieterse · Answer

You will need both a script and a Power Automate flow to be able to pull this off. The Flow would be triggered by a change in workbook B, which in turn calls the script. The script takes the values from workbook B to update workbook A.

stanis1 · Answer

Thanks for the reply Jan! My question is how to store value of one workbook to other?
And in this point of formula : targetTable.addRows(1, rowsToMoveValues); this add me a row in last , how can I add the row where IDSource=IdTable2? Like update, thanks!

jkpieterse · Answer

Stanis1&nbsp;See:&nbsp;https://learn.microsoft.com/en-us/office/dev/scripts/tutorials/excel-power-automate-trigger&nbsp;

stanis1 · Answer

Hi again! I've tried but i have issues,I don't know how to update the data , my scripts in automate flow only get data of origin table and parse in json array to the destionation table,I want to update with the ID, (for example,in origin table I have "ID:1, Name:Alex,Age:23" and in the destination table i have the same row, so when I update Alex in origin "ID:1,Name:Alex,Age:33", i wan to be changed in destination table,here my two scripts:SCRIPT 1:function main(workbook: ExcelScript.Workbook): string {let sh: ExcelScript.Worksheet = workbook.getActiveWorksheet();//get tablelet tbl: ExcelScript.Table = sh.getTable("taulaProva1");//get table's column countlet tblColumnCount: number = tbl.getColumns().length;//set number of columns to keeplet columnsToKeep: number = 4;//set the number of rows to removelet rowsToRemove: number = 0;//resize the table rangelet tblRange: ExcelScript.Range = tbl.getRangeBetweenHeaderAndTotal().getResizedRange(rowsToRemove, columnsToKeep - tblColumnCount);//get the table valueslet tblRangeValues: string[][] = tblRange.getValues() as string[][];//create a JSON stringlet result: string = JSON.stringify(tblRangeValues);//return JSON stringreturn 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 arraylet tableValuesArray: string[][] = JSON.parse(tableValues);//gets row count from the arraylet valuesRowCount: number = tableValuesArray.length - 1//gets column count from the arraylet valuesColumnCount: number = tableValuesArray[0].length - 1//resizes the rangelet rang: ExcelScript.Range = sh.getRange("A1").getResizedRange(valuesRowCount, valuesColumnCount)//sets the value of the resized range to the arrayrang.setValues(tableValuesArray)}I will appreciate any solution ,thanks a lot!

jkpieterse · Answer

To properly test this, I would find a way to see what the JSON looks like exactly. The create a new script which contains two functions: the Main function, which reads one row of your table. And an UpdateTable function which does exactly what your update script is supposed to do. That way, you can easily test whether the update function works

Forum Discussion

Pass excel data to to other excel Script (all in excel online)

5 Replies

Resources