Calculating HMAC SHA-1 in the Browser

If you’re looking for the equivalent of hash_hmac('sha1', 'string', 'secret'); in JavaScript, then here you go:

async function hmac_sha1 (str, secret) {
  // see https://stackoverflow.com/a/47332317/1134119
  let enc = new TextEncoder("utf-8");
  let key = await window.crypto.subtle.importKey(
    "raw", // raw format of the key - should be Uint8Array
    enc.encode(secret),
    { // algorithm details
      name: "HMAC",
      hash: {name: "SHA-1"}
    },
    false, // export = false
    ["sign", "verify"] // what this key can do
  );
  let signature = await window.crypto.subtle.sign(
    "HMAC",
    key,
    enc.encode(str)
  );
  let b = new Uint8Array(signature);
  return Array.prototype.map.call(b, x => x.toString(16).padStart(2, '0')).join("");
}

Connect to SharePoint Online using an app clientId and clientSecret

Get `clientId` and `clientSecret`

(source)

You’ll need credentials:

  • `clientId` – required string, client id obtained when registering the addin
  • `clientSecret` – required string, client secret obtained when registering the addin
  • `realm` – your SharePoint Online tenant id. The easiest way to find tenant is to open SharePoint Online site collection, click Site Settings → Site App Permissions. Under this page you wll see at least one app « Microsoft.SharePoint ». The tenant id (realm) is highlighted in the image below:

Example of the expected result: 

{
  clientId: '28bq7e56-8c3a-487d-hbfb-ef1a74539cbe',
  clientSecret: 's6LZ4VvoeKOS+MyAhklcavsyJBF4XhWo06OgY6czYJ0=',
  realm: '85e5f09b-4c17-4d80-afea-260bb171c456'
}

To get the credentials, you need to register a new addin inside SharePoint Online, by fellowing these steps:

  1. Open SharePoint Online app registration page, e.g. https://contoso.sharepoint.com/sites/dev/_layouts/15/appregnew.aspx
  2. Click on « Generate » for Client id and Client Secret, fill in Title, App Domain, Redirect URI (you can type in any values you want)
  3. Click on « Create » and save generated Client Id and Client Secret
  4. [IF YOU HAVE TENANT RIGHTS] Now you need to apply permissions to the newly registered app. If you want to register the app once and use it for any site collection, it’s better to apply tenant scope permissions, so you can use the credentials everywhere inside your SharePoint tenant. To apply tenant scoped permissions, open AppInv.aspx page under SharePoint adminstration web site, e.g. https://[YOUR_ORGANIZATION]-admin.sharepoint.com/_layouts/15/appinv.aspx, copy paste Client Id from step n°3 into App Id field and click « Lookup ».
  5. [IF YOU HAVE TENANT RIGHTS] You will see your registered app, paste in the following XML into the « Permission Request XML » field and click « Create »: 
        <AppPermissionRequests AllowAppOnlyPolicy="true">
      <AppPermissionRequest Scope="http://sharepoint/content/tenant" Right="FullControl" />
    </AppPermissionRequests>

  6. [IF YOU ARE NOT A TENANT] If you only want to give permissions on 1 site collection, you can register the app on a regular site collection by using url https://contoso.sharepoint.com/sites/dev/_layouts/15/appinv.aspx. In this case you are not able to use tenant scoped permissions and can only apply site collection permissions: 
        <AppPermissionRequests AllowAppOnlyPolicy="true">
      <AppPermissionRequest Scope="http://sharepoint/content/sitecollection" Right="FullControl" />
    </AppPermissionRequests>
  7. You will see addin « Trust » confirmation, click on « Trust It »:

    if trust-it button is not enabled and you get a red label saying tenant admin needs to trust the app, go back and try again in a few minutes.
  8. Now you can use client id and client secret to send authenticated http requests.

To know more about the XML permissions, you can check the Microsoft documentation.

Get Access Token

