DEV Community: Mathieu Huot

Convert Excel date into Office Scripts

Mathieu Huot — Thu, 21 Mar 2024 01:54:02 +0000

Hi, this is a note to self about the treatment of Excel workbook dates when used inside of an Office script.

Context

At work, we just transitioned from Google Workspace to Microsoft 365. So I had to convert some of my workflow to this new environment. I had to adapt a few things, especially with regards to scripts.

What is a date

A date in Excel is the number of days from January 1st, 1900 (at midnight).
A date in JavaScript is the number of milliseconds since January 1st, 1970 (at midnight).

So any time I want to manipulate a date extracted form Excel in an Office script, I need to convert it to JavaScript format first.

Converting Excel dates to JavaScript dates

(excelDate - 25569) * 24 * 60 * 60 * 1000

This equation is doing two things:

Rebasing to 1970 by subtracting the number of days between 1970 and 1900 from the Excel date (excelDate - 25569).
Converting days into milliseconds * 24 * 60 * 60 * 1000.

It can be used in a utility function like so:

function convertDate(excelDate: number) {
  return new Date((excelDate - 25569) * 24 * 60 * 60 * 1000);
}

Now I have a quick reference if I run into this same situation again. Maybe it can be useful for others too.

Thanks for reading! 😁

Highlight Nunjucks code blocks with twig

Mathieu Huot — Tue, 24 Nov 2020 21:20:47 +0000

I just discovered lately that, when writing Markdown, I can highlight Nunjucks code blocks using the twig flag! At least it works with Dev.to Markdown flavor. I had previously tryed the nunjucks tag without success and the html was missing some key highlights. Here's a comparition of the three.

With the nunjucks tag

---
permalink: formations/{{ video.snippet.title | lower | slug }}/
---
<section class="listing-section">
  {% for video in videos %}
    {% card
    defaultClass = 'listing-card',
    title = video.data.title,
    detail = video.data.publishedat | timeFormat,
    text = video.data.description,
    link = video.url | url,
    linkText = 'participe à la formation!'
    %}
  {% endfor %}      
</section>

With the html tag

---
permalink: formations/{{ video.snippet.title | lower | slug }}/
---
<section class="listing-section">
  {% for video in videos %}
    {% card
    defaultClass = 'listing-card',
    title = video.data.title,
    detail = video.data.publishedat | timeFormat,
    text = video.data.description,
    link = video.url | url,
    linkText = 'participe à la formation!'
    %}
  {% endfor %}      
</section>

With the twig tag

---
permalink: formations/{{ video.snippet.title | lower | slug }}/
---
<section class="listing-section">
  {% for video in videos %}
    {% card
    defaultClass = 'listing-card',
    title = video.data.title,
    detail = video.data.publishedat | timeFormat,
    text = video.data.description,
    link = video.url | url,
    linkText = 'participe à la formation!'
    %}
  {% endfor %}      
</section>

Ah, much better! Thanks for reading! 😊

Requesting YouTube API endpoint using Node and 11ty

Mathieu Huot — Wed, 15 Jul 2020 16:16:31 +0000

The other day, I was asked to import YouTube videos from a channel on a website I maintain for a small local organization. My idea was to import the IDs for every videos on that channel so I could built an iframe element with the right src attribute. I didn't want to import data from YouTube with every site build and since I'm managing the YouTube channel for the client, I was happy with running a script with every updates (which are not frequent).

Note: in order to use the YouTube API you'll need to setup with Google first. Here's an introduction on how to do that.

I usually find Google API confusing. The documentation is straight forward and thorough enough. It's just that there's so many parameters to go through. In the end, all I need is to get the data and then use it in a template for prerendering. My static site generator (SSG) of choice right now is Eleventy (11ty) which runs on Node. For this project, the JamStack architecture really fits well!

11ty is so flexible and simple. I really love working with this tool!

The code

I could have used a library like Axios, Request (now deprecated) or node-fetch, but my requirement was so simple that I decided to go without dependencies! Node's standard library has a https module which gives me access to a get method. Making a get request with the https module is quite simple. Here's the code using the stream.pipeline() method:

