Forum Discussion
Pass excel data to to other excel Script (all in excel online)
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...
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!
