Remove custom properties/metadata for an Office document [javascript]

I have this document library on Sharepoint where I have one custom column called Metadata and that is a lookup to another list on my Sharepoint.

When the users download an Office document from this library and then re-upload it we could have the below error message:

There is at least one lookup column that enforces a relationship behavior and contains values that reference one or more non-existent items in the target list.

It’s because the Office documents keep the custom columns from the document library from where they have been downloaded… In that case the file tries to reassign the Metadata with an ID that doesn’t exist anymore… causing this issue!

Because I’m using a homemade interface to upload documents, I’ve been able to pass some code to delete the file’s properties on-the-fly before pushing it into the document library.

To do so, you need JSZip that will permit to unzip the Office document in order to retrieve the file docProps/custom.xml and to change the properties we want before the final upload.

Let’s imagine my page contains an <input type="file">, and that I have already loaded JSZip. Then I use FileReader:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
var fileReader = new FileReader();
fileReader.onloadend = function(e) {
  // content is the "arraybuffer" that represents my file
  var content = e.target.result;
  // check the file's extension (here "docx", "xlsx", and "pptx", but we could add more extensions
  var ext=file.name.split(".").slice(-1)[0];
  switch (ext) {
    case "docx":
    case "xlsx":
    case "pptx": {
      // load content in JSZip
      JSZip.loadAsync(content)
      .then(function(zip) {
        // unzip the file that contains metadata/properties
        return zip.file("docProps/custom.xml").async("text")
        .then(function(txt) {
          // replace the faulty column
          txt = txt.replace(/name="Metadata"><vt:lpwstr>\d+<\/vt:lpwstr>/,'name="Metadata"><vt:lpwstr><\/vt:lpwstr>');
          // reinject the new file
          zip.file("docProps/custom.xml", txt);
          return zip.generateAsync({type:"arraybuffer"})
        })
      })
      .then(function(content) {
        // do something with your content
        // for example (https://aymkdn.github.io/SharepointPlus/): $SP().list("my_list").createFile({content:content, filename:file.name})
      })
      break;
    }
    default: {
      // for the other files, treat them normally
      // for example (https://aymkdn.github.io/SharepointPlus/): $SP().list("my_list").createFile({content:content, filename:file.name})
    }
  }
}
fileReader.onerror = function(e) {
  console.error(e.target.error);
}
fileReader.readAsArrayBuffer(file);

Émuler Raspberry Pi sous Windows

Depuis que j’ai fait assistant-plugins, j’ai plusieurs utilisateurs qui m’ont demandé comment l’installer sur leur Raspberry. Ce système est censé être similaire à une Debian, cependant ils semblent y avoir des différences… J’ai donc cherché à émuler cet OS sous mon Windows 10.

Sources :

