My Office Script code is taking 1 minute in Excel, but when I run it in Power Automate it takes more than 15 minutes and fails (error 504).

Question

function main(workbook: ExcelScript.Workbook) {  
  let w2 = workbook.getWorksheet('Admissão');  
  
  //Pega a data de hoje  
  let today = new Date().toLocaleDateString();  
  
  //guarda ultima celula da planilha principal  
  let ultimacelad = w2.getCell(w2.getRange().getRowCount() - 1, 0).getRangeEdge(ExcelScript.KeyboardDirection.up).getRowIndex();  
  
  //Loop para leitura de todas as planilhas  
  for (let i = 1; i <= 9; i++) {  
    //sinaliza script rodando  
    let selectedSheet = workbook.getWorksheet(`R-${i}`);  
    //limpar filtros plan  
    selectedSheet.getAutoFilter().clearCriteria();  
  
    //linhas e colunas para a planilha   
    let lns: number = 1;  
    let cols: number = 0;  
    let lnw2: number = 0;  
    let colw2: number = 0;  
  
    //Ultima célula das planilhas  
    let ultimacel = selectedSheet.getCell(selectedSheet.getRange().getRowCount() - 1, 1).getRangeEdge(ExcelScript.KeyboardDirection.up).getRowIndex();  
  
    //Inserir dados na planilha principal  
    while (lns <= ultimacel) {  
  
      if (selectedSheet.getCell(lns, cols + 1).getValue() == "Encaminhar") {  
  
        //COPIA DOS DADOS PARA A PLANILHA PRINCIPAL  
        w2.getCell(ultimacelad + 1, colw2).copyFrom(selectedSheet.getCell(lns, cols + 2).getResizedRange(0, cols + 20), ExcelScript.RangeCopyType.values, false, false);  
        w2.getCell(ultimacelad + 1, colw2).setValue(today);  
  
        selectedSheet.getCell(lns, cols + 1).getResizedRange(0, cols + 19);  
        selectedSheet.getCell(lns, cols + 1).setValue("Encaminhado");  
        selectedSheet.getCell(lns, cols + 2).setValue(today);  
        ultimacelad++;  
      }  
      lns++;  
    }  
  }  
}  

Answer 1

Can you please describe your scenario more? What is the script supposed to do?

Answer 2

Your performance issues are likely related to a few things: Your nested loops (the for and while loops). Your while loop could potentially run a long time depending on the value of ultimacel. And your use of your getCell calls within the while loop could be expensive. So all of those things could be slowing you down in PowerAutomate.

To increase speed, I would create an array of values for each column you're using getCell with. Once you have the array of values, you can loop through those. Looping through the arrays should be much faster and you should get much better performance.