cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
twarner
New Member

Run Office Script error

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

 

twarner_0-1648168160561.png

Is there something I'm missing?

 

Thanks,

Tony

1 ACCEPTED SOLUTION

Accepted Solutions
mjburley
Resolver II
Resolver II

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

View solution in original post

3 REPLIES 3
mjburley
Resolver II
Resolver II

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

twarner
New Member

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
      }
    }
  }
}

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

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Users online (4,624)