When using MS Excel to provide reports from SharePoint (SP) data, a common problem is managing the size of the dataset being retrieved. SP sites are notoriously long-lived and data can grow and sprawl over time, with lists having 100,000s or even millions of items in them, and Excel reports linked to those lists can take so long to load they are all but unusable. Using an OData query can help get around these issues.
The easiest way to avoid pulling more data into MS Excel than you need from SharePoint is to time-bound it using a filtered OData query.
The following example shows how to query a list based on fixed dates, filtering on the item created date:
OData.Feed("http://site-url/_vti_bin/listdata.svc/list-title?$filter=Created ge datetime'2020-01-01' and Created lt datetime'2021-01-01'", null, [Implementation="2.0"])Code language: plaintext (plaintext)Although the above might be useful in a number of scenarios, dynamic date ranges might work better. Below are some examples of how to make the date bounds dynamic based on the current date.
Get data for the current calendar year only:
OData.Feed("http://site-url/_vti_bin/listdata.svc/list-title?$filter=Created ge datetime'" & Text.From(Date.Year(DateTime.Date(DateTime.LocalNow()))) & "-01-01' and Created lt datetime'" & Text.From(Date.Year(Date.AddYears(DateTime.Date(DateTime.LocalNow()), 1))) & "-01-01'", null, [Implementation="2.0"])Code language: plaintext (plaintext)Get data for the current financial year to date only:
(Assumes 1st April FY start date, and notice the use of conditional statements to calculate the date range!)
OData.Feed("http://site-url/_vti_bin/listdata.svc/list-title?$filter=Created ge datetime'" & Text.From(if Date.Month(DateTime.Date(DateTime.LocalNow())) < 4 then Date.Year(DateTime.Date(DateTime.LocalNow())) - 1 else Date.Year(DateTime.Date(DateTime.LocalNow()))) & "-04-01' and Created lt datetime'" & Text.From(if Date.Month(DateTime.Date(DateTime.LocalNow())) < 4 then Date.Year(DateTime.Date(DateTime.LocalNow())) else Date.Year(Date.AddYears(DateTime.Date(DateTime.LocalNow()), 1))) & "-04-01'", null, [Implementation="2.0"])Code language: plaintext (plaintext)Get data for the previous full calendar year:
OData.Feed("http://site-url/_vti_bin/listdata.svc/list-title?$filter=Created ge datetime'" & Text.From(Date.Year(DateTime.Date(DateTime.LocalNow())) - 1) & "-01-01' and Created lt datetime'" & Text.From(Date.Year(DateTime.Date(DateTime.LocalNow()))) & "-01-01'", null, [Implementation="2.0"])Code language: plaintext (plaintext)Get data for the previous full financial year:
(Assumes FY runs 1st April to 31st March.)
OData.Feed("http://site-url/_vti_bin/listdata.svc/list-title?$filter=Created ge datetime'" & Text.From(if Date.Month(DateTime.Date(DateTime.LocalNow())) < 4 then Date.Year(DateTime.Date(DateTime.LocalNow())) - 2 else Date.Year(DateTime.Date(DateTime.LocalNow())) - 1) & "-04-01' and Created lt datetime'" & Text.From(if Date.Month(DateTime.Date(DateTime.LocalNow())) < 4 then Date.Year(DateTime.Date(DateTime.LocalNow())) - 1 else Date.Year(DateTime.Date(DateTime.LocalNow()))) & "-04-01'", null, [Implementation="2.0"])Code language: plaintext (plaintext)NOTE: We could change the above financial year info to run 6th April to 5th April each year (like HMRC’s official financial year) simply by changing both the
“-04-01” literal string values to “-04-06”.