CODEWITHSUNDEEP
javascriptexceloffice365worksheetoffice-js
Alex K
Alex K

Reputation: 3192

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

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

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

But after some research I found that the worksheets identifiers always changing after reopening workbook and according documentation it's not a bug, it's a feature.

"{040E0F18-0F61-4CD9-886D-95112C925793}" changed to "{00000000-0001-0000-0000-000000000000}" after reopen (question about that)

Is there way to keep settings for worksheets between opening closing workbook ?

Upvotes: 4

Views: 140

Answers (1)

Michael Zlatkovsky
Michael Zlatkovsky

Reputation: 8670

I can think of one workaround, though I'll admit it's not the most elegant thing in the world.

For any worksheet that you want to save settings for, first create a binding for the "WorksheetName!A1:XFD1048576". You will get an ID back, and this time it's unique and persisted 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 settings based off of that ID (or some mapping telling you that Sheet1 = id AFD43243DDR3232, with settings ____).

Does that help?

~ Michael Zlatkovsky, developer on Office Extensibility team, MSFT

Upvotes: 1

Related Questions