Ok, so there are two parts to this:
- Get the data from the SharePoint list
- Put that data where you want it to go in your table
For retrieving the data from the list, I would recommend looking into using the REST API from Javascript code you put on the page where your table is, because then you will have the data in a place where you can easily then turn around and insert it into your table. I'm not going to go into too much detail here about how to do that, there are tons and tons of resources out there to help you figure it out (just google for "sharepoint rest api"), and the details of how that code would be written has entirely to do with which Javascript library you decide to use to help make the HTTP requests. But for your example, you're probably going to want to put together a query like
/_api/web/lists/getbytitle('CurrentTimeFrame')/items?$filter=Title eq 'shipping'&$select=Days
Now, for the second part, you are right that you'll have to add a little more HTML into your table, but I don't think you want to add input
s. An input
will give you a text box that looks like you should be able to enter data into it. You just need something to display the data in a read-only way (unless I'm completely misunderstanding what you are trying to do), so really all you need is an element that you can add an id
to, so that you can find it from your Javascript code. I would just add a div
into your table cell:
<td style="background-color:Black; color:White; text-align:center; font-weight:bold; border-style:inset">
<div id="shippingDays"></div>
</td>
Then when you get the data from the list, you can do
var shippingDaysDiv = document.getElementById("shippingDays");
shippingDaysDiv.innerText = retrievedDays + " Days";
As far as making sure the data in the table is refreshed, you won't really need to worry about that, because you'll set it up so the Javascript code runs every time the page is loaded, so anytime anyone goes to that page, they will always be seeing the newest data because the code dynamically goes out and retrieves the data from the list at that time.
In response to your question in the comments about which Javascript library to use, here are some suggestions:
One of the easiest to use, I think, is jQuery. You will definitely find plenty of examples of using jQuery's $.ajax()
function to call SharePoint's REST API. The downside is that you have to include the whole jQuery library just to be able to use $.ajax()
. That may or may not be a concern for you. Also, you may end up having to use jQuery if you have to support Internet Explorer. Oh, but an upside to using jQuery would be that you can use it to help you make inserting the data that you retrieve into the table a little easier, jQuery is very good for DOM manipulation.
If you don't have to support Internet Explorer, you could try using the native ES6 Fetch API. fetch()
is built-in to modern Javascript implementations (hence - not in IE), so you won't actually have to include an extra library. If you're going to eventually be making SharePoint web parts using SPFx, I would go with fetch()
because the tools provided in SPFx I believe are just wrappers for fetch()
. At least their syntax is very similar.
If you can get away with supporting only IE 11 from the IE family, another option to consider would be axios. Axios syntax for making queries is very similar to jQuery $.ajax()
, so it is pretty easy to understand, but the responses are a bit different, you just have to play around with it a little bit (and read the documentation) to see what's going on there and how to use it.
Based on your update with the code you have tried, here's what I see: a lot of room for improvement, which I can't fully get into here. Sorry if that seems a little harsh, but it is the truth. I'll throw out a brief list of some of the things I see that could improve, but I'm only going to get into the details of what needs to be improved in order to address the crux of your original question - how to get data from a list and insert it into a table.
- Depending on how you are adding that HTML to a SharePoint page, you might not need the outer
<html>
tags or the <head>
and <body>
tags. I would go so far as to say you most likely don't but the reasons for that are off topic here.
- All your inline styles will be much easier to manage if you extract them into CSS classes and use classes on the table elements instead.
- You are loading jQuery twice, which is unnecessary. You first load version 1.4.2 from
jquery.com
and then you immediately override that by loading version 1.11.3 from googleapis.com
, which means technically you have downgraded the version of jQuery you are using. I know that SPServices has a minimum version of jQuery you need to use, but that does not mean you need to use exactly that version. I would load the latest version of jQuery, and only do it once.
- You are using SPServices. There's nothing inherently wrong with SPServices, it's a very helpful library, but if you are on any version of SharePoint beyond 2010, it's probably easier to just query the REST API directly. Even if you did still want to use SPServices, from the looks of your code, you have some problems with how you have that set up too.
- From the looks of your code, it seems like you are trying to append rows to your table, instead of inserting the data directly into a table cell. Not only that, it looks like you are trying to insert a two column row into a nine column table.
I would go back to what I had suggested before: setting yourself up with placeholder div
s that have unique IDs that you can use to locate them later. I am assuming the places you want to insert the data you get from the list are here
<td style="background-color:Black; color:White; text-align:center; font-weight:bold; border-style:inset">Domesticshipping  Business Days</td>
and here
<td style="background-color:Black; color:White; text-align:center; font-weight:bold; border-style:inset">Canadashipping Business Days</td>
So here's how I would set up that row of the table. I am omitting all the styling to make it a bit easier to see what's going on with the HTML itself:
<tr>
<td colspan="3">Domestic</td>
<td><div id="domesticShippingDays"></div></td>
<td></td>
<td colspan="3">Canada</td>
<td><div id="canadaShippingDays"></div></td>
</tr>
Then, in the script block I would do this:
<script type="text/javascript">
// this tells jQuery to wait until the page has loaded before executing
// the inner code. this way we know the table has been rendered and we
// will be able to find the placehoder divs
$(document).ready(function () {
// _spPageContextInfo is a built-in SharePoint javascript object
// that has quite a bit of useful info. in this case we're using
// it to get the URL of the current site as the basis of the REST
// query we will build. as far as the REST query itself, it seems
// that you do not want _only_ the row that has the domestic shipping
// days, but you want the row that has the canada shipping days info
// as well, so we are just going to go ahead and ask the list for _all_ items
var timeFrameRequestUrl = _spPageContextInfo.webAbsoluteUrl + "/_api/web/lists/getbytitle('CurrentTimeFrame')/items";
// now we make the query
$.ajax({
url: timeFrameRequestUrl,
method: "GET",
headers: {
accept: "application/json;odata=verbose"
}
}).done(function (response) {
// the done function fires once the request succeeds
// and the response has been received from the server.
// since the query was for _all_ the list items, they
// will be in an array called "results" that is on a property
// called "d" which is part of the regular SharePoint REST response.
response.d.results.forEach(function (listItem) {
// we are iterating over each list item, so we can check to
// see what the value of the Title field is, and take action accordingly
if (listItem.Title === "domesticshipping") {
// here we grab the placeholder div by using it's ID
// and insert the text we want in it, including the number of days
// in the "days" column (internal name "c3t9")
$("#domesticShippingDays").text(listItem.c3t9 + " Business Days");
}
// you only showed a screenshot of what the domestic shipping
// title column value is, so i'm just guessing at this. needless
// to say, you should be checking for what the actual value is
if (listItem.Title === "canadashipping") {
$("#canadaShippingDays").text(listItem.c3t9 + " Business Days");
}
});
}).fail(function () {
// the fail function will only fire if the request fails.
// you can look up the documentation of this to see how you
// can get more information about exactly what error occurred.
alert("Oops! Something went wrong.");
})
})
</script>
I would also forewarn you that the weakness of this setup is that you are relying on the values of the Title fields, which can be easily edited and changed. If someone at some point goes in to the CurrentTimeFrame
list and changes "domesticshipping" to "Domestic Shipping", the code will break because
if (listItem.Title === "domesticshipping")
will never find the correct list item to get the days from. Unless you go in and update your code to reflect the new field value like
if (listItem.Title === "Domestic Shipping")
As you can imagine, having to go in and update your code every time someone edits the Title of one of those list items will get annoying really quickly.