API, Programming

Solved: odata filter by datetime with greater than (or less than) range

Need to filter your odata query and only have the datetime value to work with?

if your format looks like: 2018-12-31T00:00:00

Then you can query it by wrapping it in a datetime tag with quotes. see example below with it combining with an ID filter to only show a person’s information greater than 1/1/2018

https://yourserver/yourservice/YourData?$filter=(%20ID%20eq%201234%20and%20EXP_DATE%20gt%20datetime%272018-01-01T00:00:00.000%27)

Note: you can also do other options like less than (lt) or others.

Onward!

API, Box, C#, Certificates, Web, XML

How To Use the Box API To Adjust User Properties and Make Read-Only in Bulk (w/example)

  • Get up to date on the APIs you need for users @ https://developer.box.com/v2.0/reference
  • Sign up for your developer account at your enterprise @ https://yourenterprise.app.box.com/developers/console (replace with your enterprise application name in the link above)
  • Create your Box app & API Key – link here and here
  • Pull down the Box Windows SDK @ – https://github.com/box/box-windows-sdk-v2
  • Update the pem file with your actual pem information from the app you created
    • in case you’re wondering it should contain the —–BEGIN ENCRYPTED PRIVATE KEY—– and —–END ENCRYPTED PRIVATE KEY—–
  • Update the properties appropriately in the app.config
  <appSettings>
    <add key="boxClientId" value="uniquestringhere" />
    <add key="boxClientSecret" value="uniquestringheretoo" />
    <add key="boxEnterpriseId" value="1234567" />
    <add key="boxPrivateKeyPassword" value="uniquestringhereforpassword" />
    <add key="boxPublicKeyId" value="uniquepublickeyid" />
    <add key="ClientSettingsProvider.ServiceUri" value="useifneeded" />
  </appSettings>
  • From there you can update your Main runner in the program.cs appropriately. In the case of my sample below I am getting authorized, retrieving my users and looping through everyone but the main admin account and making them read-only. I’m also writing the information out to a file so I’ll have the logs of who was set / not set.
 
            var privateKey = File.ReadAllText("private_key.pem");

            var boxConfig = new BoxConfig(CLIENT_ID, CLIENT_SECRET, ENTERPRISE_ID, privateKey, JWT_PRIVATE_KEY_PASSWORD, JWT_PUBLIC_KEY_ID);
            var boxJWT = new BoxJWTAuth(boxConfig);

            var adminToken = boxJWT.AdminToken();
            Console.WriteLine("Admin Token: " + adminToken);
            Console.WriteLine();

            var adminClient = boxJWT.AdminClient(adminToken);

            var items = await adminClient.UsersManager.GetEnterpriseUsersAsync("", 0, 1000);
            items.Entries.ForEach(async i =>
            {
                if (i.Login != "myspecialadminaccount@domain.com")
                {
                    BoxUserRequest userRequest = new BoxUserRequest()
                    {
                        Id = i.Id,
                        Status = "cannot_delete_edit_upload"
                    };
                    System.Console.WriteLine("\t{0}", i.Name);
                    System.Console.WriteLine("\t{0}", i.Id);
                    System.Console.WriteLine("\t{0}", i.Login);
                    System.Console.WriteLine("\t{0}", i.Type);
                    System.Console.WriteLine(" ");
                    // Turn on for prod
                    BoxUser user = await adminClient.UsersManager.UpdateUserInformationAsync(userRequest);
                    Console.WriteLine(userRequest.Name + "updated to read-only");
                    System.Threading.Thread.Sleep(2000);
                    stringtext = i.Name.ToString() + " - " + i.Id.ToString() + " - " + i.Login.ToString() + " - " + i.Type.ToString() + " - updated to readonly" + Environment.NewLine;
                    System.Console.WriteLine(" ");
                    
                }
                else
                {
                    BoxUserRequest userRequest = new BoxUserRequest()
                    {
                        Id = i.Id
                    };
                    System.Console.WriteLine("\t{0}", i.Name);
                    System.Console.WriteLine("\t{0}", i.Id);
                    System.Console.WriteLine("\t{0}", i.Login);
                    System.Console.WriteLine("\t{0}", i.Type);
                    System.Console.WriteLine(" ");
                    Console.WriteLine(userRequest.Name + " NOT updated to read-only");
                    System.Threading.Thread.Sleep(2000);
                    stringtext = i.Name.ToString() + " - " + i.Id.ToString() + " - " + i.Login.ToString() + " - " + i.Type.ToString() + " - NOT updated to readonly" + Environment.NewLine;
                    System.Console.WriteLine(" ");
                    
                }
            });
            File.AppendAllText("C:\\Temp\\" + "log.txt", stringtext);
  • Enable your box application to your enterprise accounts – link here
  • Run your app (In TEST with test accounts! Then, with approval, prod.)
    • If the app won’t run…check:
      • code syntax errors
      • certificate pem
      • values in the app config
      • authorization status in the admin Console on Box
      • that your app is enabled and still available on the enterprise developer site

