Trying to use Office Scripts and Flow to copy from one workbook to another

[victor_lion]

I'm using Office Scripts and it's been going well, until now that I have to copy some information from one workbook, to another workbook... I can copy from one to another normally, but can't record with office scripts, looked around for some codes that does something similar, but had no luck, maybe you can help me with some ideas.

[sfos83]

Hi Yutao,

Your solution looks pretty elegant! However upon the 'Import' run I get the following error:Line 4: Unexpected token u in JSON at position 0

For reference, I've got 2 workbooks:

Dashboard: A dashboard represents all the analytics from an email flow collected from Outlook 365/Power Automate. The flow of data goes into sheet 'Raw Data' and the dashboard is displayed on sheet 'Dashboard'.

in a variable row range A2:E(x)  A scheduled flow clears the 'Raw Data' at 6am ready for the next day of collecting emails from outlook. (and hence the Dashboard data disappears)

DailyLog:  the Dashboard data needs to be copied from today and be pasted (special values) under the last row from the previous day, and so on, in this workbook.

I'd like to do the equivalent online version of copying the range ("A2:E & lastrow") as you would in VBA before pasting it in the 'Daily Log' workbook, and if you could assist, you'd be a superhero!

Thanks

Steve

[Yutao]

@sfos83 

Hi Steve,

Would you mind sharing your script snippets and maybe the major parts of your flow?

Thanks,

Yutao

[MalcolmBean]

Hi I started using this solution for something and while it seems to be working great but it only returns the first line of my selected range. AI:G500 any help?

[tpr82]

Hi @Yutao,

Thank you for your scripts, it works perfectly on one site. I copied the flow for another SP-site, but it doesn't work here and i can't figure out why. I use your 2nd scripts (with dynamic range). I checked permission levels for sites + files, since i am the owner of both, and the Excel-files in use, and all seems fine. Flow works until the Import script (last in flow). So basically flows are the same, only differences are SP-sites and Excel workbooks; both exported ranges are dynamic workbooks and give output data. All worksheets are named Sheet1. I really hope you can help me, since I'm not that deep into JS-coding.

The Export script gives output data and Import script receives same input data (checked), but gives error:

"We were unable to run the script. Please try again.\nOffice JS error: Line 2: Worksheet getRange: The request failed with status code of 404, error code ResourceNotFound and the following error message: Invalid version: error\r\nclientRequestId: ..."

Using entire array; same as other flow / SP-site.

[tpr82]

Simple test flow. Tried 2 new workbooks. Both sheets named = "Sheet1".

1st script: clear range (OK) :

function main(workbook: ExcelScript.Workbook)
{
	let selectedSheet = workbook.getActiveWorksheet();
	selectedSheet.getRange("A2:Y20000").clear(ExcelScript.ClearApplyTo.contents);
}

2nd script: static export (OK) :

function main(workbook: ExcelScript.Workbook): string
{
    let sheet = workbook.getWorksheet("Sheet1");
    let range = sheet.getRange("A1:Y20000");
    return JSON.stringify(range.getValues());
}

3rd script: static import :

function main(workbook: ExcelScript.Workbook, values: string)
{
    let sheet = workbook.getWorksheet("Sheet1");
    let range = sheet.getRange("A1:Y20000");
    range.setValues(JSON.parse(values));
}

(Also tried import/export range: A2:Y20000 & workbook.getActiveSheet)

INPUTS shows data.

OUTPUTS Error: "\nOffice JS error: Line 4: Worksheet getRange: The request failed with status code of 404, error code ResourceNotFound and the following error message: Invalid version: error\r\nclientRequestId: ... "

[Yutao]

Hello @tpr82 ,

The "Invalid version" in the error message seems a bit strange. Would you mind sharing the clientRequestId from the failed "Run script" action? Feel free to send a private message to me if you feel more comfortable sharing over there.

[tpr82]

clientRequestId: 3337512f-3722-41d9-b247-3ea68e4c0c7f

[tpr82]

@Yutao 

As you mentioned earlier, the range could be too large and it was in my case (A1:Y20000), as i tested OK with a smaller range. So i split the import/export steps in half (A1:Y10000 & A10001:Y20000) in the flow. Now it works like a charm.

Thank you! Learned a lot.

[charly_sass]

