Here’s an example of an OfficeScript that copies data from one sheet to another in the same workbook:
function main(workbook: ExcelScript.Workbook, sourceSheetName: string, targetSheetName: string) { // Get the source and target sheets let sourceSheet = workbook.getWorksheet(sourceSheetName); let targetSheet = workbook.getWorksheet(targetSheetName); // Get the used range of the source sheet let sourceRange = sourceSheet.getUsedRange(); // Copy the values from the source range to the target sheet let targetRange = targetSheet.getRange(sourceRange.getAddress()); targetRange.setValues(sourceRange.getValues()); }
To use this OfficeScript, follow these steps:
- Open Excel Online or Excel for the web.
- Open an existing workbook that contains the source and target sheets.
- Note down the names of the source sheet and the target sheet.
- Click on the “Automate” tab in the ribbon.
- Click on the “Script Lab” button.
- In the Script Lab pane, create a new script or open an existing one.
- Replace the default code in the script editor with the code provided above.
- In the
mainfunction call, provide the names of the source sheet and target sheet as arguments. - Click the “Run” button to execute the script.
This script copies the data from the used range of the source sheet to the corresponding range in the target sheet. The getUsedRange function retrieves the range containing the data in the source sheet, and the getAddress function gets the address of the range. Then, the values are copied from the source range to the target range using the getValues and setValues functions.
Please note that OfficeScripts are currently only supported in Excel for the web and Excel Online. They are not available in the desktop version of Excel.