(you can find a C# code as an example)

You need to do a POST request to https://accounts.accesscontrol.windows.net/[YOUR_TENANT_REALM]/tokens/OAuth/2 with a « Content-Type » header that has the value « application/x-www-form-urlencoded », and the body parameters that must be:

  • "grant_type":"client_credentials"
  • "client_id":"[YOUR_CLIENT_ID]@[YOUR_TENANT_REALM]"
  • "client_secret":"[YOUR_CLIENT_SECRET]"
  • "resource":"00000003-0000-0ff1-ce00-000000000000/dell.sharepoint.com@[YOUR_TENANT_REALM]"

See below an example in PHP:

$curl = curl_init();
curl_setopt($curl, CURLOPT_URL, "https://accounts.accesscontrol.windows.net/[YOUR_TENANT_REALM]/tokens/OAuth/2");
curl_setopt($curl, CURLOPT_HTTPHEADER, [ "Content-Type: application/x-www-form-urlencoded" ]);
curl_setopt($curl, CURLOPT_POSTFIELDS, http_build_query([
  "grant_type" => "client_credentials",
  "client_id" => "[YOUR_CLIENT_ID]@[YOUR_TENANT_REALM]",
  "client_secret" => "[YOUR_CLIENT_SECRET]",
  "resource" => "00000003-0000-0ff1-ce00-000000000000/dell.sharepoint.com@[YOUR_TENANT_REALM]"
]));
curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
$response = json_decode(curl_exec($curl));
curl_close($curl);

echo $response->access_token;

The response should contain an access token. Example:

{
  "token_type":"Bearer",
  "expires_in":"86399",
  "not_before":"1679393911",
  "expires_on":"1679480611",
  "resource":"00000003-0000-0ff1-ce00-000000000000/dell.sharepoint.com@[YOUR_TENANT_REALM]",
  "access_token":"eyJ0eXAiOiJKV1QiLCJhbGciOiJSU[...]SxXA5Lqbk1OcOVdwQ"
}

Finally, you can do your REST API request to SharePoint Online with passing the header « Authorization » that has the value « Bearer [YOUR_ACCESS_TOKEN] ».

Mount a SMB Network Drive with Pivotal Cloud Foundry (pcf) and access it using NodeJS

Connect to the Apps Manager and navigate to the space where you want to mount the drive. Look at the members, and make sure your user has the correct roles:

Then in a console, connect and log into the pcf space (something like cf login and cf -s APPNAME).

Next, try the command cf create-service smb Existing Files_Share -c '{\"share\":\"//server.name.domain.com/Files_Share\"}' (the backslashes are important if you’re on Windows).

Now you can bind the service with cf bind-service APPNAME Files_Share -c '{\"username\":\"username_without_domain\",\"password\":\"network_password\",\"mount\":\"/mnt/Files_Share\"}'

For more info, see https://docs.cloudfoundry.org/devguide/services/using-vol-services.html#smb

spfx error: No development certificate found. Generate a new certificate manually, or set the `canGenerateNewCertificate` parameter to `true` when calling `ensureCertificateAsync`

When using the command gulp serve, you could receive the below error:

No development certificate found. Generate a new certificate manually, or set the `canGenerateNewCertificate` parameter to `true` when calling `ensureCertificateAsync`

To resolve, you can type gulp trust-dev-cert.

Deploy a PCF NodeJS app as a scheduled task

I have a NodeJS app that runs as a process and that executes a task every 15 minutes using node-schedule.

We first need a manifest.yml file that contains:

---
applications:
- name: APP-NAME
  buildpack: nodejs_buildpack
  no-route: true
  health-check-type: process
  env:
    OPTIMIZE_MEMORY: true

The no-route parameter is true so that we don’t get a route assigned, and the health-check-type is set to process so that the orchestrator monitors process availability and doesn’t try to ping a non-existent web endpoint. And OPTIMIZE_MEMORY in « env » section is based on the Pivotal recommendations.

If you need to use a local package in your app, you’ll have to pack it up first. To do it, go to your local module folder, and type npm pack. It will create a .tgz file that you’ll have to store in a local_modules folder for your app. Next, use npm install .\local_modules\package-1.2.3.tgz.

You can now deploy your app with pcf push APP-NAME and you can read the logs with cf logs APP-NAME --recent.

Power Automate: execute a SQL Query via On-Promise Gateway

In Power Automate, when you want to connect to a SQL Server and if you have a On-Promise Gateway, then you cannot use the command « Execute a SQL Query » because it will say it’s not currently supported.

There is a workaround with « Transform data using Power Query » (ATTENTION: you cannot load it from a flow from a Solution… you’ll have to go to your Flows and edit the flow from there):

Let’s say we have 3 tables: ITEM_CATALOG, CATALOG and CURRENCY. We want to join them and filter them based on a variable found previously in our flow.

First, we can define our where. Here I have several values that I want to test using a IN:

I create a string with my different values separated by a coma.

Next, we can open the Power Query editor:

In the interface, we choose the 3 tables we need to merge and we add a parameter called « where »:

We rename it to « where » and leave the default settings:

Then we use the « Advance Editor »:

And we wrote the below:

let
  where = Text.Split( "@{variables('where')}" , ",")
in
  where

It means we want to split the variable « where » coming from the flow, based on the coma separator:

We can now merge the tables and add a filter:

And when the step to filter is here, we select « in » and our query:

Last step is to « Enable Load » to make sure this is what the operation will return to our flow:

You can run it to test and see if it works.

Then, to get the output from it, we’ll use a « Parse JSON »… The schema is probably something like:

{
    "type": "object",
    "properties": {
        "resultType": {
            "type": "string"
        },
        "value": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "COLUMN_A": {
                        "type": "string"
                    },
                    "COLUMN_B": {
                        "type": "integer"
                    },
                    "COLUMN_C": {
                        "type": "string"
                    }
                },
                "required": [
                    "COLUMN_A",
                    "COLUMN_B",
                    "COLUMN_C"
                ]
            }
        }
    }
}

