SharePoint Copying Macro Enabled Excel File Using JavaScript

Question

I have an Excel macro enabled file store in my SharePoint document library. Using JavaScript, I'm trying to essentially copy the file and create a duplicate of it with a different name for users to edit. The code I have currently is below.

 function copyAndRenameTemplate(fileName, templateFilePath, destinationFolderPath) {
        getFormDigestValue().then(function(formDigestValue) {
            // Get template file's unique id
            $.ajax({
                url: "https://mysiteurl/sites/subsite/_api/web/getfilebyserverrelativeurl('" + templateFilePath + "')",
                type: "GET",
                headers: {
                    "Accept": "application/json;odata=verbose",
                    "X-RequestDigest": formDigestValue
                },
                success: function(data) {
                    var templateFileUniqueId = data.d.UniqueId;
    
                    // Copy template file to destination folder
                    $.ajax({
                        url: "https://mysiteurl.ca/sites/subsite/_api/web/getfolderbyserverrelativeurl('" + destinationFolderPath + "')/Files/add(url='" + fileName + ".xlsm', overwrite=true)",
                        type: "POST",
                        headers: {
                            "Accept": "application/json;odata=verbose",
                            "X-RequestDigest": formDigestValue,
                            "X-HTTP-Method": "POST",
                            "If-Match": "*",
                            "Content-Type": "application/json;odata=verbose"
                        },
                        data: JSON.stringify({
                            'type': 'SP.File',
                            'sourceUniqueId': templateFileUniqueId
                        }),
                        success: function(data) {
                            console.log("File copied and renamed successfully.");
                        },
                        error: function(error) {
                            console.log("Error copying and renaming file: " + JSON.stringify(error));
                        }
                    });
                },
                error: function(error) {
                    console.log("Error retrieving template file: " + JSON.stringify(error));
                }
            });
        }).catch(function(error) {
            console.error('Error retrieving form digest: ', error);
            alert('Failed to retrieve form digest value: ' + JSON.stringify(error));
        });
    }

The code properly copies and renames the file at the correct location, but when I try to open it, I get a message "We found a problem with some content with filename.xlsm. Do you want us to try to recover as much as we can? If you trust the source of this workbook, click Yes". Clicking yes does nothing besides closing the message. I've tried manually renaming the file extension to see if that helps but it still throws the same message. I'm assuming some part of my code is wrong and is causing the file to corrupt but can't figure it out. Is there something I'm doing wrong or is there a different way to accomplish this altogether?

Answer 1

After some further research and tinkering I found the following solution to be suitable for my needs.

function copyAndRenameTemplate(fileName, templateFilePath, destinationFolderPath) {
        getFormDigestValue().then(function(formDigestValue) {
            // Get the file content as ArrayBuffer
            $.ajax({
                url: "https://mysiteurl.ca/sites/subsite/_api/web/getfilebyserverrelativeurl('" + templateFilePath + "')/$value",
                type: "GET",
                headers: {
                    "Accept": "application/json;odata=verbose",
                    "X-RequestDigest": formDigestValue
                },
                xhrFields: {
                    responseType: 'arraybuffer' // Ensures the response is an ArrayBuffer
                },
                success: function(arrayBuffer) {
                    // Upload the file as an ArrayBuffer to the destination folder
                    var uploadUrl = "https://mysiteurl.ca/sites/subsite/_api/web/getfolderbyserverrelativeurl('" + destinationFolderPath + "')/Files/add(url='" + fileName + ".xlsm', overwrite=true)";
                    
                    $.ajax({
                        url: uploadUrl,
                        type: "POST",
                        data: arrayBuffer,
                        processData: false,
                        contentType: "application/vnd.ms-excel.sheet.macroEnabled.12",
                        headers: {
                            "Accept": "application/json;odata=verbose",
                            "X-RequestDigest": formDigestValue
                        },
                        success: function(data) {
                            console.log("File copied and renamed successfully.");
                        },
                        error: function(error) {
                            console.log("Error copying and renaming file: " + JSON.stringify(error));
                        }
                    });
                },
                error: function(error) {
                    console.log("Error retrieving template file: " + JSON.stringify(error));
                }
            });
        }).catch(function(error) {
            console.error('Error retrieving form digest: ', error);
            alert('Failed to retrieve form digest value: ' + JSON.stringify(error));
        });
    }

The key difference was to read the file as an ArrayBuffer and force the content type of the POST request to match that of a macro enabled Excel file.

I'm still open to other better solutions, as this one works in most cases. Certain file names sill cause the problem (with no discernable pattern) but this seems to be uncommon enough to still suit my needs.