youtube-refresh.js

const https = require('https');
const fs = require('fs');
const { pipeline } = require('stream');

//Creating Writable stream to write on _data/youtube.json.
//If the file doesn't exist, it will be created.
const OUTPUT = fs.createWriteStream('./_data/youtube.json');

https.get(`https://www.googleapis.com/youtube/v3/search?...`, res => {
    //Piping res (IncomingMessage: Readable) to OUTPUT (Writable).
    //The pipeline method wilL manage stream flow and errors!
    pipeline(res, OUTPUT, error => {
        if(error) return console.error(error.stack);
        console.log('Transfering YouTube data is done!');
    });  
}).on('error', error => console.error(error.stack));

To come up with this, I had to read the documentation and experiment a bit with the Node's Stream module (v14.5.0 docs). Since the res returned by the get method extends the stream.Readable, it felt really natural to use a stream method (in this case the pipeline method) to manage the response.

Note: unlike the pipe method, pipeline will destroy every streams automatically on error!

Finally, when I need to, I would run this script in the project's root directory like this before I build for production with the new data:

command line interface

node youtube-refresh.js

By running this command, I get a new (or refreshed) youtude.json file in the _data directory which in 11ty exposes data globally through the youtube object! Then, I can use the data to build pages. Here's an example of what I did with the 11ty built-in pagination constructor:

videos.njk

---
layout: base-layout.njk
pagination:
  data: youtube.items
  size: 1
  alias: video
  addAllPagesToCollections: true
tags: videos
eleventyComputed:
  title: Formations {{ video.snippet.title }}
  description: "{{ video.snippet.description }}"
  publishedat: "{{ video.snippet.publishedAt }}"
permalink: formations/{{ video.snippet.title | lower | slug }}/
---
<main class="post-layout default-blog-theme">
    <article>
        <header class="article-header">
            <h1>{{ video.snippet.title }}</h1>
            <p><small>{{ video.snippet.publishedAt | timeFormat }}</small></p>
        </header>
        <div class="youtube">
            <iframe src="https://www.youtube.com/embed/{{ video.id.videoId }}?rel=0" allow="accelerometer; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
        </div>
        <p>{{ video.snippet.description }}</p>
    </article>
</main>

There's a lot going on here. First, I paginate the data in youtube.items, which is the data contained in _data/youtube.json, in order to create one page per item. Then, I use addAllPagesToCollections (new in v0.8.0) to create a collection available through collections.videos which I will use on a listing page (see below)! I also use eleventyComputed (new in v0.11.0) and permalink in order to generate specific title, description, date and url for each page! The timeFormat filter makes date human readable and french canadian locale (only works with Node v13 and up). 😉

Here's the listing page:

formations.njk

---
layout: formations-layout
title: Formations
description: Les formations offertent par l'ADTILCSL!
date: 2019-07-16
tags: pages
pagination:
  data: collections.videos
  size: 9
  alias: videos
  reverse: true
permalink: formations/{% if pagination.pageNumber > 0 %}{{ pagination.pageNumber }}/{% endif %}index.html
---
<section class="listing-section">
  {% for video in videos %}
    {% card
    defaultClass = 'listing-card',
    title = video.data.title,
    detail = video.data.publishedat | timeFormat,
    text = video.data.description,
    link = video.url | url,
    linkText = 'participe à la formation!'
    %}
  {% endfor %}      
</section>

My stack

Software	Version
OS	Linux Mint 18.2 Sonya
Node	v14.x
11ty	0.11.0

The end

Thanks for reading! 😀

Processing sass with 11ty

Mathieu Huot — Mon, 14 Oct 2019 04:23:32 +0000

Even though I've been using the solution described below with good success on POC and side projects, I believe it is not optimal in most situation. It's easier and probably more efficient to use the CLI as described here:

Using SASS With Eleventy

John Kemp-Cruz ・ Mar 29 '21

#11ty #eleventy #sass #webdev