Hey @Yutao,

this answer look like what i'm searching for since a few days, but i get one problem with this code.

After the Importer is finished i have all data in one cell (A2):

is there any fail in my action settings?

thanks a lot

[Yutao]

@charly_sass in the "values" box in the Run script action, you probably should get rid of those square brackets ("[", "]") but send only the "result" variable. Otherwise, all the data will be set into a single cell.

[David_MA]

I was helping one of my users last week with a similar issue. Each day he receives an export from FedEx of all shipments and wanted to create a spreadsheet with a running tally of all of the items. I could not come up with a solution in Power Automate, as the volume in the spreadsheet exceeds the Excel action threshold limits. However, I was able to come up with a Power Shell script that he can run each day to combine the daily export with the master list.

# Load the Excel COM object
$Excel = New-Object -ComObject Excel.Application

# Open the two Excel files
$Workbook1 = $Excel.Workbooks.Open("C:\path_to_file\file1.xlsx")
$Workbook2 = $Excel.Workbooks.Open("C:\path_to_file\file2.xlsx")

# Select the worksheets you want to combine
$Worksheet1 = $Workbook1.Worksheets.Item("Sheet1")
$Worksheet2 = $Workbook2.Worksheets.Item("Sheet1")

# Get the used range for each worksheet
$Range1 = $Worksheet1.UsedRange
$Range2 = $Worksheet2.UsedRange

# Determine the dimensions of the combined range
$CombinedRangeRows = $Range1.Rows.Count + $Range2.Rows.Count - 1
$CombinedRangeColumns = $Range1.Columns.Count

# Get the range for all rows except the first row in the first worksheet
$Range1WithoutHeader = $Worksheet1.Range("A2", $Worksheet1.Cells.Item($Range1.Rows.Count, $Range1.Columns.Count))

# Copy the data from the first worksheet (excluding the header row) to the second worksheet
$Range1WithoutHeader.Copy($Worksheet2.Cells.Item($Range2.Rows.Count + 1, 1))

# Resize the range to include all of the data
$CombinedRange = $Worksheet2.Range($Worksheet2.Cells.Item(1, 1), $Worksheet2.Cells.Item($CombinedRangeRows, $CombinedRangeColumns))

# Save the combined data to file2.xlsx
$Workbook2.Save()

# Close all open Excel files and quit Excel
$Workbook1.Close()
$Workbook2.Close()
$Excel.Quit()
Exit

Note: file1.xlsx is the daily file, and file2.xlsx is the combined file. In both spreadsheets, the worksheets are named Sheet1. If they are named differently, you will need to revise the above. If you put the above in Notepad and save it with a .ps1 extension, you can right-click on the file to execute it in Power Shell. I've tested it with 1,000 rows or so and it runs in a matter of seconds.

I have read there may be a way to run the Power Shell script through Power Automate Desktop, but I am not familiar with that.

[andrewmccourt64]

Hi @Yutao - would you split the imported ranges on the export script or the import script? I'm more or less doing exactly as you suggested, but am getting a Timeout error when trying to import the data.

Export Script:

function main(Workbook: ExcelScript.Workbook, sheetName: string) {
    let range = Workbook.getWorksheet(sheetName).getRange('A1');
    let surroundingRegion = range.getSurroundingRegion();
    let values = surroundingRegion.getValues();
    return values;
}

Import script:

function main(Workbook: ExcelScript.Workbook, sheetName: string, startCell:string, values: string) {
    Workbook.getWorksheet(sheetName).getRange().clear(ExcelScript.ClearApplyTo.contents);

    let range = Workbook.getWorksheet(sheetName).getRange(startCell);
    if (values.length === 0) {
        return;
    }

    let resizedRange = range.getAbsoluteResizedRange(values.length, values[0].length);
    resizedRange.setValues(values);

}

 Thanks,

Andy

[Yutao]

Hi @andrewmccourt64 - if the dataset you are working with is large, sometimes it's indeed possible to time out. In that case, you may need to split the dataset into smaller chunks.

[andrewmccourt64]

@Yutao  - about 4205 rows and 25 columns, so not very large at all.

How would you get the import script to split the imported dataset?

