[multiple blogs/posts out there noted the issue, no solution was ever published]
What is required to save a Localized Calculated Column Formula using JSOM/REST?
I am injecting the script below into a FldEdit.aspx page.
- It saves the Formula you are editting on every keypress
- providing immediate feedback on errors
This al works fine on English sites but on non-english sites the Formula is displayed in a Localized format
But I can NOT save the Localized format, using REST or JSOM code, the Formula is not accepted.
This screenshots shows the Dutch Localized UI,
not making any changes to the Formula, my custom save action is rejected (red error)
If I change the Formula (in that Localized Dutch site!) to English notation I can save the Formula using JSOM or REST.
It IS stored. a reload of the FldEdit.aspx page displays it in dutch again.
A click on the standard OK button saves the Localized Formula without any issues.
If I change the Formula to English Locale and use the SharePoint OK Button to save/close the page I (ofcourse) get an error.
?? What is required to save a Localized Calculated Column Formula using JSOM/REST?
.
Scriptfile
execute in an existing Formula (FldEdit.aspx page)
as Chrome snippet or as a Bookmarklet or as a GreaseMonkey/TamperMonkey script or stick it into your MasterPage.
/*global SP,document,STSHtmlDecode,clearTimeout,setTimeout,_spPageContextInfo*/
SP.SOD.executeFunc('sp.js', 'SP.ClientContext',
function () {
var textarea = document.getElementById('onetidIODefTextValue1'),
status = function (text, color) {
SP.UI.Status.removeAllStatus(true);
SP.UI.Status.setStatusPriColor(SP.UI.Status.addStatus(text), color);
};
status('CalcMaster loaded', 'yellow');
textarea.onkeyup = function (event) {
if ([37, 38, 39, 40].indexOf(event.keyCode) < 0) { //ignore arrow keys
clearTimeout(typing);
var typing = setTimeout(function () {
var guid = _spPageContextInfo.pageListId.slice(1, 37),
title = document.getElementById('idColName').value,
formula = STSHtmlDecode(textarea.value),
ctx = SP.ClientContext.get_current(),
fields = ctx.get_web().get_lists().getById(guid).get_fields(),
field = ctx.castTo(fields.getByInternalNameOrTitle(title), SP.FieldCalculated);
//formula = ('=IF(TRUE,1,2)');
field.set_formula(formula);
field.update();
status('Saving: ' + title, 'blue');
ctx.executeQueryAsync(function () {
status('Formula saved!', 'green');
}, function (sender, args) {
status(args.get_message(), 'red');
});
}, 500); //debounce, do not save while typing
}
};
}
);
Additional info for 200-point Bounty hunters
You can extract the English formula using JSOM from the schemaXml
var schemaXml=document.createElement('DIV');
schemaXml.innerHTML=field.get_schemaXml();
schemaXml=schemaXml.firstChild;
var formula=schemaXml.getElementsByTagName('Formula')[0].innerText;
BUT
- This formula returns the Internal (and english) names for Columnnames, so saving it causes an error because it does not match the Displaynames (eg. Taskname/Title)
- The , or ; separator also needs to be tackled
iJS