Run Office Script error

[twarner]

Hi! I'm a new Power Automate user and trying to setup what I thought would be an easy flow.

I have an Office Script written in Excel that I want to run on a schedule. I found the flow template for this: 1. Recurrence step 2. Run script step.  The setup is very straight forward - I've got my Excel file in Onedrive and selected the one Office Script embedded therein. When I try to test it, it immediately errors out:

We were unable to run the script. Please try again.
Compilation error: module.ts(51,49): error TS1005: ';' expected.

clientRequestId: 33313a85-8caa-4e64-8f66-64b90e99541e

Is there something I'm missing?

Thanks,

Tony

[mjburley · Accepted solution]

Does the Excel script work within Excel itself - the error implies the script in Excel is at fault - maybe post that script here?

[twarner]

mjburley -

Thank you for your response.  Yes, the script works as expected in Excel online. I had written this in VBA for Excel, but decided I needed to run it automatically, so converted it to Office Script (first time trying Office Script, so please forgive me if it is not very elegant):

function main(workbook: ExcelScript.Workbook)
{

// Create an object to access the source data worksheet
  let sh = workbook.getActiveWorksheet();

// Create a new worksheet for the results
 const results_sh = workbook.addWorksheet("Results")

// Declarations
//let arrPrjDate = []
  let numCols = 51;
  let ResultRowOffset = 1


  //get the current used range and lastRow
  let range = sh.getUsedRange();
  let rangeValues = range.getValues();
  let lastRow = range.getRowCount();
  const textRangeofRows = "A2:A" + lastRow
  const textResultRange = "A1:A" + lastRow
  let ResultsRange = results_sh.getRange(textResultRange);

  //Print column headers to the Results sheet
  ResultsRange.getCell(0, 0).setValue("Project/ART Name");
  ResultsRange.getCell(0, 1).setValue("PID");
  ResultsRange.getCell(0, 2).setValue("Production Event - Date");
  ResultsRange.getCell(0, 3).setValue("Production Event - Feature/Functionality");
  ResultsRange.getCell(0, 4).setValue("Production Event - ON/OFF/LIVE");
  ResultsRange.getCell(0, 5).setValue("Production Event - Notes/Impacted Apps");
  ResultsRange.getCell(0, 6).setValue("Production Event - Deployment Successfull?");
  ResultsRange.getCell(0, 7).setValue("Production Event - Detailed Status");
 
 //Loop through each row of Prod Event Data
  for (let i = 1; i < rangeValues.length; i++) {
    //Step through the row looking at each set of Prod Event dates
    for (let k = 3; k < rangeValues[i].length; k = k + 6) {
     // Get the date for this step and be sure it is not blank
      let Date = rangeValues[i][k];
      if (Date != "") {
        //Print the results to the results sheet

        //Print Project Name
        ResultsRange.getCell(ResultRowOffset, 0).setValue(rangeValues[i][0])
        //Print PID
        ResultsRange.getCell(ResultRowOffset, 1)setValue(rangeValues[i][1]);
        //Print Date
        ResultsRange.getCell(ResultRowOffset, 2).setValue(Date);
        // Print Feature
        ResultsRange.getCell(ResultRowOffset, 3).setValue(rangeValues[i][k + 1]);
        // Print ON/OFF/LIVE
        ResultsRange.getCell(ResultRowOffset, 4).setValue(rangeValues[i][k + 2]);
        // Print Notes/Impacted Apps
        ResultsRange.getCell(ResultRowOffset, 5).setValue(rangeValues[i][k + 3]);
        // Print Successful Deployment
        ResultsRange.getCell(ResultRowOffset, 6).setValue(rangeValues[i][k + 4]);
        // Print Detailed Status
        ResultsRange.getCell(ResultRowOffset, 7).setValue(rangeValues[i][k + 5]);
        // Only advanced the Results row counter if we printed something (the date wasn't blank)
        ResultRowOffset = ResultRowOffset + 1
      }
    }
  }
}

[twarner]

mjburley -

I took another look at my script and realize that YOU WERE RIGHT! I had a syntax error that I had introduced after my last successful run:

ResultsRange.getCell(ResultRowOffset, 1)setValue(rangeValues[i][1]);

As you can see, I missed the '.' before the setValue method.  Once I fixed that, the script runs via Power Automate.

Thank you for your help!

Tony