How to save settings for worksheets between opening a closing workbook (Office.js API for Excel)?

Question

According to my question, I found that the best way to programmatically attach settings for a worksheet is to use the worksheet ID, because the worksheet name can be changed by the user.

Office.context.document.settings.set("{040E0F18-0F61-4CD9-886D-95112C925793}", JSON.stringify(myValues));

But after some research, I found that worksheet identifiers always change after reopening the workbook, and according to the documentation this is not an error, it is a feature.

"{040E0F18-0F61-4CD9-886D-95112C925793}" after reopening ( question about what )

Is there a way to save the settings for worksheets between opening the closing book?

Answer 1

I can think of one workaround, although I admit that this is not the most elegant thing in the world.

For any worksheet for which you want to save the settings, first create a binding for "WorksheetName! A1: XFD1048576". You will get the identifier back, and this time it is unique and stored in the document

Office.context.document.bindings.addFromNamedItemAsync("WorksheetName!A1:XFD1048576", "matrix", function (result) {
    if (result.status == 'succeeded'){
        console.log('Added new binding with ID: ' + result.value.id);
    }
    else {
        console.log('Error: ' + result.error.message);
    }
});

Now create the settings based on this ID (or some mapping telling you Sheet1 = id AFD43243DDR3232 with the settings ____).

Does it help?

~ Mikhail Zlatkovsky, developer of the Office Extensibility team, MSFT