Voici les étapes :

  1. Télécharger la dernière version disponible de QEMU sur le site https://qemu.weilnetz.de/w32/ (par exemple qemu-w32-setup-20171211.exe au moment où j’écris cet article)
  2. Une fois téléchargé, on va faire un clique-droit et dézipper qemu-w32-setup-20171211.exe vers qemu-w32-setup-20171211
  3. Télécharger la dernière version de Raspbian via https://www.raspberrypi.org/downloads/raspbian/ dans le répertoire qemu-w32-setup-20171211/ (ou via ce lien https://downloads.raspberrypi.org/raspbian_latest) (par exemple je prends la version lite qui se nomme 2017-11-29-raspbian-stretch-lite.zip)
  4. Télécharger le kernel dans le répertoire qemu-w32-setup-20171211/ via https://github.com/dhruvvyas90/qemu-rpi-kernel (par exemple j’ai pris kernel-qemu-4.4.34-jessie)
  5. On va immédiatement agrandir la taille de notre image de 5G (pour éviter d’avoir des problèmes d’espace disque plus tard). Pour cela on va se rendre dans le dossier qemu-w32-setup-20171211/ avec CMD et on va taper : qemu-img.exe resize 2017-11-29-raspbian-stretch-lite +5G
  6. On peut lancer l’émulation avec la commande (voir tous les paramètres disponibles) : qemu-system-arm.exe -kernel -cpu arm1176 -m 256 -M versatilepb -serial stdio -append "root=/dev/sda2 panic=1 rootfstype=ext4 rw" -drive "file=,index=0,media=disk,format=raw" -redir tcp:2222::22
    (ce qui va donner chez moi : qemu-system-arm.exe -kernel kernel-qemu-4.4.34-jessie -cpu arm1176 -m 256 -M versatilepb -serial stdio -append "root=/dev/sda2 panic=1 rootfstype=ext4 rw" -drive "file=2017-11-29-raspbian-stretch-lite.img,index=0,media=disk,format=raw" -redir tcp:2222::22)
  7. Si tout se passe comme prévu l’image devrait démarrer et arriver jusqu’à vous demander un login (pi) et pass (raspberry) … Attention, par défaut c’est un clavier QWERTY qui est appliqué, il faut donc taper rqspberry pour le password.
  8. On va passer le clavier en français en tapant : sudo apt-get install console-data, puis sudo sudo dpkg-reconfigure console-data
    On choisit select keymap from fullist puis pc / azerty / French / Same as X11 (latin 9) / Standard.
  9. Si le clavier continue à être en anglais, taper : sudo dpkg-reconfigure keyboard-configuration en sélectionnant le clavier par défaut proposé, puis pour la langue, choisir Other puis French, et ensuite les propositions par défaut.
    Et finalement la commande : sudo setupcon
  10. On va maintenant finir d’agrandir notre partition. Pour cela on tape : sudo fdisk /dev/sda et on suit les instructions ci-dessous :
    • On affiche la table des partitions avec la lettre “p”. On va noter le chiffre qui apparait dans la colonne Start pour la deuxième ligne (celle qui correspond à la partition de type Linux) (chez moi cela vaut 94208)
    • On efface la partition principale avec la lettre “d” (cela devrait être la 2)
    • On crée une nouvelle partition avec la lettre “n”, puis on choisit primary avec “p” et la position “2”
    • Pour le premier secteur on va utiliser la valeur trouvée précédemment (94208 pour moi)
    • On appuie sur “Enter” pour le last sector (pour utiliser la valeur proposée)
    • À la question Do you want to remove the signature?, répondre “N”
    • On écrit la table de partition avec “w”
    • Puis on reboot avec sudo shutdown -r now
    • Après le reboot on va terminer par : sudo resize2fs /dev/sda2
  11. Maintenant on va augmenter la taille du swap : sudo nano /etc/dphys-swapfile où on va remplacer CONF_SWAPSIZE=100 par CONF_SWAPSIZE=1024
  12. On redémarre le service avec : sudo /etc/init.d/dphys-swapfile stop puis sudo /etc/init.d/dphys-swapfile start

Maintenant l’émulateur est prêt à être utilisé. Se reporter aux deux sources fournies en début d’article pour plus de détails et des options supplémentaires !

How to delete a document locked by another user in Sharepoint using JavaScript

When you open a file from Sharepoint, it will receive a short term lock that will prevent others to change some properties on the file.

This protection can be useful, but also very annoying, for example when the file is not closed properly, then the lock could stay “forever”.

There are many posts on the web about it.

I found one that has been super useful: https://pholpar.wordpress.com/2014/04/07/how-to-use-javascript-to-delete-short-term-locks-from-documents-opened-from-sharepoint/
The author explains very well the different steps what I’m trying to summarize:

  1. Send a request to _vti_bin/_vti_aut/author.dll with special headers/body
  2. Auth.dll will provide the lockid
  3. Send a request to _vti_bin/cellstorage.svc/CellStorageService with special headers/body, included the lockid
  4. The file is unlocked

The code to send to CellStorageService, and provided by the author, didn’t work for me. I’ve had to use Fiddler and open the document into Office on my computer to see the kind of requests send by it to unlock a document. Based on it, I’ve re-built the code and you can find my solution below.

