using REST to query sharepoint
Sharepoint Query Search With REST
Related posts
PowerApps functions
Parse text to number
Filter('Workflow Tasks'; ID = Value(txtId.Text))
Add datas (listItem)
Patch(NewVoie;Defaults(NewVoie);{Num_x00e9_rovoie:"0"&LookUp(NewVoie;ID=1).Num_x00e9_rovoie}))
Update context, and forms datas
SubmitForm(FormBeneficiaires);;ResetForm(FormBeneficiaires);; NewForm(FormBeneficiaires);; UpdateContext({showPopup:false});
If(IsBlankOrError(SubmitForm(Form1)), Set(saveStatus, "An error occured" & Form1.Error), Set(saveStatus, "Operation succeded"))
Navigate to another form
Navigate(Page_infos_enregistrements)
Get query string parameter and set a variable
Set(InitiativeId; Param("ID"))
Get a field from your datasource by ID
First(Filter(Initiatives; ID=1)).Nom
And Or Not
Or(And(Radio1.Selected.Value=4; !IsBlank(txtComment.Text));Radio1.Selected.Value<4)
Update Lookup Field
Patch(
ResultatAnalyses;
First(//here item to update
Filter(
ResultatAnalyses;
Affaire.Id = currentAffaire.ID And Analyse.Id = ThisItem.ID
)
);
{
Title: "notused";
Commentaires: txtGalComment.Text;
Gravite: Rating1.Value;
Affaire: {//lookup field name
Id: currentAffaire.ID;//id of lookup
Value: LookUp(
Affaires;//list who contains lookup value
ID = currentAffaire.ID;//id of lookup
currentAffaire.Title//title of lookup value
)
}
}
)
Patch Choice
TypeIntervention: {Value: dtvTypeIntervention.Selected.Value}
Execute automate with json
'My workflow'.Run(
JSON(
{
SolutionId: selectedSolution.ID,
ImageContent: UploadedImage14.Image
},
JSONFormat.IncludeBinaryData
)
);
Reg ex to get cleaned string
Clear(AttachmentsCollection);
ForAll(
RenameColumns(DataCardValue91.Attachments, "Name", "Name1"),
Collect(
AttachmentsCollection,
Name1
)
);Set(Title1, First(AttachmentsCollection).Value);Set(FileName1, Concat( Split(First(AttachmentsCollection).Value, "" ), If( IsMatch(Result, "([^A-Za-z0-9\.\-])" ), "",Result ) ))
Save Form
SubmitForm(Form1);;If(!IsBlankOrError( Form1.Error); Notify("Une erreur est survenue lors de la sauvegarde " & Form1.Error; NotificationType.Error);Notify("La savegarde a réussi";NotificationType.Information);;Set(currentElement; Form1.LastSubmit))
Sort columns
Set(Month, Distinct(SortByColumns(CurrentMonthMails, "Year", Ascending, "Month", Ascending), Month))
Set date
Set(StartDate, DateAdd(DateTimeValue( Day(Today) &"/"& Month(Today) &"/"& Year(Today) &" 00:00:00"), -30));
Sum
Sum(Filter(CurrentMonthMails, Month = ThisItem.Result ), uniqMails)
SharePoint RestShow Users List
let currentSort = { column: null, direction: 'asc' }; // Store the current sort state
// Creates the style element
function createStyleElement(id, content) {
var style = document.createElement("style");
style.type = "text/css";
style.id = id;
style.innerHTML = content;
if (style.styleSheet) {
style.styleSheet.cssText = content;
} else {
let st = document.getElementById(id);
if (st == undefined) {
var head = document.head || document.getElementsByTagName("head")[i];
head.appendChild(style);
} else {
st.innerHTML = content;
}
}
return style;
}
// Function to filter the table based on dropdown selection
function filterTable(columnIndex, value) {
let table, tr, td, i, select, selectedValue, txtValue;
table = document.querySelector("table");
tr = table.getElementsByTagName("tbody")[0].getElementsByTagName("tr");
select = table.getElementsByTagName("select")[columnIndex];
//debugger;
selectedValue = value;
// Loop through all table rows and hide those that don't match the filter
for (i = 0; i < tr.length; i++) {
td = tr[i].getElementsByTagName("td")[columnIndex];
if (td) {
txtValue = td.textContent || td.innerText;
if (selectedValue === "" || txtValue === selectedValue) {
tr[i].style.display = "";
} else {
tr[i].style.display = "none";
}
}
}
}
function sortTable(columnIndex, direction) {
let table, rows, switching, i, x, y, shouldSwitch;
table = document.querySelector("table");
switching = true;
let tbody = table.querySelector("tbody");
// Set the current sort state
currentSort.column = columnIndex;
currentSort.direction = direction;
while (switching) {
switching = false;
rows = tbody.rows;
for (i = 0; i < rows.length - 1; i++) {
shouldSwitch = false;
x = rows[i].getElementsByTagName("td")[columnIndex];
y = rows[i + 1].getElementsByTagName("td")[columnIndex];
let isNumber = false;
if (!isNaN(x.innerHTML)) {
// Check if rows should switch based on ascending or descending order
if (direction === 'asc') {
if (parseFloat(x.innerHTML) > parseFloat(y.innerHTML)) {
shouldSwitch = true;
break;
}
} else if (direction === 'desc') {
if (parseFloat(x.innerHTML) < parseFloat(y.innerHTML)) {
shouldSwitch = true;
break;
}
}
}
else {
// Check if rows should switch based on ascending or descending order
if (direction === 'asc') {
if (x.innerHTML.toLowerCase() > y.innerHTML.toLowerCase()) {
shouldSwitch = true;
break;
}
} else if (direction === 'desc') {
if (x.innerHTML.toLowerCase() < y.innerHTML.toLowerCase()) {
shouldSwitch = true;
break;
}
}
}
}
if (shouldSwitch) {
rows[i].parentNode.insertBefore(rows[i + 1], rows[i]);
switching = true;
}
}
}
// Function to generate the table
function generateTableFromJson2(jsonArray, select, addHeaders = true) {
const style = `
body{
background-color: #f8f8f8;
}
.parentDiv{
height:400px;
overflow-x:scroll;
white-space: nowrap;
}
ul
{
list-style-type: none;
}
table {
width: 100%;
border-collapse: collapse;
}
th, td {
padding: 8px 12px;
text-align: left;
border: 1px solid #ddd;
}
tbody tr{
max-height: 15px;
}
th {
background-color: #f4f4f4;
color: #000;
}
/* Scrollable table wrapper */
.table-wrapper {
height: 800px;
overflow-y: auto;
border: 1px solid #ddd;
overflow-x:scroll;
}
/* Style for dropdowns in header */
select {
width: 100%;
padding: 4px;
margin-top: 5px;
}
/* Style for the sorting arrows */
.sort-arrows {
cursor: pointer;
margin-left: 5px;
}
`;
createStyleElement("fdiStyle", style);
// Create table element
let table = document.createElement('table');
// Create table header
let header = table.createTHead();
let headerRow = header.insertRow(0);
// Get keys (headers) from the first object in the JSON array
//let keys = Object.keys(jsonArray[0]);
let keys = select.split(",");
if (addHeaders) {
keys.forEach((key, index) => {
if (key !== "__metadata") {
let th = document.createElement('th');
th.innerHTML = key;
// Create a dropdown (select) for filtering
let select = document.createElement('select');
select.addEventListener('change', function () {
const selectedValue = select.value;
filterTable(index, selectedValue);
});
// Populate dropdown with unique values from the JSON data
let uniqueValues = [...new Set(jsonArray.map(item => item[key]))];
// Add a default "All" option for no filter
let optionAll = document.createElement('option');
optionAll.value = "";
optionAll.text = `All`;
select.appendChild(optionAll);
// Create an option for each unique value
if (typeof (uniqueValues[0]) === typeof (1)) {
const pp = uniqueValues.sort((a, b) => {
if (a < b) {
return -1;
}
if (a > b) {
return 1;
}
return 0;
});
pp.forEach(value => {
let option = document.createElement('option');
option.value = value;
option.text = value;
select.appendChild(option);
});
} else
uniqueValues.sort().forEach(value => {
let option = document.createElement('option');
option.value = value;
option.text = value;
select.appendChild(option);
});
// Sort arrows for sorting the columns
let upArrow = document.createElement('span');
upArrow.innerHTML = '⬆️';
upArrow.classList.add('sort-arrows');
upArrow.onclick = () => sortTable(index, 'asc');
let downArrow = document.createElement('span');
downArrow.innerHTML = '⬇️';
downArrow.classList.add('sort-arrows');
downArrow.onclick = () => sortTable(index, 'desc');
th.appendChild(select); // Append the dropdown to the header
th.appendChild(upArrow); // Append the dropdown to the header
th.appendChild(downArrow); // Append the dropdown to the header
headerRow.appendChild(th);
}
});
}
// Create table body and populate rows with data
let tbody = document.createElement('tbody');
jsonArray.forEach((item) => {
let row = tbody.insertRow();
keys = select.split(",");
keys.forEach((key) => {
let cell = row.insertCell();
if (key !== "__metadata") {
cell.setAttribute("nowrap", "nowrap");
if (key === "permissions") {
if (item.permissions !== undefined && item.permissions.length > 0) {
const ul = document.createElement('ul');
item.permissions.forEach((perm) => {
let li = document.createElement('li');
const tablePerm = document.createElement('table');
const tbodyPrem = document.createElement('tbody');
let rowPrem = tbodyPrem.insertRow();
let cellPerm = rowPrem.insertCell();
//member
cellPerm.appendChild(generateTableFromJson2([perm.Member], "LoginName,Title", false))
//perms
cellPerm = rowPrem.insertCell();
cellPerm.appendChild(generateTableFromJson2(perm.RoleDefinitionBindings.results, "Name", false));
tablePerm.appendChild(tbodyPrem);
li.appendChild(tablePerm);
ul.appendChild(li);
});
cell.appendChild(ul);
} else {
cell.innerHTML = " ";
}
} else if (key.indexOf("/") > 0) {
cell.innerHTML = item[key.split("/")[0]][key.split("/")[1]]
} else
cell.innerHTML = item[key]; // Insert each value from the JSON into the table cell
}
});
});
// Append the body to the table
table.appendChild(tbody);
return table;
}
function removeSlasches(select, datas) {
const ret = [];
const fields = select.split(',');
for (let i = 0; i < datas.length; i++) {
const toAdd = {};
for (let j = 0; j < fields.length; j++) {
if (fields[j].indexOf('/') > 0) {
const splitted = fields[j].split('/');
toAdd[splitted.join('')] = datas[i][splitted[0]][splitted[1]];
} else
toAdd[fields[j]] = datas[i][fields[j]];
}
ret.push(toAdd);
}
console.log("removeSlasches", ret);
return ret;
}
async function GetDigestValue(siteUrl) {//
const fetchOptions = {
method: 'POST',
headers: {
'Accept': 'application/json;odata=verbose',
'Content-type': 'application/json;odata=verbose'
}
};
const response = await fetch(siteUrl + "/_api/contextinfo", fetchOptions);
return (await response.json()).d.GetContextWebInformation.FormDigestValue;
}
function sleep(ms) {
return new Promise(resolve => setTimeout(resolve, ms));
}
async function ExecuteQuery(req, fetchOptions, maxRetry = 3, wait = 1, trynum = 1) {
console.log("ExecuteQuery", req, fetchOptions, maxRetry, wait, trynum);
if (trynum >= maxRetry) {
console.log("ExecuteQuery Error", req);
console.log("ExecuteQuery Error", fetchOptions);
throw new Error(`ExecuteQuery error! maxRetry >= trynum`);
}
try {
let startDate = new Date();
let diffMinutes = 0;
respList1 = await fetch(req, fetchOptions);
endDate = new Date();
const diffMs = endDate - startDate;
diffMinutes = Math.floor(diffMs / (1000));
console.log(`seconds ${diffMinutes} queryNumber ${trynum}`)
//avoid 429 too much queries
if (!respList1.ok && respList1.status == 429) {
const errorDetails = await respList1.text(); // Get error details from the response
let err = JSON.parse(errorDetails);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
await sleep(wait * 10);// * trynum
return await ExecuteQuery(req, fetchOptions, maxRetry, (wait * 5), ++trynum);
} else if (!respList1.ok && respList1.status == 503) {//avoid 503 server unavailable / connections error
const errorDetails = await respList1.text(); // Get error details from the response
let err = JSON.parse(errorDetails);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
await sleep(wait * 10);// * trynum
return await ExecuteQuery(req, fetchOptions, maxRetry, (wait * 5), ++trynum);
} else if (!respList1.ok && respList1.status == 403) {//avoid 403 reload page in another tab
//debugger;
window.open(_spPageContextInfo.webAbsoluteUrl, '_blank')
const errorDetails = await respList1.text(); // Get error details from the response
let err = JSON.parse(errorDetails);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
await sleep(wait * 10);// * trynum
window.open(_spPageContextInfo.webAbsoluteUrl, '_blank')
return await ExecuteQuery(req, fetchOptions, maxRetry, (wait * 5), ++trynum);
} else if (!respList1.ok) {
console.log("ExecuteQuery Error", respList1);
const errorDetails = await respList1.text(); // Get error details from the response
console.log("ExecuteQuery Error", respList1);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${errorDetails}`);
let err = JSON.parse(errorDetails);
console.error(`HTTP error! Status: ${respList1.status}, Details: ${err.error.message.value}`);
console.log(err.error.message.value);
throw new Error(`HTTP error! Status: ${respList1.status}`);
}
return respList1
} catch (error) {
console.log(error);
throw new Error(`HTTP error! Status: ${error}`);
}
}
const siteUrl = _spPageContextInfo.webAbsoluteUrl;
const fetchOptions = {
method: 'GET',
headers: {
'Accept': 'application/json;odata=verbose'
}
};
//get web server relative url
let resp = await ExecuteQuery(`${siteUrl}/_api/web?$select=ServerRelativeUrl`, fetchOptions);
let usrType1 = await resp.json();
console.log(usrType1.d);
const webRelativeUrl = usrType1.d.ServerRelativeUrl;
//get user list
resp = await ExecuteQuery(`${siteUrl}/_api/web/lists?$select=RootFolder/ServerRelativeUrl,Id&$expand=RootFolder`, fetchOptions);
usrType1 = await resp.json();
let userListId = "";
usrType1.d.results.forEach(list => {
if (list.RootFolder.ServerRelativeUrl.toLowerCase() === (webRelativeUrl + "/_api/web/siteuserinfolist").toLowerCase() ||
list.RootFolder.ServerRelativeUrl.toLowerCase() === (webRelativeUrl + "/_catalogs/users").toLowerCase()) {
userListId = list.Id;
}
});
if(userListId === ""){
console.log("list not found");
throw new Error("list not found");
}
resp = await ExecuteQuery(`${siteUrl}/_api/web/lists(guid'${userListId}')/items?$select=FirstName,IsActive,LastName,UserName,Modified,Author/Title,JobTitle,Department,IsSiteAdmin,EMail,Title,Name,ContentTypeDisp,Deleted,PrincipalCount,ID&$expand=Author`, fetchOptions);
let j = await resp.json();
console.log(j.d);
resp = await ExecuteQuery(`${siteUrl}/_api/web/siteusers?$select=ID,PrincipalType,IsShareByEmailGuestUser`, fetchOptions);
const usrType = await resp.json();
j.d.results.forEach(element => {
//debugger;
const usr = usrType.d.results.find(u => u.Id === element.ID);
//console.log("usrType", usrType);
if (usr !== undefined) {
element.PrincipalType = usr.PrincipalType;
element.IsShareByEmailGuestUser = usr.IsShareByEmailGuestUser;
} else {
element.PrincipalType = -1;
element.IsShareByEmailGuestUser = false;
}
});
document.body.innerHTML = ``;
const table = generateTableFromJson2(j.d.results, "EMail,Title,PrincipalType,IsShareByEmailGuestUser,Name,IsActive,FirstName,LastName,UserName,Modified,Author/Title,JobTitle,Department,IsSiteAdmin,Deleted,ID");
// Append the table to the container
document.getElementById('tableContainer').appendChild(table);
Add a comment