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

Occasional Contributor

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)

5 Replies
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.
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!
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 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 appreciate any solution ,thanks a lot!
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