1

I have several hundred urls I need to paste in to a Sharepoint list. I can paste the urls with no problem, but I need to paste in a Title too (or 'Display Text' to give the exact system name for the field). I like to be able to paste in the 'Display Text' at the same time as pasting the url. I'm working in Edit / Quick Edit mode, rather than the standard mode.

When in Edit mode, I can click in the url field and it will allow me to manually type in the Display Text, but that'll take ages and cause me to lose the will to live.

Image of list item, showing the url column

Research

I've found a couple of posts with proposed solutions, neither of which work for me.

Link 1 and Link 2. This third link also looked useful, it involves using a workflow. *New addition: Link 4 and Link 6. (No, there's no Link 5).

Link 2 Results

The second of the links above looked especially promising. In Excel I pasted in the formula they've used:

=CONCATENATE(“”””,B2,””””,”, “,””””,,A2,””””)

Yet the result I get is a #NAME? error.

Here's a screenshot of what I see in Excel. The formula looks ok to, Excel appears happy to accept it, yet gives a bad result.

Excel picture with Concatenate formula

Link 3 Results

The workflow failed to paste the values correctly; the display text contains both the url and the 'Display Text'.

Picture of failed url, created by workflow

result of using the workflow option

Link 4 Results

THis post has two answers, Sergei's accepted answer has since been deprecated by Microsoft (html no longer permitted in calculated columns). I tried out the second answer, by using a calculated column, but this only returned the value of the text field I'm using as a title for the url.

5. Ganesh's suggestion

Thanks for posting Ganesh. I tried out this method, I'm using Excel. Please note the formula that created the url. As you see in the 2nd and 3rd pictures below, the value 'I am the Title' is posted to both fields, the url has not been pasted in.

enter image description here enter image description here enter image description here

Link 6 Results

(I don't have the correct version of Access to be able to attempt this method by Daniel Šmon, but am posting for reference for others facing the same issue as me. Yes, I'm selfless I know I know...)

  • 1
    I don't know how to help you, but I just wanted to say that I wish every question included screenshots that were this helpfully annotated. – willman Mar 9 at 16:58
  • I think if you just copy paste the URL in hyperlink column in quick edit mode (without clicking the hyperlink icon which opens the pop-up) - just like you input data in single line of text field, it should work for you. It will save the link both in URL as well as Description/Display text field. – Ganesh Sanap Mar 9 at 17:12
  • Is it a requirement that you have to do this by pasting? – Rune Sperre Mar 10 at 8:44
  • 1
    Hi Rune, I'd be happy to use a workflow or some other means (I'm not a programmer, I can use html/css and basic .js). I don't have the right version of Access it seems, otherwise I could try out a solution by Daniel Šmon: danielsmon.com/2014/12/05/… – Tally Mar 10 at 8:56
2

For the Link2, you could try this formula, this works for me.

=CONCATENATE("""",B3,"""",", ","""",A3,"""")
| improve this answer | |
  • This works fine for me, thanks Michael – Tally Mar 10 at 11:09
  • BTW, for anyone making use of this, using Michael's formula in Excel will produce something like this, including the quotation marks: "myurl", "Click Link" - I then pasted this in to Sharepoint using the Edit mode. – Tally Mar 10 at 15:08
  • One other thing to note - I found that my columns in Excel need to be alongside each other, otherwise the formula does not return the 'url, Display Text' – Tally Mar 11 at 8:57
1

This will let you paste ...code! As you are on SP2013 this should work as you still have access to sp.js

The following script will retrieve and loop through all items in the list, combining two columns: URL and URLText into the third column Link for each item. Finally an update is made to the batch.

If you update the list and column names you should be able to paste this into the console of dev tools. See screenshot below code.

function updateListItems() {
    var clientContext = SP.ClientContext.get_current();
    var oList = clientContext.get_web().get_lists().getByTitle('URL');
    var camlQuery = new SP.CamlQuery();
    this.collListItem = oList.getItems(camlQuery);
    clientContext.load(collListItem);
    clientContext.executeQueryAsync(Function.createDelegate(this, this.onQuerySucceeded), Function.createDelegate(this, this.onQueryFailed));
}

function onQuerySucceeded(sender, args) {
    var listItemEnumerator = collListItem.getEnumerator();
    var clientContext = SP.ClientContext.get_current();
    var oList = clientContext.get_web().get_lists().getByTitle('URL');        
    while (listItemEnumerator.moveNext()) {
        var oListItem = listItemEnumerator.get_current();
        var urlValue = new  SP.FieldUrlValue();
        urlValue.set_url(oListItem.get_item("URL"));
        urlValue.set_description(oListItem.get_item("URLText"));
        updateitem = oList.getItemById(oListItem.get_id());
        updateitem.set_item("Link", urlValue);     
        updateitem.update();  
    }
    clientContext.executeQueryAsync(Function.createDelegate(this, ()=>alert("OK!")), Function.createDelegate(this, ()=>alert("FAILED :(")));
}

function onQueryFailed(sender, args) {
    alert('Request failed. ' + args.get_message() + '\n' + args.get_stackTrace());
}

updateListItems();

enter image description here

| improve this answer | |
  • Hi Rune, thanks for posting this. I made a new list called URL to test this out and configured it exactly as you have it. For me I have this value in the Link field: bbc.co.ukhttps://www.bbc.co.uk - so it has doubled up. The 'Display Text' (or URL text) field has worked. My method was: in IE using Edit mode - paste the values in, 'save'. Then in Chrome, <F12> for developer console, I pasted in your script, pressed <Enter> to run. Got the OK! message; refresh page - values appear in the Link field. – Tally Mar 10 at 10:33
  • I don't know if it is relevant (or has any effect on this solution working), but I'm posting here for completeness. In the Console I also see a message saying: "start.js?rev=RJ1wZoVJ%2F7xfKjfBnjaWng%3D%3D:1 [Deprecation] 'window.webkitStorageInfo' is deprecated. Please use 'navigator.webkitTemporaryStorage' or 'navigator.webkitPersistentStorage' instead." – Tally Mar 10 at 10:34
  • In my test, both the URL and URLText fields are just text fields, is it the same in your list? The only way I can see this happening is if it it pulling the data from another hyperlink field... (Also, the Link field is of course of type Hyperlink) – Rune Sperre Mar 10 at 10:39
  • You're right - my URL field was a hyperlink field. I deleted the field and created a new 'URL' single line of text field. I can't run the code again, I get an error. I cleared the cache in Chrome but it's not helped. It's quite a long error, it starts with "sp.js?rev=z%2Fir7JiXnjpUhs5tGBaz4Q%3D%3D:2 Uncaught Error: The property or field has not been initialized. It has not been requested or the request has not been executed. It may need to be explicitly requested." I can post the full thing if it's useful. – Tally Mar 10 at 11:09
  • 1
    No problem, it's fun to try to remember how I used to do things in SP2013 :) I think there must be some problem with the field name (maybe it got a different internal name, you can see that in list settings) - but as long as you got a working solution all is good and well! – Rune Sperre Mar 10 at 11:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.