Forum Discussion
Stanis1
Oct 25, 2022Copper Contributor
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...
Stanis1
Oct 26, 2022Copper Contributor
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!
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
Oct 26, 2022Silver Contributor
Stanis1 See: https://learn.microsoft.com/en-us/office/dev/scripts/tutorials/excel-power-automate-trigger
- Stanis1Oct 26, 2022Copper ContributorHi 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!- JKPieterseOct 26, 2022Silver ContributorTo 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