You may need to make several tries in order to find the correct schema. You can also use the « Generate from sample » by pasting the data from the previous step:

We use « value » in the loop:

And then we can access our columns:

Pass an URL parameter to a SharePoint Online form’s field

The only way to pass a URL parameter to a SharePoint Online (modern design) form’s field is to use PowerApps (at least, if you cannot add any JS on your website!).

Important warning: when you use PowerApps to manage your form, all edits to the list settings won’t reflect to the PowerApps form. For example, if you add a field, it won’t show up, and you’ll have to manually update the PowerApps to add it (see at the bottom of this article).

From the list view, go to Integrate then PowerApps and Customize forms:

Once PowerApps has open the form, you need to do several things.

1. Load the ID

We first need to make sure the form will load the required item when we pass the ID URL parameter:

From the SharepointForm Advanced Settings, we change the DefaultMode to check if we have the ID parameter, and if we don’t have it, then it should be a New Form, otherwise an Edit Form:

If(IsBlank(Param("ID")), FormMode.New, FormMode.Edit)

From the SharepointForm Advanced Settings, we change the Item section to check if we have the ID parameter, and if we have it, then we do a lookup in our list to find the data:

If(IsBlank(Param("ID")), SharePointIntegration.Selected, LookUp('NAME OF THE LIST', ID = Value(Param("ID"))))

Add a SUBMIT button

With PowerApps, there is no button to save the changes! We’ll add a button in the form:

In the button’s properties, we change the onSelect to be:

SubmitForm(SharePointForm1)

Be aware that the page will stay with the form after clicking on the button. You could want to close using the Exit() function, but the user will be redirected on office.com … I’d recommend to use Launch() by redirecting your users to a page:

SubmitForm(SharePointForm1); Launch("https://tenant.sharepoint.com/sites/MySite/");

Set field’s value based on URL parameter

We can finally set the field’s value based on the parameter in the URL. Select the INPUT zone of the field, and in the Default section we use the below formula:

