Running MS Office Script on SharePoint spreadsheet leaving the file locked?

[younceb]

Running MS Office Script on SharePoint spreadsheet leaving the file locked?

I have a scenario like this:

1. File is dropped in SP folder - triggers the flow with new file

2. File properties are grabbed to run MS Office Script on the data

3. If the script returns data in bad state, then move the file to a new SP folder --> This part is failing with file is in locked mode.

4. Otherwise create a list item with the read details from spreadsheet

I've tripled checked to make sure the file is not open in any browser windows. I am using the file currently for testing, so it's very unlikely someone else has the file open at all.

I've added a Do Until to wait 5 minutes for the file to unlock, but either that hasn't been long enough for the file to unlock or the file will not unlock.

My question is what is causing the file to lock in the first place? Could it be the run of the MS Office Script on the file?

I am able to open and edit the file as Expected after the run totally fails, so really drawing a blank with this.

This is the script - any advice for improvement is appreciated. 

function main(workbook: ExcelScript.Workbook) : string {


let returnMessage = ""
  //Static name of the worksheet containing the concerned rows
let sheetRSS = workbook.getWorksheet("Sheet Name");

  //grab the table object, only one in the RSS template on this worksheet
let table = sheetRSS.getTables()[0];

 //get the used range
let tableRange = table.getRange()

//Reason for Request in column 2, 0 indexed array
let complexityColumn = tableRange.getColumn(5);

//Variable to inspect the contents of cell in loop
let currentCellValue = "";

//Number of cells in the Reason for Request column
let complexityColCellCount = complexityColumn.getCellCount();

//Tally the number of Create Package rows
let numberOfCreatePackageCells = 0


  //These values are associated with columns
  let currentProjectID_Cell = sheetRSS.getCell(4,2).getText().trim();
  //console.log("Project id: " + currentProjectID_Cell)

  let currentSite_Cell = sheetRSS.getCell(4, 5).getText().trim();
  //console.log("Site: " + currentSite_Cell)

  let currentDivision_Cell = sheetRSS.getCell(5, 5).getText().trim();
  //console.log("Division: " + currentDivision_Cell)

  let currentDepartment_Cell = sheetRSS.getCell(6, 5).getText().trim();
  //console.log("Dept: " + currentDepartment_Cell)

  let currentArea_Cell = sheetRSS.getCell(7, 5).getText().trim();;
  //console.log("Area: " + currentArea_Cell)

  //If any of these are empty, fail fast 
  if (currentArea_Cell === "" || currentProjectID_Cell === ""){
      returnMessage += "Error - One of the below required fields is empty in sheet: " + workbook.getName() +". /n Project ID: " + currentProjectID_Cell + "\nSite: " + currentSite_Cell + "\nDivision: " + currentDivision_Cell + "\nDepartment: " + currentDepartment_Cell + "\nArea: " + currentArea_Cell
      console.log(returnMessage)
      return returnMessage;
  }

  //Basically we have an array that will hold our rows
  //which is also an array - of objects
  let rows : Array<Array<Object>> = [];

  //Row object with needed values
  let row : Array<Object> = [];
  

//Loop through every cell in the Complexity column
for (let i = 0; i < complexityColCellCount; i++) {

      //Read the value of the cell in this spot based on loop counter
      currentCellValue = complexityColumn.getCell(i,0).getText();

      //found one?? Increment the counter and push the row values
      if (currentCellValue.includes("Create Package")){
        numberOfCreatePackageCells++;

        row = [{
          name: "DatabaseID",
          value: tableRange.getCell(i, 0).getText().trim(),
          required: true
        },
        {
          name: "Priority",
          value: tableRange.getCell(i, 3).getText().trim(),
          required: true
        },
        {
          name: "ReasonForRequest",
          value: tableRange.getCell(i, 2).getText().trim(),
          required: true
        },
        ...
        ]
        rows.push(row)
      }
    }


  //If we never found a Create Package row, then we have hit an error scenario
  //No work to do
  if(numberOfCreatePackageCells = 0){

    returnMessage = "Error - No Create Package Rows found in file."
    console.log(returnMessage);
    return returnMessage;
    
  }

  //Check each row has the value populated for fields marked required as true
  for(let b = 0; b < rows.length; b++){
    for (let c = 0; c < rows[b].length; c++){
      let currentArray = rows[b];
      let currentObj : Object = currentArray[c];
      if(currentObj["required"] === true)
        if (currentObj["value"] === ""){
          returnMessage += "\n" + "Error - " + currentObj["name"] + " was found to be empty when required is marked as: " + currentObj["required"]+ " in RSS Spreadsheet: " + workbook.getName();  
        }
    }
  }

  if (!returnMessage.includes("Error")){
    returnMessage = JSON.stringify(rows);
  }
  console.log(returnMessage);
  return returnMessage;
}

[younceb · Accepted solution]

Although I am not totally sure the cause of the lock - it seems like it's the Office Script call.

I was able to get the flow to progress by waiting 10 minutes or so, up from the 5 minutes I was waiting before.