I want to use office script to copy data from one workbook to other

Question

My problem statement is a little complicated, so please bear with me.

I am going to explain the process as much as I can -

• Step 1: Let's say every week, I receive a client sheet called 'Main sheet' that has certain data against each client.
  
• Step 2: I have to now copy each client's data and paste it into a separate sheet for the specific client. E.g. - I copy data for client ABC from the main sheet, open a separate Excel for client ABC called 'client abc' and paste it against the current date, which is already populated on the client sheet.
  

Basically, I will have a main sheet with client information of +20 clients, and this needs to be pasted into a respective individual client Excel/workbook.

I want to automate this using Office script and PowerAutomate. I am good with PowerAutomate but have zilch knowledge of Office script and how to make one.

I want to create a script to copy the data from the client one main sheet to other respective client sheets. Can someone please assist me with this?

I have attached 2 SS files for better understanding and am happy to provide more information if needed.

Answer 1

I get what you are saying, but honestly you start going down the scripting road, you may never come back. But thats your call. Im fighting a battle with PowerShell, and MS is makign life hellish.

Once thing I do know about Excel though, is you can have a new sheet open and pull data from the unopened spreadsheet .

You can pull data from an unopened Excel sheet into a new Excel sheet by using the Power Query feature. Go to the "Data" tab, select "Get Data," then choose "From File" and "From Excel Workbook," and locate the closed workbook to import the data.

I dont know if that will help, but might be a direction that works for you. Good Luck.

Answer 2

Hi @Oshin Jawharkar

Thanks for sharing the details of your scenario. However, I wasn’t able to find any files attached to your post. Could you please double-check the upload?

Also, I’ve sent you a direct message with more information in case you’d prefer to share the files there. Looking forward to helping you further once we have access to the sample workbooks!

Answer 3

I don't know script but this is easy enough to do with a macro:

Loop through each row of interest in main
     Choose worksheet for that customer
     Find customer row containing the date
     Loop through main columns of interest
          Assign data from this cell in main to the appropriate cell in customer worksheet
     End loop
End loop

Answer 4

Hi @Oshin Jawharkar

Thank you for reaching out via the Microsoft Q&A forum. 

Just a quick note before we begin: I'm not a professional in this field, but I’ve done some research to come up with a potential solution that might help streamline your workflow. Below is a summary of the automation process we discussed, which transfers data from a main Excel sheet to individual client files using Office Script and Power Automate. 

The goal here is to make your weekly data tasks a lot easier and less manual. Every week, you upload a main Excel sheet to SharePoint that contains financial data for multiple clients. Instead of copying and pasting rows for each client by hand, we’re setting up a system that can: 

• Pull the right data for each client based on their name and pay date, 
• Grab the key financial figures from the main sheet, 
• And automatically drop that data into the correct client file stored on SharePoint. 

This way, you save time, reduce errors, and keep everything organized without having to do it all manually. 

This script looks through the main sheet and finds the row that matches a specific client name and pay date. Once it finds the right row, it pulls out the financial data you need like liabilities, FIT, SOCEE, MEDEE, and so on; and sends that data back to Power Automate so it can be used in the next step. 

You’ll want to add this script to your main Excel file and name it ExtractClientData. 

function main(workbook: ExcelScript.Workbook, clientName: string, payDate: string) {
  const sheet = workbook.getWorksheet("Main");
  const data = sheet.getUsedRange().getValues();
  const header = data[0];
  const rows = data.slice(1);
  const match = rows.find(row =>
    row[1]?.toString().trim() === clientName &&
    new Date(row[2]).toDateString() === new Date(payDate).toDateString()
  );
  if (!match) {
    console.log("No matching row found.");
    return;
  }
  const valuesToWrite = match.slice(3, 9); // Columns D to I
  return {
    clientName,
    payDate,
    values: valuesToWrite
  };
}
 

 This second script handles the writing part. It opens the client’s Excel file (specifically the sheet named "dep 2025"), checks if there’s already a row with the same pay date, and then: 

• If it finds a match, it updates that row with the new data. 
• If not, it adds a brand-new row at the bottom with the pay date and financial values. 

You can save this script in any Excel file or keep it separate, just make sure it’s named WriteToClientSheet. 

function main(workbook: ExcelScript.Workbook, payDate: string, values: string[]) {
  const sheet = workbook.getWorksheet("dep 2025");
  const usedRange = sheet.getUsedRange();
  const data = usedRange.getValues();
  const payDateCol = 8; // Column I
  const startRow = 1;
  let targetRow = -1;
  for (let i = startRow; i < data.length; i++) {
    const cellDate = data[i][payDateCol];
    if (cellDate && new Date(cellDate).toDateString() === new Date(payDate).toDateString()) {
      targetRow = i;
      break;
    }
  }
  if (targetRow !== -1) {
    for (let j = 0; j < values.length; j++) {
      sheet.getCell(targetRow, j + 1).setValue(values[j]); // Start from column B
    }
  } else {
    const newRow = sheet.getRange(`A${data.length + 1}:L${data.length + 1}`);
    const newData = ["", ...values];
    newData[payDateCol] = payDate;
    newRow.setValues([newData]);
  }
}
 

Here’s how the automation flow works: 

1. Trigger: It starts when a new Excel file is uploaded to a specific SharePoint folder (like MainSheets/). 
2. Power Automate opens the new file and loops through each client. 
3. It runs ExtractClientData to get the relevant financial info. 
4. Then it opens the client’s individual Excel file (e.g., ABC.xlsx). 
5. Finally, it runs WriteToClientSheet to update or add the data. 

This setup is meant to take the repetitive manual work off your plate and keep your client files consistently up to date. I believe you’ll grasp the overall flow without much trouble, especially with your experience in Power Automate. 

Please note that: 

• This solution is still in the theoretical stage, as we haven’t had the chance to test it in a live Power Automate environment yet due to the lack of a testing setup.
• Some columns, especially date fields, might still need to be entered manually depending on how your data is structured. 

Please give it a try in your setup and let me know how it goes. 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".  

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.