Sorting Google Sheets rows based on date column

Help me Build my Workflow
1

Yo. Wanting to sort outputs of a Google sheet by a date column provided in each row.

Issue is that the “Sort” node sorts based on string values, so it doesn’t properly sort by date.

Tried using custom code (generated for me by Claude and ChatGPT - I’m not good enough at coding to make my own for this) but it didn’t work (didn’t sort at all sometimes, got some errors, etc.)

Wondering if there’s a workaround to somehow sort these rows by newest to oldest.

image
image1175×841 73.6 KB

2

Hey @sijobi2222 - I think you only have to convert the date. You can do that with a code node before you sort. If you have access, you could also change the format in sheets.

const moment = require('moment');

const items = $input.all().map(item => {
  const data = item.json;
  
  // Here you want to rename the property to what you have 
  if (data.date) {
    data.date = moment(data.date, 'M/D/YY').format('YYYY-MM-DD');
  }
  
  return { json: data };
});

return items;
2 Likes
3

Yes use the date node to change the date format and then try the sort node

4

@sijobi2222 if you work in cloud, then you may use the following technique to add a field to sort data by while keeping original date intact (assuming you want to preserve the date formatting).

@jksr is right, you need to convert the date for sorting purposes. Insert an “Edit Fields” node between “Google Sheets” and “Sort” and configure it:

  • Mode : Manual Mapping
  • Fields to set: sortingKey with expression {{ DateTime.fromFormat($json["Date Posted"], "M/d/yy") }}
  • Check “Include Other Input Fields”

See an example below for reference.

image
image973×431 38.8 KB

In your “Sort” node, use sortingKey instead of Date Posted.

Please mark this (or @jksr’s) post as a Solution if either worked for you.

1 Like
5

This is a useful technique! Nice to add information to an object.

6

Thanks @Olek; for some reason, the output doesn’t quite work for me here as it comes out like this:

image
image244×626 24.6 KB

Also wouldn’t that still end up not working with the sort, since by default the Sort Node sorts by string?

7

Thanks @jksr - which is the property to be renamed in the code you provided, and what am I supposed to be renaming it to?

Same with the other answer though, how would this solution sort properly if the Sort node sorts by string?

8

With your mouse, hover over the value input box. You will notice the value interpretation mode switch under lower right corner of the input box. Set the value interpretation mode to “Expression”.

image
image661×155 10.5 KB

(This example comes from my current app, do not copy code. This is just to illustrate the mode switch you need to locate.)

Also wouldn’t that still end up not working with the sort, since by default the Sort Node sorts by string?

It will be passed down to the Sort node as a string.

1 Like
9

This worked, thank you @Olek! Unfortunately this led me to another issue, but since my initial issue has been resolved I’d like to mark your answer as the solution.

I’m not seeing the option to do that though, and usually I can. Anyone from the n8n team know why this is so?

image
image368×134 4.91 KB

1 Like
10

@sijobi2222 , I’m glad it worked. I myself hit the Fixed/Expression “bug” from time to time, and working with date and time data from spreadsheets is tricky.

Thank you for recognizing my suggestions as a solution. I reckon, the purpose of Solution button is let other readers get straight to it when they find this topic while seeking for help in a similar situation. Yet this topic is not really long and offers a couple of possible approaches to solving issues connected with date/time transformation, which I find really useful. So, I guess other fellow n8ners will find this thread a quite insightful read.

TL;DR: You cannot mark a post as a Solution, but it is not a big issue, imho.

1 Like