function main(Workbook: ExcelScript.Workbook, sheetName: string, startCell:string, values: string) {
    Workbook.getWorksheet(sheetName).getRange().clear(ExcelScript.ClearApplyTo.contents);

    let range = Workbook.getWorksheet(sheetName).getRange(startCell);
    if (values.length === 0) {
        return;
    }

    let resizedRange = range.getAbsoluteResizedRange(values.length, values[0].length);
    resizedRange.setValues(values);

}

Thanks,

Andy

[Yutao]

Ah I guess I found the problem! Can you double check your code for the data type of the input parameter `values`? In the code snippet you shared above, it is of type `string`. But I suppose it actually should be `string[][]`. Otherwise, `resizeRange.setValues(values)` will actually try to set the whole dataset into every single cell of the specified range!

Can you update your code to this and try again? Note the data type for `values` is now `string[][]` in the function parameter list. Let me know how it goes.

function main(Workbook: ExcelScript.Workbook, sheetName: string, startCell: string, values: string[][]) {
    Workbook.getWorksheet(sheetName).getRange().clear(ExcelScript.ClearApplyTo.contents);

    let range = Workbook.getWorksheet(sheetName).getRange(startCell);
    if (values.length === 0) {
        return;
    }

    let resizedRange = range.getAbsoluteResizedRange(values.length, values[0].length);
    resizedRange.setValues(values);

}

[andrewmccourt64]

@Yutao Ah sorry for the confusion, my actual code does have string[][] and is still returning the error - I came across this post and tried to switch setValues to JSON.stringify(values), but when it didn't work I switched it back to values. Missed adding the [][] back in.

This is the error I receive:

    "body": {

        "message": "We were unable to run the script. Please try again.\nOffice JS error: Line 25: Range setValues: Timeout\r\nclientRequestId: 85ebe946-af0d-4ccb-9450-cbaefa6fd44b",

        "logs": []

    }

[Yutao]

@andrewmccourt64 No worries 🙂 ! Do you mind sharing the clientRequestId from one of your timed out runs of the "Run script" action? We may need to identify where the timeout was exactly happening.

This screenshot shows how you can get the clientRequestId if you are not familiar with it.

[andrewmccourt64]

clientRequestId: 332ff205-beb5-4826-b8e5-411ce1941c73

Thanks,

Andy

[Yutao]

@andrewmccourt64  - thanks for sharing the client request ID. I was able to see the logs of that request can indeed see the timeout error for the single API call to setValues.

Here is one possible way to split the data into chunks when setting into the range:

function main(Workbook: ExcelScript.Workbook, sheetName: string, startCell: string, values: string[][]) {
  Workbook.getWorksheet(sheetName).getRange().clear(ExcelScript.ClearApplyTo.contents);

  if (values.length === 0) {
    return;
  }

  let range = Workbook
    .getWorksheet(sheetName)
    .getRange(startCell)
    .getAbsoluteResizedRange(values.length, values[0].length);

  const chunkSize = 1000;
  for (let i = 0; i < values.length; i += chunkSize) {
    const chunk = values.slice(i, i + chunkSize);
    const chunkRange = range.getOffsetRange(i, 0).getAbsoluteResizedRange(chunk.length, chunk[0].length);
    chunkRange.setValues(chunk);
    console.log(`Synching ${chunk.length} rows...`);
  }
}

You can tweak the `chunkSize` if needed. Also note please do not remove that `console.log` line because that's the way to force the script to synchronize with Excel periodically.

Let me know how it goes.

[PresidentEvil]

Hi @Yutao,

Thanks a lot for this! I was able to export the range however importer is not importing the range.

I have added an additional step (after import) in my flow to send email with the attachment of file where the range is exported. I can see the result of the export in my flow. But I get a blank excel attachment after the run is complete. When I went back to check the destination file where the data was supposed to be imported, as expected, the destination file was blank hence, I was receiving the blank excel attachment.

I don't know what the issue is. There is no error in the flow, or the script and they run fine, just that the 'import' is blank. Below is the import code & flow I'm using:

function main(Workbook: ExcelScript.Workbook, sheetName: string, address: string, values: string[][]) {
    let range = Workbook.getWorksheet(sheetName).getRange(address);
    if (values.length === 0) {
        return;
    }

    let resizedRange = range.getAbsoluteResizedRange(values.length, values[0].length);
    resizedRange.setValues(values);
}