Run Script (excel) in Power automate

AT_M 11

Hi,

I created the sort script in excel online and connected to Power automate. It's set up with 'Recurrence' to run daily.
When I tested it as solo, it worked but it's failed when it's run by power automate. The error message was;
--- Quote ---
We were unable to run the script. Please try again.
Workbook not found.
clientRequestId: 0a43a19b-0b2c-4cfc-8ef9-3954a5767e0e
--- Unquote ---

I used workboog.getactiveworksheet method which is default behaviour in powerautomate.
Please advise 1) what is wrong about it and 2) is there any workaround this? Also, is there any method in script to clear table? Because, the table will be overlapped if I don't clear the table.

Look forward to hearing any idea on this.

Martikainen, Olli 0 Reputation points

2023-07-07T09:17:29.4466667+00:00
I am having similar issues. Running a script in Excel works fine, but trying to run that same script through power automate, I get an error.:

Cannot read properties of undefined (reading 'getRange')\r\nclientRequestId: 072594e6-58ca-44ba-b2b0-0b296ff4fb17",

6 answers

Carlos Felipe Lima Tecles 1

I´m also with the same problem.

Power Automate error:
We were unable to run the script. Please try again.
Runtime error: Line 18: Cannot read property '0' of null
clientRequestId: 783d7250-a8a7-4770-baa6-2a3dd1e39223

Script:
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();

// Ajusta Status
selectedSheet.getRange("C2").setFormulaLocal("=SE(E2<=HOJE();\"ATRASADO\";SE(E2-7<=HOJE();\"SEMANA\";\"EM ANDAMENTO\"))");

// Cola ajuste de status para outras linhas
selectedSheet.getRange("C2").autoFill();

// Formata colunas como data
selectedSheet.getRange("D:D").setNumberFormatLocal("dd/mm/aa;@");
selectedSheet.getRange("E:E").setNumberFormatLocal("dd/mm/aa;@");
selectedSheet.getRange("F:F").setNumberFormatLocal("dd/mm/aa;@");
selectedSheet.getRange("P:P").setNumberFormatLocal("dd/mm/aa;@");


// Converte datas de texto para data de fato
selectedSheet.getRange("D1").getResizedRange(0, selectedSheet.getRange("D:D").getValues()[0][0].toString().split(/[\t]/).length - 1).setValues([selectedSheet.getRange("D:D").getValues()[0][0].toString().split(/[\t]/)]);

selectedSheet.getRange("E1").getResizedRange(0, selectedSheet.getRange("E:E").getValues()[0][0].toString().split(/[\t]/).length - 1).setValues([selectedSheet.getRange("E:E").getValues()[0][0].toString().split(/[\t]/)]);

selectedSheet.getRange("P1").getResizedRange(0, selectedSheet.getRange("P:P").getValues()[0][0].toString().split(/[\t]/).length - 1).setValues([selectedSheet.getRange("P:P").getValues()[0][0].toString().split(/[\t]/)]);

// Formata coluna de matrícula para que apareçam todos os dígitos
selectedSheet.getRange("G:G").setNumberFormatLocal("0");

// Formata coluna de CNPJ para que apareçam todos os dígitos
selectedSheet.getRange("X:X").setNumberFormatLocal("0");

// Formata colunas de CPF para que apareçam todos os dígitos
selectedSheet.getRange("I:I").setNumberFormatLocal("0");
selectedSheet.getRange("N:N").setNumberFormatLocal("0");

// Dias Atraso
selectedSheet.getRange("AD1:AD2").setFormulasLocal([["DIAS_ATRASO"], ["=se(C2=\"ATRASADO\";HOJE()-E2;\"\")"]]);

// Teste CPF e idade RF
selectedSheet.getRange("AE1:AF2").setFormulasLocal([["TESTE_CPF_RF", "TESTE_IDADE_RF"], ["=SE(AC2=\"OUTROS\";SE(I2=N2;\"ERRO\";\"\");\"\")", "=SE(AC2=\"OUTROS\";SE(HOJE()-P2>365*60;\"IDADE ACIMA 60\";\"\");\"\")"]]);

}

Yutao Huang - MSFT 701 Reputation points Microsoft Employee

2022-04-04T05:30:54.783+00:00
Based on the error message, it was failing most likely because referencing some non-existent entities (e.g., worksheet, range, table, etc.).

Here are some troubleshooting ideas:

Instead of using workbook.getActiveWorksheet(), use things like workbook.getWorksheet("Sheet1"). Please refer to this to see how to avoid relative references: https://learn.microsoft.com/en-us/office/dev/scripts/testing/power-automate-troubleshooting#avoid-relative-references.

Use console.log(...) statements in your code to output some troubleshooting information, e.g.: find out how your script executes, and on which line it fails, etc. You can expand the "Run script" action in the Power Automate run history of your flow and look for the logs in the body box.

Try to break down long chaining methods like selectedSheet.getRange("E1").getResizedRange(0, selectedSheet.getRange("E:E").getValues()[0][0].toString().split(/[\t]/).length - 1).setValues([selectedSheet.getRange("E:E").getValues()[0][0].toString().split(/[\t]/)]) into shorter statements, use temp variable to capture the values and output to console.log() and see if the values are expected.

Hope this helps!

Share via

Run Script (excel) in Power automate

6 answers

Your answer