If(IsBlank(Param("Title")), ThisItem.Title, Param("Title"))

Here my field is called « Title » so I decided to use a parameter called « Title » as well.

Link to the form

We cannot use the NewForm.aspx or EditForm.aspx to access this form, but we need a special link.

Go to your list settings:

Then go to the form settings (it’s from there that you can decide to keep PowerApps or use the original Sharepoint Forms), and click on See versions and usage:

You’ll get the App Id from this page:

Next, you’ll use the App Id to forge the URL: https://apps.powerapps.com/play/providers/Microsoft.PowerApps/apps/APP_ID
With our example, the URL will be: https://apps.powerapps.com/play/providers/Microsoft.PowerApps/apps/c6f23ac1-dcbd-4daf-925e-2701ab241ca0

You can now pass the URL parameter: https://apps.powerapps.com/play/providers/Microsoft.PowerApps/apps/APP_ID?Title=Hello%20World
And an ID to retrieve an existing item: https://apps.powerapps.com/play/providers/Microsoft.PowerApps/apps/APP_ID?Title=Hello%20World&ID=2

How to use it with a LookUp column?

If you want to auto-select a LookUp field using an URL parameter, you need to do a few things…

First, we need to add the related table. To do so, click on Data in the left navigation bar and search for SharePoint:

Search for the table and add it.

Second (optional) step: click on the Lookup field in the form and change the Items to show a list of options – if no « Lookup » ID in the URL, then we use the default list of options:

The below formula permits to retrieve the « ID » and « Title » from the distant list, base on the « Lookup » parameter, and to rename the result as {Id:"ID", Value:"Title"}:

If(IsBlank(Param("Lookup")), Choices([@'CURRENT LIST NAME'].COLUMN_NAME), RenameColumns(ShowColumns(Filter('DISTANT LIST NAME', ID = Value(Param("Lookup"))), "ID", "Title"), "ID", "Id", "Title", "Value"))

Third, click on the Lookup field in the form and change the DefaultSelectedItems to select the item from the list of options:

The below formula returns an empty selection with {Id:"", Value:""} when no URL param, otherwise it returns the first record for our lookup:

If(IsBlank(Param("Lookup")), {Id:"", Value:""}, First(RenameColumns(ShowColumns(Filter('DISTANT LIST NAME', ID = Value(Param("Lookup"))), "ID", "Title"), "ID", "Id", "Title", "Value")))

And finally, we can pass Lookup=ID in the URL to select the related item in the other list

How to deal with new fields?

If you add a new field to your list’s settings, you’ll have to edit the form in PowerApps, and then edit the fields and add the new one:

(I used this article as a starting point)

Transfer an Alexa AWS Lambda function from the online editor to the ASK CLI

When we follow the guide to build a new smarthome skill, it gives the steps to create a function in the online code editor.

But if you prefer to use the ASK CLI, there is some steps to follow…

I first create a fake skill with ask new (using the « hello world » and « AWS Lambda » options).

Once the folder structure and files are created, I edit the .ask/ask-states.json file to reflect the information from the skill I created during the guide.

Then in the folder skill-package I remove everything except skill.json. To find what to put into that file, use the command: ask smapi get-skill-manifest -s <SKILL ID> and copy/paste that code.

Finally, I force the deploy with ask deploy --ignore-hash.

The Lambda function can now be managed locally on your computer and deployed with ASK CLI. You can go to the different skill consoles to delete the fake skill « hello world » you created.

Add a domain to a Let’s Encrypt certificate

For Apache, in the folder sites-available, you need to create your the-new-one.your-domain.com.conf file. Then enable the new site with a2ensite the-new-one.your-domain.com.

You can list all domains associated with a certificate:

certbot certificates

Now we add the SSL using certbot. You need to list all the existing domains and add the new one:

certbot --apache --cert-name your-domain.com -d first.your-domain.com,second.your-domain.com,third.your-domain.com,the-new-one.your-domain.com