I want to copy an excel online data range and paste it to another workbook

Atanu Sarkar 0 Reputation points
2024-03-16T08:14:51.1666667+00:00

Hi All,

I want to use Office script and perform below action if possible please?

I have a master excel file on SharePoint contains 7 sheets. Each sheet are for weekly schedule like Saturday, Sunday and Monday and so on.

I want to copy Master excel.sheets("user defined").range("A3:T41") data and paste it to(cell A3 )another Excel file(TempFile) located in the same SharePoint. E.g TempFile.sheet1.range("A3).

There should be a button on each master excel sheets. Once users pressed button, it shou copy that sheet range data and paste it to TempFile( range and destination cell describe above)

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,507 questions
0 comments

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Shawn Collins 505 Reputation points
    2024-04-14T13:16:29+00:00

    To achieve the desired functionality using Office Scripts in Excel on the web, you'll follow these steps:

    1. Create an Office Script to copy data from the specified range in the master file and paste it into the TempFile.
    2. Set up a button on each sheet in the master file that, when clicked, triggers this script.
    3. Use Power Automate to handle the integration with SharePoint and automate the script execution.

    Here's a step-by-step breakdown:

    Step 1: Write the Office Script

    You need to write a script that:

    • Opens the TempFile.
    • Copies data from the active sheet of the master file.
    • Pastes it into the specified range in the TempFile.

    Here’s a basic script to start with. This script assumes that both the master file and the TempFile are accessible and that you have appropriate permissions to modify them.To achieve the desired functionality using Office Scripts in Excel on the web, you'll follow these steps:

    1. Create an Office Script to copy data from the specified range in the master file and paste it into the TempFile.
    2. Set up a button on each sheet in the master file that, when clicked, triggers this script.
    3. Use Power Automate to handle the integration with SharePoint and automate the script execution.

    Here’s a basic script to start with. This script assumes that both the master file and the TempFile are accessible and that you have appropriate permissions to modify them.

    `async function main(workbook: ExcelScript.Workbook): Promise<void> { // Load the current worksheet and get the range to be copied let currentSheet = workbook.getActiveWorksheet(); let rangeToCopy = currentSheet.getRange("A3:T41");

    // Load the data in that range rangeToCopy.load("values"); await workbook.context.sync();

    // Now, open the TempFile by using its URL in SharePoint // Note: This requires the workbook URL and might need integration with Power Automate to fully implement let tempFileUrl = "https://yourSharePointSiteURL/sites/YourSite/Documents/TempFile.xlsx"; let tempWorkbook = await ExcelScript.openWorkbookByUrl(tempFileUrl);

    // Get the first worksheet in the TempFile let tempSheet = tempWorkbook.getWorksheets()[0];

    // Get the target range and set the values copied earlier let targetRange = tempSheet.getRange("A3"); targetRange = targetRange.getResizedRange(rangeToCopy.getRowCount() - 1, rangeToCopy.getColumnCount() - 1); targetRange.setValues(rangeToCopy.getValues());

    // Save changes and close the workbook await tempWorkbook.context.sync(); await tempWorkbook.closeWorkbook(); } ` Step 2: Link the Script to a Button in Each Sheet Add a button from the "Insert" menu in Excel for the web. Assign the script to each button on each sheet so that when clicked, it runs the script. Step 3: Consider Automating with Power Automate If you need this process to be part of a larger workflow or if you need more complex logic (like error handling, notifications, etc.), consider using Power Automate:

    Create a flow that triggers on a button click or on a schedule. Use the "Run script" action in Power Automate to execute the Office Script.

    Notes Permissions: Make sure that the script has permissions to access both the master file and the TempFile on SharePoint. Workbook URLs: You will need to know the exact URLs for the Excel files stored in SharePoint.

    0 comments