Hope this helps! I can send the source out on request if needed (just let me know).

API, CRM, JavaScript, Programming, SharePoint, Troubleshooting, Web

Solved: Issue with SharePoint Rest API Document Upload – Solving the case of the single apostrophe/quote in the URL POST

So I ran across an interesting mystery a while back that I thought I would share the fix for once I had time to document it. I have a client that uses an embeddable SharePoint page within CRM to allow document uploads right from the front entity screens without having to deviate to another screen and for the most part things have been going well. Until…the mystery began.

The problem: In some sporadic cases when uploading documents via POST to the REST API users would encounter an issue with the dynamically generated URLs from SharePoint that contain single apostrophes or quotes in the library name that would be used in the POST URL.

Errors may look like:

{"error":{code":"-1,Microsoft.SharePoint.Client.InvalidClientQueryException","message":{"lang":en-US","value":"The expression\"web/getfolderbyserverrelativeurl('yourfolder/O'Lastname, Firstname - 23424ID')/files/add(overwrite=true,url='filename.pdf')\" is not valid."}}}

The odd part about that? If you browse the library on its own, it’s fine! However, trying to post to the REST API with an improperly escape quote is not. So let’s fix that.

The solution: It’s all in how you build the URL with properly escaped characters.

Snippet in question:

    // Add the file to the file collection in the Shared Documents folder.
    function addFileToFolder(arrayBuffer) {

        // Get the file name from the file input control on the page.
        var parts = fileInput[0].value.split('\\');
        var fileName = parts[parts.length - 1];

        // Construct the endpoint.
        var fileCollectionEndpoint = String.format(
                "{0}/_api/web/getfolderbyserverrelativeurl('{1}')/files" +
                "/add(overwrite=true, url='{2}')",
                serverUrl, serverRelativeUrlToFolder, fileName);

        // Send the request and return the response.
        // This call returns the SharePoint file.
        return jQuery.ajax({
            url: fileCollectionEndpoint,
            type: "POST",
            data: arrayBuffer,
            processData: false,
            headers: {
                "accept": "application/json;odata=verbose",
                "X-RequestDigest": jQuery("#__REQUESTDIGEST").val(),
                "content-length": arrayBuffer.byteLength
            }
        });

Fixed snippet:

    // Add the file to the file collection in the Shared Documents folder.
    function addFileToFolder(arrayBuffer) {

        // Get the file name from the file input control on the page.
        var parts = fileInput[0].value.split('\\');
        var fileName = parts[parts.length - 1];

        // Construct the endpoint.
        var fileCollectionEndpoint = String.format(
                "{0}/_api/web/getfolderbyserverrelativeurl('{1}')/files" +
                "/add(overwrite=true, url='{2}')",
                serverUrl, serverRelativeUrlToFolder.replace(/\%27/g,"''"), fileName);

        // Send the request and return the response.
        // This call returns the SharePoint file.
        return jQuery.ajax({
            url: fileCollectionEndpoint,
            type: "POST",
            data: arrayBuffer,
            processData: false,
            headers: {
                "accept": "application/json;odata=verbose",
                "X-RequestDigest": jQuery("#__REQUESTDIGEST").val(),
                "content-length": arrayBuffer.byteLength
            }
        });

Conclusion? With the replace regex fix in place it will now take every occurance (and not just the first, thanks regex) of a single quote that come as a part of the folder directory and properly escape them when I want to post a file back with the API.

Key change:

serverRelativeUrlToFolder.replace(/\%27/g,"''")

Everybody wins now, espcially the people out there with apostrophes in there names and folder names with titles that still need the quotes in them! Hope this helps.

References:

API, C#, Programming, SMS, Web Service

C# Send A SMS Text Message Programmatically Using Clickatell HTTP API

I would start off by heading here to better understand the Clickatell HTTP API: https://www.clickatell.com/apis-scripts/apis/http-s/. If you are interested they allow you 10 free text messages to try it out before you buy a package. Just sign up for an account and create your HTTP API and use your information to populate the snippet below.

For a particular project I am using this API to send out texts for verification purposes. I am using my method inside of a web service to send the information about a user as well as their pre-generated pin. Of course it is a lot different in my project but I am providing the bare bones below.

If you are a skimmer and you skipped reading anything I said above then here you go:

Code for MyWebRequest class:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Net;
using System.IO;
using System.Text;
using System.Configuration;

namespace CustomWebServices
{
public class MyWebRequest
{
private WebRequest request;
private Stream dataStream;

private string status;

public String Status
{
get
{
return status;
}
set
{
status = value;
}
}

public MyWebRequest(string url)
{
// Create a request using a URL that can receive a post.

request = WebRequest.Create(url);
}

public MyWebRequest(string url, string method)
: this(url)
{

if (method.Equals("GET") || method.Equals("POST"))
{
// Set the Method property of the request to POST.
request.Method = method;
}
else
{
throw new Exception("Invalid Method Type");
}
}

public MyWebRequest(string url, string method, string data)
: this(url, method)
{

// Create POST data and convert it to a byte array.
string postData = data;
byte[] byteArray = Encoding.UTF8.GetBytes(postData);

// Set the ContentType property of the WebRequest.
request.ContentType = "application/x-www-form-urlencoded";

// Set the ContentLength property of the WebRequest.
request.ContentLength = byteArray.Length;

// Get the request stream.
dataStream = request.GetRequestStream();

// Write the data to the request stream.
dataStream.Write(byteArray, 0, byteArray.Length);

// Close the Stream object.
dataStream.Close();

}

public string GetResponse()
{
// Get the original response.
WebResponse response = request.GetResponse();

this.Status = ((HttpWebResponse)response).StatusDescription;

// Get the stream containing all content returned by the requested server.
dataStream = response.GetResponseStream();

// Open the stream using a StreamReader for easy access.
StreamReader reader = new StreamReader(dataStream);

// Read the content fully up to the end.
string responseFromServer = reader.ReadToEnd();

// Clean up the streams.
reader.Close();
dataStream.Close();
response.Close();

return responseFromServer;
}
}
}

And now the code for the method itself:

public string SendText(string mobile, string pin)
{
// Make sure to add the country code to ensure the text goes through
string phoneCheck = "";
if (mobile.Length == 10)
{
phoneCheck = "1" + mobile;
}
// Clickatell settings
string textmessage = "Your verification pin is " + pin;
string parameters = "";
parameters = "user=yourclickatellusername";
parameters = parameters + "&amp;password=yourclickatellpassword";
parameters = parameters + "&amp;api_id=youruniqueclickatellhttpapiid";
parameters = parameters + "&amp;to=" + phoneCheck;
parameters = parameters + "&amp;text=" + textmessage;
parameters = parameters + "&amp;mo=1";
parameters = parameters + "&amp;from=15558675309";
MyWebRequest myRequest = new MyWebRequest("<a href="http://api.clickatell.com/http/sendmsg">http://api.clickatell.com/http/sendmsg</a>", "POST", parameters);
myRequest.GetResponse();
} // SendText

* Note: If you put this call on a server that will require a proxy then add it to your MyWebRequest class