To export data from the current sheet’s range A1:E100 to a new Excel workbook, you can use the following OfficeScript:
function exportToExcel() { // Get the active worksheet var worksheet = context.workbook.getActiveWorksheet(); // Define the range to export var range = worksheet.getRange("A1:E100"); range.load("values"); return context.sync() .then(function() { // Create a new workbook var newWorkbook = context.workbook.application.createWorkbook(); newWorkbook.load("worksheets"); return context.sync() .then(function() { // Add a new worksheet to the new workbook var newWorksheet = newWorkbook.worksheets.add(); newWorksheet.name = "ExportedData"; newWorksheet.activate(); // Get the range in the new worksheet var newRange = newWorksheet.getRange("A1:E100"); // Copy the values from the original range to the new range newRange.values = range.values; // Save and close the new workbook return newWorkbook.saveAsAsync() .then(function() { newWorkbook.close(); }); }); }) .then(context.sync); }
In this example, the exportToExcel function performs the following steps:
It gets the active worksheet using the
context.workbook.getActiveWorksheet()method.It defines the range to export, in this case, A1:E100, using the
getRange()method.It loads the values of the range using the
load("values")method.It synchronizes the context to ensure the range values are retrieved.
It creates a new workbook using
context.workbook.application.createWorkbook().It loads the worksheets of the new workbook using
load("worksheets").It synchronizes the context to ensure the new workbook and its worksheets are retrieved.
It adds a new worksheet to the new workbook using
newWorkbook.worksheets.add().It sets the name of the new worksheet to “ExportedData” using
newWorksheet.name.It activates the new worksheet using
newWorksheet.activate().It gets the range in the new worksheet using
newWorksheet.getRange("A1:E100").It assigns the values from the original range to the new range using
newRange.values = range.values.It saves the new workbook using
newWorkbook.saveAsAsync().It closes the new workbook using
newWorkbook.close().
You can call the exportToExcel function to export the data from the current sheet’s range A1:E100 to a new Excel workbook. The new workbook will contain a worksheet named “ExportedData” with the exported data.
Please note that this example assumes you are running the OfficeScript in Excel and have the necessary permissions to create and save workbooks. Adjust the range and other parameters as needed for your specific use case.
Additionally, remember to set up the necessary context and load the OfficeScript runtime to ensure the script executes successfully.