0

I want to save different sizes of custom settings data into the excel settings. But when the custom setting is too big, saveAsync does not save, which is expected - There is a 2mb limit per add-in.

https://learn.microsoft.com/en-us/javascript/api/office/office.settings?view=word-js-preview

This is my current code:

const bigJsonString = "Very big string";
const jsonObject = JSON.parse(stringValue);
Office.context.document.settings.set('mydata', jsonObject);
Office.context.document.settings.saveAsync((result)=>{
    ...
});

I know that saveAsync will call the callback method with the result. But somehow it works for Office 365 but not for the Excel Desktop.

I want to measure the jsonObject or bigJsonString size and if it is more than 2mb stop the set and saveAsync calls

Something like this:

const bigJsonString = "Very big string";
const jsonObject = JSON.parse(stringValue);
if(GetSize(jsonObject) > 2){
    // acknowledge user that add-in can't save customsetting.
} else {
    Office.context.document.settings.set('mydata', jsonObject);
    Office.context.document.settings.saveAsync((result)=>{
    ...
}
});

How Office-JS measure the size of the settings object? (I want to mimic)

Update (side note):

Based on the below link, it looks like Microsoft removed this 2mb restriction.

What is the office setting limitation for Excel Add in

AntonIva
  • 597
  • 1
  • 6
  • 22

1 Answers1

1

It's possible to measure the size of the setting using JavaScript.

const isWritableSetting = (object) => {
   // .size will return your object size in bytes
   const blob = new Blob([JSON.stringify(object)], { type: 'application/json' })
   return blob.size < 2 /* Megabytes */ * 1000 /* Kilobytes */* 1000;
};

I've tweaked the 1024 to 1000 to be a bit safer; you could validate it to see if it's eligible for 1024 * 1024.

In your code then, all you need to do is:

if (isWritableSetting(object)) ...

A good reference for sizing up objects in JS here: String length in bytes in JavaScript

Mavi Domates
  • 4,262
  • 2
  • 26
  • 45