You’re offline. This is a read only version of the page.
Hello,
As the title above suggests, I am trying to make a flow that will copy data from one excel document to another. All the resources I look at on this subject seem to be outdated.
I don't really mind at this stage if it's a simple "lift and shift" my only requirement would be that I want the data to be overwritten each time the flow is run (looking at scheduling it to recur once per week)
If anyone could help me out with this, I'd be grateful.
Cheers,
Share
Report
All responses (
Answers (
/**
* This script returns the values from the used ranges on each worksheet.
*/
function main(workbook: ExcelScript.Workbook): WorksheetData[] {
// Create an object to return the data from each worksheet.
let worksheetInformation: WorksheetData[] = [];
// Get the data from every worksheet, one at a time.
workbook.getWorksheets().forEach((sheet) => {
let values = sheet.getUsedRange()?.getValues();
worksheetInformation.push({
name: sheet.getName(),
data: values as string[][]
});
});
return worksheetInformation;
}
// An interface to pass the worksheet name and cell values through a flow.
interface WorksheetData {
name: string;
data: string[][];
}/**
* This script
locates the specified tab and clears the data on it.
*/
function main(workbook: ExcelScript.Workbook,
SheetName: string = "Sheet1"
) {
//Check if the worksheet exists.
let dataWorksheet = workbook.getWorksheet(SheetName);
if (dataWorksheet) {
//delete the worksheet.
dataWorksheet.getRange().clear(ExcelScript.ClearApplyTo.contents);
//switch to the worksheet
workbook.getWorksheet(SheetName).activate();
}
else {
console.log(`No worksheet with the specified name in this workbook.`);
};
}/**
* This script adds data to a specified tab with data provided by the Return Worksheet Data script
*/
function main(workbook: ExcelScript.Workbook,
worksheetName: string,
worksheetInformation: WorksheetData[]
) {
// Add each new worksheet.
worksheetInformation.forEach((value) => {
let sheet = workbook.getWorksheet(worksheetName);
// If there was any data in the worksheet, add it to a new range.
if (value.data) {
let range = sheet.getRangeByIndexes(0, 0, value.data.length, value.data[0].length);
range.setValues(value.data);
}
});
}
// An interface to pass the worksheet name and cell values through a flow.
interface WorksheetData {
name: string;
data: string[][];
}Nowadays you can use Excel connectors to manipulate any data in a workbook in whatever way you want using Office Scripts, which is scripting in Excel integrated with Power Automate. Using this you can export data from one workbook and then write it to another. This video has a good walkthrough of what to do. (2) Move data between workbooks automatically with Office Scripts & Power Automate | Excel Off The Grid - YouTube
If that data is in a table and is being copied to a table in the second document, then yes, a flow can copy data from one Excel document to another. The primary limitation of the Excel connectors for Power Automate is that they can only work with data in defined tables in Excel. As far as overwriting the data in the destination, there isn't a quick or easy way to do that. If you intend to write to an existing file, you would first have to manually delete the rows in the table on that document before adding the new rows.
Alternately, if you don't want to clear out the existing rows from the destination, you can create worksheets and tables in a document, so you could create a blank Excel doc, add a workbook to it, add a table to the workbook, then write the rows into it. There's a good blog post here showing how to do that create process.
#1
WarrenBelz
146,645
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,997
Most Valuable Professional
