Migrating data from one workbook to another

Question

Hi there,

I'm currently using Office Scripts to automate a task, and I've run into a challenge. I need to dynamically extract data from one worksheet in a different workbook and then use that data as a parameter for a script that's supposed to work in another workbook altogether.

I'm wondering if Excel Office Scripts allows for this type of cross-workbook functionality. If anyone has experience or insights on how to achieve this, I would greatly appreciate your guidance.

Thanks.

Answer 1

PowerShell itself doesn't have direct capabilities to interact with Office Scripts, as Office Scripts are primarily designed to run within the context of Excel on the web. However, you can use PowerShell to automate Excel files locally or manipulate Excel files in SharePoint or OneDrive using Microsoft Graph API, but bridging this with Office Scripts specifically (which run in Excel for the web) involves a few challenges and workarounds.

Here’s a high-level approach you might consider:

Use PowerShell to manipulate Excel files locally: If both workbooks are accessible on your local system or a network drive, you can use PowerShell with the ImportExcel module to read data from one workbook and write to another. This isn't using Office Scripts but rather direct manipulation using PowerShell.

Using Microsoft Graph API: If your workbooks are stored in OneDrive or SharePoint, you can use the Microsoft Graph API to interact with these files. You can run PowerShell scripts that make API calls to Microsoft Graph to fetch or modify Excel files. You could potentially set parameters or fetch data which then triggers an Office Script, though running the Office Script itself would need to be initiated differently as Graph API currently does not support triggering Office Scripts.

Office Scripts integration: For directly integrating with Office Scripts, you would generally need to use them within the context of Excel on the web. Office Scripts can be automated using Power Automate, which could potentially be triggered by a PowerShell script via HTTP requests to the Power Automate API.

Here is an example PowerShell script using ImportExcel to copy data from one workbook to another, which can serve as a starting point if you choose the local manipulation route:

Ensure you have the ImportExcel module installed

Install-Module -Name ImportExcel -Scope CurrentUser

$sourcePath = "path\to\source\workbook.xlsx"

$destinationPath = "path\to\destination\workbook.xlsx"

Read data from the source workbook

$data = Import-Excel -Path $sourcePath -WorksheetName "Sheet1"

Write data to the destination workbook

$data | Export-Excel -Path $destinationPath -WorksheetName "DataSheet" -Append