Excel scripts and data limit

[cecilia123]

Hello, 

I have a power automate flow which runs office scripts in excel. The first script copies and returns certain contents of the file and first occurring date in the file and dynamically allocates these returns to another script to either paste the data in the correct place chronologically using the first occurring date or it pastes the data at the end of the existing data. It works perfectly fine for files below 5MB but the issue I am facing is that the copy script will only run on smaller files because of the restrictions with excel scripts and the range functions. I have read that if I am able to split the data into chunks or there are some ways around this limitation but I can't find a clear guide how to do it or anywhere that confirms this is even possible. 

I thought about implementing a part into the copy function which first determines how big the file is (how many rows), then divides it by how many max rows we are allowed to deal with at a time and then loops the copying process for however many  chunks are needed and returns multiple copied chunks. Then in the paste function i wanted to loop the pasting process for however many chunks were returned and paste them in order. An issue I see with this solution is that currently in my power automate flow I have certain boxes where I dynamically allocate the copied data, and there is no way to determine how many chunks there will be, therefore I can't assign what the second script is pasting. 

Does anyone know if for my case it is possible to modify my script or PA flow so that it works for larger files or where I can find a guide on how to do so? Thanks in advance.

This is the copying script:

function main(workbook: ExcelScript.Workbook) {

  // Get the worksheet named "SourceSheet"

  let sourceSheet = workbook.getActiveWorksheet();

  // Get the used range in the source sheet starting from row 6

  let usedRange = sourceSheet.getRange("B6").getSurroundingRegion();

  // Get the values from the used range

  let values = usedRange.getValues();

  // remove the top rows from the array

  values = values.slice(1);

  // Convert the values array to a JSON string

  let jsonString = JSON.stringify(values);

  // Get the top date from B6

  let topDate = sourceSheet.getRange("B6").getValue();

  // Convert the top date to a formatted date string

  let firstDate = formatExcelDate(topDate);

  // Log the JSON strings to the console (for debugging purposes)

  console.log(jsonString);

  console.log(firstDate);

  // Return the JSON strings

  return { jsonString: jsonString, firstDate: firstDate };

}

// Function to format Excel date to yyyy/mm/dd

