Here’s an example of an OfficeScript that copies data from one sheet and pastes it into a new row in another sheet, dynamically finding the new row using the last used range:
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(); // Find the last used row in the target sheet let targetLastRow = targetSheet.getRange("A:A").getSpecialCells(ExcelScript.SpecialCellType.LastCell).getRow(); // Compute the new row for pasting let newPasteRow = targetLastRow + 1; // Copy the values from the source range let valuesToCopy = sourceRange.getValues(); // Paste the values into the new row in the target sheet let targetRange = targetSheet.getRangeByIndexes(newPasteRow, 1, valuesToCopy.length, valuesToCopy[0].length); targetRange.setValues(valuesToCopy); }
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. Then, it finds the last used row in the target sheet by searching for the last cell in column A. The new row for pasting is computed as the last used row + 1. Finally, the values are pasted into the new row in the target sheet using the setValues function.
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.