Here’s an example of an OfficeScript that exports data from the current sheet’s range A1:E100 to a CSV file:
function exportToCSV() { // 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() { // Convert the data to CSV format var csvContent = "data:text/csv;charset=utf-8,"; range.values.forEach(function(row, rowIndex) { var csvRow = row.join(","); csvContent += rowIndex === 0 ? csvRow : "\n" + csvRow; }); // Create a download link for the CSV file var downloadLink = document.createElement("a"); downloadLink.setAttribute("href", encodeURI(csvContent)); downloadLink.setAttribute("download", "data.csv"); document.body.appendChild(downloadLink); // Click the download link to initiate the CSV download downloadLink.click(); // Clean up the download link document.body.removeChild(downloadLink); }) .then(context.sync); }
In this example, the exportToCSV 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 converts the data to CSV format by iterating over each row and joining the values with commas. The resulting CSV content is stored in the
csvContentvariable.It creates a download link element using
document.createElement("a").It sets the
hrefattribute of the download link to the generated CSV content.It sets the
downloadattribute of the download link to specify the filename as “data.csv”.It appends the download link to the document body.
It triggers a click event on the download link to initiate the CSV download.
It removes the download link from the document body to clean up.
You can call the exportToCSV function to export the data from the current sheet’s range A1:E100 to a CSV file. The CSV file will be downloaded automatically with the name “data.csv”.
Please note that this example assumes you are running the OfficeScript in a web environment where you can trigger the CSV download. Adjust the range, file name, 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.