Retrieve all fields in sharepoint list using jquery or javascript

Question

question

venkateshpadmanabhan-5594 asked Mar 25, '22 RaytheonXie-MSFT commented Apr 5, '22

Retrieve all fields in sharepoint list using jquery or javascript

Hi.
I am trying to retrieve the contents of sharepoint custom list using javascript/jquery. The data should be moved to csv file.
I found an example, which uses below code to move data to csv, but has the fields hardcoded. Is it possible to achieve this with all fields and field names not being hardcoded.

 var webUrl = "siteurl";
 var listName = "listname";
 var fields = ["Id", "FileLeafRef", "Modified"];
 var today = new Date();
 today.setHours(0,0,0,0);
 $.ajax({
     url: webUrl + "/_api/web/lists/GetByTitle('" + listName + "')/items?$filter=Modified ge DateTime'" + today.toISOString() + "'&$select=" + fields.join(","),
     type: "GET",
     headers: {"Accept": "application/json; odata=verbose"}
 }).done(function(data) {
     var results = data.d.results;
     var csv = "data:text/csv;charset=utf-8," + fields.join(",") + "\n";
     for (var j = 0; j < results.length; j++) {
         for (var k = 0; k < fields.length; k++) {
             csv += results[j][fields[k]];
             csv += k < fields.length - 1 ? "," : "";
         }
         csv += "\n";
     }
     var a = document.createElement("a");
     a.setAttribute("href", encodeURI(csv));
     a.setAttribute("download", "data.csv");
     document.body.appendChild(a);
     a.click();
 });

sharepoint-dev office-sharepoint-server-development

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 1 · 2022-03-25T14:05:30.33Z

RobWindsor-4651 answered Mar 25, '22 venkateshpadmanabhan-5594 commented Mar 25, '22

Try removing the $select parameter to get all the field values. If there's still one or more values missing from the response, trying change the end of the request URL to &$select=*,FieldValuesAsText&$expand=FieldValuesAsText. Sometimes FieldValuesAsText includes field values not returned by default by the REST API.

Something like this, https://robwindsortest991.sharepoint.com/sites/Demo/_api/Web/Lists/GetByTitle('Products')/Items?$select=*,FieldValuesAsText&$expand=FieldValuesAsText

Fiddler trace

image.png (24.1 KiB)

image.png (27.6 KiB)

· 3

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

venkateshpadmanabhan-5594 · Mar 25 at 02:19 PM

Thanks for the quick response.

In the example, which you had provided, how to achieve the below:

var fields = ["Id", "FileLeafRef", "Modified"]; // capture all the fields
var csv = "data:text/csv;charset=utf-8," + fields.join(",") + "\n";

0 ·

RobWindsor-4651 venkateshpadmanabhan-5594 · Mar 25 at 02:28 PM

@venkateshpadmanabhan-5594 I'm sorry, but perhaps I don't understand what you're asking. What I think you are asking is how to use jQuery.ajax to make a SharePoint REST API call to get all the field values for the items in a list without hard coding the field names. The answer I gave showed you how to do that. The response from the REST API request will include an array of JavaScript objects with properties representing the field values. So, if you have a Yes/No field named Discontinued, the object will have a property named Discontinued whose value will be a boolean.

I hope this clears up any confusion.

0 ·

venkateshpadmanabhan-5594 RobWindsor-4651 · Mar 25 at 03:52 PM

Thanks.

Can the fields from the list be saved to an array ?

0 ·

Answer 2 · 2022-03-28T07:10:51.867Z

RaytheonXie-MSFT answered Mar 28, '22 RaytheonXie-MSFT commented Apr 5, '22

Hi @venkateshpadmanabhan-5594 ,
I agree with RobWindsor-4651's answer. We can use FieldValuesAsText to get all fields. If you want to save the fields to array, we can use Object.keys(). You can refer to following code.

             var webUrl = "https://xxx.sharepoint.com/sites/abc";
             var listName = "TestList";
             $.ajax({
                 url: webUrl + "/_api/web/lists/GetByTitle('" + listName +
                     "')/items?$select=*,FieldValuesAsText&$expand=FieldValuesAsText",
                 type: "GET",
                 headers: {
                     "Accept": "application/json; odata=nometadata"
                 }
             }).done(function(data) {
                 var results = data.value;
                 var FieldValues = results[0]["FieldValuesAsText"];
                 var fields = Object.keys(FieldValues);
                 console.log(fields);
                 var csv = "data:text/csv;charset=utf-8," + fields.join(",") + "\n";
                 for (var j = 0; j < results.length; j++) {

                     for (var k = 0; k < fields.length; k++) {
                         csv += results[j][fields[k]];
                         csv += k < fields.length - 1 ? "," : "";
                     }
                     csv += "\n";
                 }
                 var a = document.createElement("a");
                 a.setAttribute("href", encodeURI(csv));
                 a.setAttribute("download", "data.csv");
                 document.body.appendChild(a);
                 a.click();
             });

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 7

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

venkateshpadmanabhan-5594 · Mar 28 at 12:49 PM

Hi. Thanks for reply. I added above code in script editor. Added the jquery and script tags. Saved the Page. But the hyperlink does not appear. In the console, i can see all the list fields displayed. I do not see any error in the console.
Can you please let me know, what did i miss in the link not showing up ?
Even though no link was created, i can see the file being downloaded. However, all the columns which has data in the list are downloaded as value - undefined .
Why is the data shows as undefined and the original values not shown?

0 ·

RaytheonXie-MSFT venkateshpadmanabhan-5594 · Mar 29 at 06:36 AM

Hi @venkateshpadmanabhan-5594 ,
The value of undefined is because the data is an undefined object. Such as user and group column. The item contains multiple values. If we only need to output the user name. We need to use hardcode.

0 ·

venkateshpadmanabhan-5594 RaytheonXie-MSFT · Mar 29 at 12:25 PM

Hi.
I would not be able to hardcode the column types and change things. Is there any option which can be done in the code to check the column type and get the correct column value, instead of undefined ?

0 ·

Show more comments

RaytheonXie-MSFT · Apr 05 at 08:57 AM

Hi @venkateshpadmanabhan-5594 ,
Have you tried the solution I proposed?

If you have any questions or progress, you can contact me in time.

Looking forward to your reply

Have a lucky day!

Thanks,
Raytheon Xie

0 ·

question