function formatExcelDate(excelDate: number😞 string {

  let date = new Date((excelDate - 25569) * 86400 * 1000); // Convert Excel date to JavaScript date

  let year = date.getFullYear();

  let month = ('0' + (date.getMonth() + 1)).slice(-2);

  let day = ('0' + date.getDate()).slice(-2);

  return `${year}/${month}/${day}`;

}

and this is the pasting script:

function main(workbook: ExcelScript.Workbook, jsonString: string, worksheetName: string, topDate: string) {

  // Parse the JSON string to get the array of values

  let values: (string | number | boolean)[][] = JSON.parse(jsonString);

  let firstDate: string | number | boolean = JSON.parse(topDate);

  // Get the worksheet with the name provided by the user

  let targetSheet = workbook.getWorksheet(worksheetName);

  // Find the first occurrence of the first date in column A

  let searchRange = targetSheet.getRange("A:A");

  let firstDateCell = searchRange.find(firstDate.toString(), {

    completeMatch: true,

    matchCase: false,

    searchDirection: ExcelScript.SearchDirection.forward // Start at the beginning of the range and go to later columns and rows.

  });

  if (firstDateCell) {

    // Get the used range in the target sheet

    let usedRange = targetSheet.getUsedRange();

    // Calculate the clear range starting from firstDateCell row down to the last used row

    let clearStartRow = firstDateCell.getRowIndex();

    let clearStartColumn = 0; // Column A (index 0)

    let clearEndRow = usedRange.getRowIndex() + usedRange.getRowCount() - 1;

    let clearEndColumn = usedRange.getColumnIndex() + usedRange.getColumnCount() - 1;

    // Get clear range

    let clearRange = targetSheet.getRangeByIndexes(clearStartRow, clearStartColumn, clearEndRow - clearStartRow + 1, clearEndColumn - clearStartColumn + 1);

    // Clear the range contents (values and formulas)

    clearRange.clear(ExcelScript.ClearApplyTo.contents);

    // Insert new values starting from the row of firstDateCell

    let startCell = targetSheet.getCell(clearStartRow, clearStartColumn);

    startCell.getResizedRange(values.length - 1, values[0].length - 1).setValues(values);

  } else {

    // Find the last used row in column B

    let lastUsedRange = searchRange.getUsedRange();

    let lastRow = lastUsedRange.getLastRow();

    // Calculate the start row for appending

    let startRow = lastRow.getRowIndex() + 1;

    // Get the starting cell in the target sheet for appending

    let targetRange = targetSheet.getCell(startRow, 0); // Column A (index 0)

    // Get the resized range based on the values array

    let resizedRange = targetRange.getResizedRange(values.length - 1, values[0].length - 1);

    // Set the values in the target sheet for appending

    resizedRange.setValues(values);

  }

}

[rzaneti]

Hi @cecilia123 ,

Ideally, the file size should not affect your Run Script action performance. There is a limitation of 5mb for requests/responses related to Office Scripts, but it is not directly impacted by the file size. However, a known limitation is the duration of your script run: it must be lower than 120 seconds.

Your idea to break the table records in chunks is very good, and it can work. In the past, had a similar problem and used a similar approach, where I've allocated the Run script into a Do until loop. In my Office Script, I returned to PA both the total quantity of rows and the number of the currently last accessed row, so I could stay in the loop until all rows were affected. This approach is good to handle spreadsheets with different "occupied cells" size. 

For your use case, this same approach seems to work. Let me know if the description above makes sense and, if you are confused on how to put it in practice, I can come back to you on Monday with a step-by-step on how to implement this Run Script/Do until integration.

Also, for any users starting in integrating Power Automate with Office Scripts that find this thread, I'm sharing some articles that I wrote about it:

- Office Scripts vs. VBA: http://digitalmill.net/2023/06/10/office-scripts-the-new-vba/ 

- Getting started with Office Scripts: http://digitalmill.net/2023/06/19/get-started-with-office-scripts/ 

- Variables in Office Scripts: http://digitalmill.net/2024/06/26/variables-in-office-scripts/ 

- Accessing Excel ranges with Office Scripts: http://digitalmill.net/2023/09/01/accessing-excel-ranges-with-power-automate/ 

- Sending values from PA to Excel with Office Scripts: http://digitalmill.net/2024/01/17/sending-values-from-power-automate-to-excel-with-office-scripts/ 

Let me know if it works for you or if you need any additional help!

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

http://digitalmill.net/ 
https://www.linkedin.com/in/raphael-haus-zaneti/ 

[cecilia123]

Hi @rzaneti , 

Thanks so much for the reply! Just to clarify this is how I will adjust my scripts and flow: 

1. I will declare a variable called "rows pasted" at the beginning of the flow.

2. I will add another short office script that can return the total rows there are in the file I am copying from and run it in PA. 

3. I will add a do until function for when the "rows pasted" is equal to or greater than the "total rows". Inside the do until function:

3a. I will run my copy script which now copies 10 000 rows at a time to make sure it doesn't take more than 120s. 

3b. I will paste the rows copied by the copy script after each other into the new file. If the "rows pasted" is at 0, I will make sure to check for when the first date occurs so I can paste the first chunk into the correct place. 

3c. I will increment the "rows pasted" 10000 rows so I know where to copy and paste the next chunk and the do until loop will function properly. 

I've adjusted my scripts and am trying to run variations of what I just laid out but I am still running into issues. It might take a lot of testing to get right but do you see any issues with the above logic? Also, each test runs for about 10 minutes before it times out and fails, do you know how to make the tests time out sooner if they are failing so that the testing takes less time?

Thanks again!

[rzaneti]

Hi @cecilia123 ,

I don't see any apparent error in the script description. You can change the retry policy by clicking in settings:

And then changing this dropdown to 'None':

This change must limit your scripts to run only once, and then fail (if it times out).

Let me know if it works!

[cecilia123]

Hi @rzaneti, 

I got the flow to run as described in my previous post! Thanks so much for your help.