Extract Dates and Times from LinkedIn URL

Here’s what our Apps Script will do:

1. copy a LinkedIn post to a cell in Google Sheets

2. run script from custom menu

3. date and time (in your time zone) is displayed in a new cell

As usual, here’s the video walkthrough:

The Custom Menu

I am a big fan of adding custom menus to make executing scripts simple.

Head into Apps Script by selecting Extensions - Apps script.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('LinkedIn Post Date')
    .addItem('Find Date', 'getDate')
    .addToUi();
}

We want this menu to appear when we open the spreadsheet, so we’ll wrap it up in an onOpen() special function.

Now we need to do a lot of things:

1. Tear the 19 digit number out of the url

2. Treat it like a 64 bit decimal (because it’s encoded…of course)

3. Convert the time portion of that into a date object

4. Get the time zone we’re using

5. Convert that date object into our time zone

6. Set that date and time in the correct format in a new cell

Luckily for me, I found a similar project that did the complicated stuff on Ollie Boyd’s GitHub. I was able to modify it to suit our purposes.

Luckily for you, I won’t bore you with trying to explain the details of each of those steps.

You can make a copy of the finished spreadsheet here.

And here’s what each step of the process looks like👇

getPostId()

function getPostId() {
  const linkedinURL= SpreadsheetApp.getActive().getRangeByName('posturl').getValue()
  const regex = /([0-9]{19})/;
  const postId = regex.exec(linkedinURL).pop();
  return postId;
}

extractUnixTimestamp(postId)

function extractUnixTimestamp(postId) {
  // BigInt needed as we need to treat postId as 64 bit decimal. This reduces browser support.
  const asBinary = BigInt(postId).toString(2);
  const first41Chars = asBinary.slice(0, 41);
  const timestamp = parseInt(first41Chars, 2);
  const dateObject = new Date(timestamp);
  return dateObject;
}

userTimeZone()

function userTimeZone(){
  const timezone = SpreadsheetApp.getActive().getSpreadsheetTimeZone()
  return timezone;
}

getDate()

//puts everything together and returns the date in our sheet
function getDate() {
  const postId = getPostId();
  const unixTimestamp = extractUnixTimestamp(postId);
  const currentTimeZone = userTimeZone()
  const thedate = Utilities.formatDate(unixTimestamp,currentTimeZone, 'MMMM dd, yyyy hh:mm:ss aa')
  SpreadsheetApp.getActive().getRangeByName('postdate').setValue(thedate)
}

Wrap up

This one is a little more technical; what’d you think?

I love making little utilities like this for random projects. Hope you learned something new.

As always, if I can help out with a particular topic or project, let me know.

📨 What’s something you wish you could automate right now? (reply)