Office Script, update table from another, worked now failing

Question

I used what I found Here and it worked great for a few months. My Workbook would always have the latest table update. Starting today it fails every time. Referencing Script 2 during the Flow-

Worksheet getRange: The request failed with status code of 504, error code UnknownError"

//resizes the range let rang: ExcelScript.Range = SelectedSheet.getRange("A2").getResizedRange(valuesRowCount, valuesColumnCount)

Script 1 =

function main(workbook: ExcelScript.Workbook)
{
let selectedSheet = workbook.getActiveWorksheet();
let usedRange = selectedSheet.getUsedRange();
// Delete range B:D
selectedSheet.getRange("B:D").delete(ExcelScript.DeleteShiftDirection.left);
// Delete range G:I
selectedSheet.getRange("G:I").delete(ExcelScript.DeleteShiftDirection.left);
// Delete range L:L
selectedSheet.getRange("L:L").delete(ExcelScript.DeleteShiftDirection.left);
// Delete range M:M
selectedSheet.getRange("M:M").delete(ExcelScript.DeleteShiftDirection.left);
// Delete range N:S
selectedSheet.getRange("N:S").delete(ExcelScript.DeleteShiftDirection.left);
// Delete range P:X
selectedSheet.getRange("P:X").delete(ExcelScript.DeleteShiftDirection.left);
// Delete range R:AW 
selectedSheet.getRange("R:AW").delete(ExcelScript.DeleteShiftDirection.left);
let newTable = selectedSheet.addTable(usedRange, true);
//get table
let tbl: ExcelScript.Table = selectedSheet.getTable("Table1");
//get table's column count
let tblColumnCount: number = tbl.getColumns().length;
//set number of columns to keep
let columnsToKeep: number = 22;
//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;

Script 2 =

function main(workbook: ExcelScript.Workbook, tableValues: string) {
let selectedSheet = workbook.getActiveWorksheet();
let SelectedSheet: ExcelScript.Worksheet = workbook.getWorksheet("Database")
let usedRange = selectedSheet.getUsedRange();
//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 = SelectedSheet.getRange("A2").getResizedRange(valuesRowCount, valuesColumnCount)
//sets the value of the resized range to the array
rang.setValues(tableValuesArray)
// Fit the width of all the columns in the Table.
SelectedSheet.getUsedRange().getFormat().autofitColumns();
selectedSheet.getUsedRange().getFormat().setHorizontalAlignment(ExcelScript.HorizontalAlignment.left);
}

Any idea what I am doing wrong?

What are the values of valuesRowCount and valuesColumnCount set to when the script runs? — Skin
– Skin, Commented Apr 1, 2022 at 0:59
I updated the original post with both scripts, I should have done that from the get go. Thanks. — Bill R
– Bill R, Commented Apr 1, 2022 at 13:19

Petra · Accepted Answer · 2022-04-04 16:47:56Z

0

Are you running Script 1 as part of a Flow too? If so, you should change this line in Script 1:

let selectedSheet = workbook.getActiveWorksheet();

When you run a script as part of a Flow, you need to specify the worksheet by ID or name. So, for example, you would change the line to something like:

let selectedSheet = workbook.getWorksheet("Sheet1")

where Sheet1 is the sheet you want to add the table to.

You have two different sheet variables in Script 2 as well. One is 'selectedSheet' and the other is "SelectedSheet'. You reference both later in the script. Do you mean to have both? And again, for scripts running as part of a Flow, always specify the sheet by ID or name that you are trying to reference. Let me know if that fixes things!

answered Apr 4, 2022 at 16:47

Petra

4162 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brian Gonzalez · Accepted Answer · 2023-01-13 01:23:54Z

0

Have you tried rerunning your flow? It's possible there could have been some temporary issue with PowerAutomate. So perhaps the service was down.

The only potential issue I see is this

      let newTable = selectedSheet.addTable(usedRange, true);
      //get table
      let tbl: ExcelScript.Table = selectedSheet.getTable("Table1");

The newTable's name is not guaranteed to be Table1. So if it wasn't, your flow might not work correctly. You could get around that by setting tbl to the newTable. So you could either write code like this:

      let newTable = selectedSheet.addTable(usedRange, true);
      //get table
      let tbl: ExcelScript.Table = newTable;

Or just update tbl to code like this:

      let tbl: ExcelScript.Table = selectedSheet.addTable(usedRange, true);

edited Jan 13, 2023 at 1:23

answered Apr 1, 2022 at 14:54

Brian Gonzalez

1,3481 gold badge7 silver badges18 bronze badges

4 Comments

Bill R Over a year ago

Hi Brian! I updated my script just in case the table name does change, thanks for the tip. Still not working, failing with the same error. Maybe MS is having an issue, I don't understand why they can't give more explanation to failures.

Bill R Over a year ago

Everything works prefect until it gets to my workbook. I created a new worksheet / table and updated my script 2 to go there. Worked perfect. Went back to my original table, it had been resized among other things. Now it is time to figure out how to lock out sheets. Thank you!

Bill R Over a year ago

@ Brian in your oriignal post your said "add the output of the first script into an Excel table. And just empty out the table every time you run the second script." Can you give an example of how you would do this?

Brian Gonzalez Over a year ago

@BillR Sure. If you can post as another question on StackOverflow I can provide an answer.

Collectives™ on Stack Overflow

Office Script, update table from another, worked now failing

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related