Tested on Sharepoint 2013 On-Promise only. I don’t know if this solution works for Sharepoint Online or other version.
Also note that I use $SP().ajax() from SharepointPlus, but it’s equivalent to the $.ajax from jQuery.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
// full path to the document
var docUrl = "https://website.com/website/Doc_Library/Test.docx";
 
// start by querying author.dll to find the lockid and the user who locked it
$SP().ajax({
  url: 'https://website.com/website/_vti_bin/_vti_aut/author.dll',
  headers:{
    "Content-Type": "application/x-www-form-urlencoded",
    "MIME-Version": "1.0",
    "Accept": "auth/sicily",
    "X-Vermeer-Content-Type": "application/x-www-form-urlencoded"
  },
  body: 'method=getDocsMetaInfo%3a14%2e0%2e0%2e6009&url%5flist=%5b' + encodeURIComponent(docUrl) + '%5d&listHiddenDocs=false&listLinkInfo=false',
}).then(function(source) {
  // go thru the source page returned to find the lockid and current user
  var nextLine = false;
  var ret = { "lockid":"", "user":"", when:"" };
  source.split("\n").forEach(function(line) {
    if (line.indexOf("vti_sourcecontrollockid") !== -1) nextLine="lockid"; // vti_sourcecontrollockid -> the lockid to use later
    else if (line.indexOf("vti_sourcecontrolcheckedoutby") !== -1) nextLine="user"; // vti_sourcecontrolcheckedoutby -> username of the user who locked it
    else if (line.indexOf("vti_sourcecontrollockexpires") !== -1) nextLine="when"; // vti_sourcecontrollockexpires -> when the server is supposed to unlock it
    else if (nextLine !== false) {
      ret[nextLine] = line.slice(7).replace(/&#([0-9]|[1-9][0-9]|[[01][0-9][0-9]|2[0-4][0-9]|25[0-5]);/g, function (str, match) { return  String.fromCharCode(match); });
      nextLine = false;
    }
  });
 
  if (!ret.lockid) { alert("Not Locked") }
  else {
    // compose a request based on what Microsoft Office sends to the Sharepoint server
    // found using Fiddler
    var releaseLockReq = '<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/"><s:Body><RequestVersion Version="2" MinorVersion="2" xmlns="http://schemas.microsoft.com/sharepoint/soap/"/><RequestCollection CorrelationId="{96A244BD-D13B-4696-9355-231FB673BC4F}" xmlns="http://schemas.microsoft.com/sharepoint/soap/"><Request Url="'+docUrl+'" UseResourceID="true" UserAgent="{1984108C-4B93-4EEB-B320-919432D6E593}" UserAgentClient="msword" UserAgentPlatform="win" Build="16.0.8201.2102" MetaData="1031" RequestToken="1"><SubRequest Type="ExclusiveLock" SubRequestToken="1"><SubRequestData ExclusiveLockRequestType="ReleaseLock" ExclusiveLockID="'+ret.lockid+'"/></SubRequest></Request></RequestCollection></s:Body></s:Envelope>';
 
    // we send it to the webservice cellstorage.svc
    $SP().ajax({
      url:'https://website.com/website/_vti_bin/cellstorage.svc/CellStorageService',
      body:releaseLockReq,
      headers:{
        'Content-Type':'text/xml; charset=UTF-8',
        'SOAPAction': "http://schemas.microsoft.com/sharepoint/soap/ICellStorages/ExecuteCellStorageRequest"
      }
    })
    .then(function(res) {
      if (res.indexOf('ErrorCode="Success"') !== -1) alert("Success") // the file has been unlocked
      else alert("Failed")
    })
  }
})

I hope it will be useful to someone else!

How to drag and drop a file into a dropzone in HTML5

We can find plenty of demo and tutorials about it on the web, but it took me a while to understand how to setup something easy and quick.

The purpose is to have a zone where we can drop a file from our computer.

You need:

  1. A zone where the file will be dropped (for example a <div></div>)
  2. A minimum of three events:
    • drop
    • dragover
    • dragleave

The three events attached to our dropzone must all call event.preventDefault() otherwise it won’t work as expected.

Then, you can apply a style to the dropzone based on dragover and dragleave, and read the property event.dataTransfer.files from drop to get the file.

See here the super simple example: https://codepen.io/Aymkdn/pen/oovXNY

Overwrite the Created field for a Sharepoint list item

Sharepoint provides automatic fields, like “Created” that contains the creation date of a list item. It’s a readonly field.

You could use a webservice to change the ReadOnly property in order to overwrite its value.

Using SharepointPlus the code is:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
// don't change the below information
// the details about this field are found using $SP().list().info
var updateSystemFields = "<fields>" +
    '<method id="1">' +
    '<field id="{8c06beca-0777-48f7-91c7-6da68bc07b69}" name="Created" sourceid="http://schemas.microsoft.com/sharepoint/v3" staticname="Created" group="_Hidden" colname="tp_Created" rowordinal="0" readonly="FALSE" type="DateTime" displayname="Created" storagetz="TRUE">' +
    "</field></method>" +
    "</fields>";
// send the request to the server
$SP().webService({
  webURL: "https://website.url.com/",
  service: "Lists",
  operation:"UpdateList",
  properties:{
    listName: "Name of the list",
    listProperties: "",
    updateFields: updateSystemFields,
    newFields: "",
    deleteFields: "",
    listVersion: "",
  }
})

Classic ASP to get a remote page with NTLM authenticate

It’s very frustrating to have to work with ASP again, after so many years…

I wanted to use an ASP page to get a remote page. However I needed to pass some NTLM authenticate to it.

After a few searches I found this solution:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
<%
' I want this page to be accessible for Cross Domain, especially from http://my.company.com (using JavaScript)
Response.AddHeader "Access-Control-Allow-Origin", "http://my.company.com"
Response.AddHeader "Access-Control-Allow-Credentials", "true"
 
' get "url" from the parameters
set REMOTE_FILE_URL=Request.QueryString("url")
 
If REMOTE_FILE_URL <> "" Then
  ' You could use MSXML2.ServerXMLHTTP but you would have to hardcoded the login/password
  ' Example:
  '    Set http = Server.CreateObject("MSXML2.ServerXMLHTTP.6.0")
  '    http.open "GET", REMOTE_FILE_URL, False, "domain\user_name", "Password"
  ' So here we'll use "WinHttp.WinHttpRequest.5.1" that will pass the IIS' credentials
  set http = CreateObject("WinHttp.WinHttpRequest.5.1")
  http.SetAutoLogonPolicy 0
 
  ' we can define timeouts (http://msdn.microsoft.com/en-us/library/windows/desktop/aa384061(v=vs.85).aspx)
  ' http.SetTimeouts(resolveTimeout, ConnectTimeout, SendTimeout, ReceiveTimeout)
  ' example: http.SetTimeouts 60000, 60000, 60000, 60000
 
  ' we can add some headers to the request that will be done by the server
  ' http.SetRequestHeader "Content-type", "application/json"
 
  ' multiple options can be defined
  ' https://msdn.microsoft.com/en-us/library/windows/desktop/aa384108(v=vs.85).aspx
  ' examples:
  ' to define the user agent: http.Option(0) = "Mozilla/5.0 (compatible; MSIE 10.0; Windows NT 6.1; Trident/6.0)"
  ' to ignore ssl errors: http.Option(4) = 13056
 
  ' if you use a proxy
  http.SetProxy 2, "proxy:80"
 
  ' method: open(http method, absolute uri to request, async (true: async, false: sync)
  http.open "GET", REMOTE_FILE_URL, False
  http.send
 
  'Response.AddHeader http.GetAllResponseHeaders
  Response.write http.responseText
Else
  Response.write "Parameter 'url' not provided."
End If
%>

Here I use the URL parameter url to call that specific page.

Sharepoint WopiFrame allow framing

In Sharepoint you can use <WebPartPages:AllowFraming runat="server" /> to disable the SAMEORIGIN for X-FRAME-OPTION giving you the opportunity to call your page from an iframe (see this other article).

However you cannot use the same trick to preview a file into the browser with WopiFrame.aspx. But there is a way to do it, using the parameters into the URL.

Let’s say the url to preview your document is:
https://my.company.com/sharepoint/_layouts/15/WopiFrame.aspx?sourcedoc=/files/Demo.docx&action=default

You need to replace action=default with action=embedview, AND you need to call WopiFrame2.aspx instead of WopiFrame.aspx (which will lead to a Something Went Wrong - Sorry, you don't have access to this page).

So the URL to use for your iframe will be:
https://my.company.com/sharepoint/_layouts/15/WopiFrame2.aspx?sourcedoc=/files/Demo.docx&action=embedview

Change a “Choice with Fill-In” field into a magic dropdown [Sharepoint]

Sometimes you want to modify the options for a dropdown box by removing some existing options, however you want to be able to keep these old values for reporting or whatever reasons.

Below is a way to do it by using jQuery and SharepointPlus. It will hide the “Specify your own value” stuff, and add the old value (if any) into the dropdown selection.

For example if you have a Choice field with options “A”, “B”, “C”. Your item ID #1 has value “B”.
After a while you decide to delete “B” and add “D”, but you want to be able to find items with the “B” value.
So you choose “Choice with Fill-In” and apply the below code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
// For flexibility reasons we create them as a Choice with Fill-In option,
//    however we hide the free text field and we show only the dropdown
//    in case of the fill-in has a value, then we add it into the list of options
$SP().formfields(["My Fist Dropdown", "Another One"]).each(function() {
  var $e = this.elem();
  // hide all except the dropdown
  //$e.not('select').hide().filter(':radio:last').closest('tr').hide(); // SP2010
  $e.eq(0).closest('table').find('tr:gt(0)').hide(); // SP2013
  // if we have a value into the fill-in box, then:
  //   - add it into the options
  //   - when another value is selected we check the first checkbox
  var fillValue = $e.last().val();
  if (fillValue) {
    $e.filter('select').append('<option data-fillin="true">'+fillValue+'</option>').each(function() {
      var $this=$(this);
      $this.find('option:last').prop("selected", true);
      $this.removeAttr("onclick").on('change', function(event) {
        var $opt = $(this).find(':selected');
        if ($opt.data("fillin")) {
          $e.filter(':radio:last').prop("checked", true);
        } else {
          $e.filter(':radio:first').prop("checked", true);
        }
      });
    });
  }
})

So now, if you create a new item you’ll see a dropdown with “A”, “C”, and “D” only.
But if you edit your item #1, you’ll have a dropdown with “A”, “C”, “D” and “B”.

Trigger an event when a file is uploaded on Sharepoint 2013 by drag and drop

If you want to trigger an event after a drag&drop a file for an upload operation on Sharepoint, then you’ll have to add some JavaScript into your page.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
// we need to make sure sp.core.js is loaded
SP.SOD.executeOrDelayUntilScriptLoaded(function() {
  window.UploadFinishFunc=function(b, a) {
    typeof g_currentControl.postUploadFunc == "function" && a.status != UploadStatus.CANCELLED && g_currentControl.postUploadFunc(a.files);
    a.status != UploadStatus.CANCELLED && myPostUploadFunc(a);
    g_currentControl.status = ControlStatus.UPLOADED;
    UpdateProgressBar(ProgressMessage.UPLOADED, a);
    RefreshResult(a);
    g_currentControl.status = ControlStatus.IDLE
  }
}, 'sp.core.js');
SP.SOD.executeFunc('sp.core.js')
 
// below is the function that will be called
function myPostUploadFunc(data) {
  console.log("myPostUploadFunc => ",data)
}