Anyhow, it's nice to know the options.

My Eleventy (11ty) project

I recently discovered 11ty and used the static site generator to build a business website. I like that 11ty allows working with different templates and mixing them too. In this site I used Nunjucks, Markdown and Sass. Now, 11ty doesn't have Sass pre-processing built-in. So I had to find my own way.

Turning scss into css

There are a few approches to solve this problem using filters or Gulp. I had used the Gulp way in another project, but for this one I wanted to do something else. So I wrote a Node script instead!

The code

The neat thing about 11ty is that it's written in JavaScript. So you can run in .eleventy.js config file any executable code that you like as long as it's JavaScript. So here's the function that I use on my project to pre-process scss files.

/config/sass-process.js

const sass = require('sass');
const fs = require('fs-extra');
const path = require('path');

module.exports = (scssPath, cssPath) => {
    //If cssPath directory doesn't exist...
    if(!fs.existsSync(path.dirname(cssPath))) {
        //Encapsulate rendered css from scssPath into result variable
        const result = sass.renderSync({file: scssPath});
        //Create cssPath directory recursively
        fs.mkdir(path.dirname(cssPath), {recursive: true})
        //Then write result css string to cssPath file
        .then(() => fs.writeFile(cssPath, result.css.toString()))
        .catch(error => console.error(error))
    }
    //Watch for changes to scssPath directory...
    fs.watch(path.dirname(scssPath), () => {
        console.log(`Watching ${path.dirname(scssPath)}...`);
        //Encapsulate rendered css from scssPath into watchResult variable
        const watchResult = sass.renderSync({file: scssPath});
        //Then write result css string to cssPath file
        fs.writeFile(cssPath, watchResult.css.toString())
        .catch(error => console.error(error))      
    });
}

And then include that function in .eleventy.js file as so.

.eleventy.js

const sass = require('./config/sass-process');

module.exports = config => {
    //Watching for modificaions in style directory
    sass('./style/index.scss', './docs/style/index.css');
}

Refactoring this code to make it asynchronous

The solution above works fine for my need. Thing is that I'm using the synchronous method to render sass sass.renderSync() and that code is blocking (very little, but still). There are mainstream solutions to render css from sass asynchronously like the node-sass library with its asynchronous render() method witch I can turn into a promise like so.

const sass = require('node-sass');
const cssResultFrom = file => {
    return new Promise((resolve, reject) => {
        sass.render({file: file}, (error, result) => {
            if(error) {
                reject(error);
            }
            resolve(result);
        });
    });
}
//Then to use it...
cssResultFrom(scssPath)

Instead of writing my own Promise wrapper, I could use a module that does it for me like the fairly new node-sass-promise.

//node-sass-promise method
const sass = require('node-sass-promise');
//And to use it...
sass.render({file: scssPath})

A little less flexible then writing my own wrapper, but less code to write! An exemple of code using node-sass-promise :

const sass = require('node-sass-promise');
const fs = require('fs-extra');
const path = require('path');

module.exports = (scssPath, cssPath) => {
    //If cssPath directory doesn't exist...
    if(!fs.existsSync(path.dirname(cssPath))) {
        //Create cssPath directory recursively
        fs.mkdir(path.dirname(cssPath), {recursive: true})
        //Render css from sass
        .then(() => sass.render({file: scssPath}))
        //Then write result css string to cssPath file
        .then(result => fs.writeFile(cssPath, result.css.toString()))
        .catch(error => console.error(error))
    }
    //Watch for changes to scssPath directory...
    fs.watch(path.dirname(scssPath), () => {
        //Render css from sass...
        sass.render({file: scssPath})
        //Then write result css string to cssPath file
        .then(result => fs.writeFile(cssPath, result.css.toString()))
        .catch(error => console.error(error))
        console.log(`Watching ${path.dirname(scssPath)}...`);      
    });
}

My stack

Software	version
OS	Linux Mint 18.2 Sonya
Node	10.15.0
NPM	6.8.0
11ty	0.8.3

The end

Thanks for reading! :-)